Search code examples
sqliteflutterdartflutter-moor

Select data based on month and year


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');