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