Select data based on month and year

Issue

We storing date as String in column created_at by below format 2019-10-09T15:29:28.000+08:00 in Moor.

We would like to write a select query, to retrieve data where month are October and year is 2019.

 Future<ABC> selectReadingBasedOnMonth(
      int month, int year) {
    return (select(abcs)
          ..where((t) {
            final sqliteDate = FunctionCallExpression<DateTime, DateTimeType>(
                'date', [t.createdAt]);

            return sqliteDate.year.equals(year) &
                sqliteDate.month.equals(month);
          }))
        .getSingle();
  }

But we are not getting any data. This is the query displayed in log

I/flutter (12004): Moor: Sent SELECT * FROM abcs WHERE
(CAST(strftime(“%Y”, date(created_at), “unixepoch”) AS INTEGER)) = ?
AND (CAST(strftime(“%m”, date(created_at), “unixepoch”) AS INTEGER)) =
?; with args [2019, 10]

Solution

Thanks for the answer provided by simolus3

 final asDate = FunctionCallExpression('date', [t.createdAt]);
 final year = FunctionCallExpression<String, StringType>(
      'strftime', [const Constant<String, StringType>('%Y'), asDate]);
 final month = FunctionCallExpression<String, StringType>(
      'strftime', [const Constant<String, StringType>('%m'), asDate]);

  return year.equals('2019') & month.equals('07');

Answered By – John Joe

Answer Checked By – Mary Flores (FlutterFixes Volunteer)