Select data based on month and year

Question

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]

@Shawn Sorry, it was my mistake, I have asked the question in wrong way, as I am using Moor,not SQLite. — John Joe

MikeT MikeT · Accepted Answer · 2020-01-02T05:30:04

The unixepoch modifier can only be used with date/time strings that are solely digits.

The "unixepoch" modifier (11) only works if it immediately follows a timestring in the DDDDDDDDDD format.

This modifier causes the DDDDDDDDDD to be interpreted not as a Julian day number as it normally would be, but as Unix Time - the number of seconds since 1970.

If the "unixepoch" modifier does not follow a timestring of the form DDDDDDDDDD which expresses the number of seconds since 1970 or if other modifiers separate the "unixepoch" modifier from prior DDDDDDDDDD then the behavior is undefined.

For SQLite versions before 3.16.0 (2017-01-02), the "unixepoch" modifier only works for dates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (unix times of -62167219200 through 106751991167).

Date And Time Functions

For example consider the following (based upon your query) :-

DROP TABLE IF EXISTS abcs;
CREATE TABLE IF NOT EXISTS abcs (created_at TEXT);
INSERT INTO abcs VALUES ('2019-10-09T15:29:28.000+08:00');

SELECT *, 
    CAST(strftime('%Y', date(created_at)/*, 'unixepoch'*/) AS INTEGER) AS year_nounixepoch,
    CAST(strftime('%m', date(created_at)/*, 'unixepoch'*/) AS INTEGER) AS month_nounixepoch,
    CAST(strftime('%Y', date(created_at), 'unixepoch') AS INTEGER) AS year_invalid,
    CAST(strftime('%m', date(created_at), 'unixepoch') AS INTEGER) AS month_invalid,
    
    CAST(strftime('%Y', strftime('%s',date(created_at)), 'unixepoch') AS INTEGER) AS year_unixepoch,
    CAST(strftime('%m', strftime('%s',date(created_at)), 'unixepoch') AS INTEGER) AS month_unixepoch
    
FROM abcs 
WHERE CAST(strftime('%Y', strftime('%s',date(created_at)), 'unixepoch') AS INTEGER) = 2019 AND CAST(strftime('%m', strftime('%s',date(created_at)), 'unixepoch') AS INTEGER) = 10;
DROP TABLE IF EXISTS abcs; /* cleanup test environment */

Note that single quotes have been used to replace double quotes, which would be the correct SQL although this discrepancy may be due to how the message is output.

This results in :-

i.e. where the unixepoch modifier has been used it results in null as the date/time is not solely digits.
the selection criteria, with unixepoch via strftime('%s',..... works as expected.

Select data based on month and year

2 Answers