0

I have a SQLite3 table "data" with a column "ts" that of type DATETIME.

Based on the docs, I tried the following query:

SELECT count(*) FROM mds_daily_data WHERE ts >= '2020-10-07 12:27:00';

This returned no results. However, it did work when I compared against the unix-epoch timestamp equivalent of '2020-10-07 12:27:00', which is 1602073620:

SELECT count(*) FROM mds_daily_data WHERE ts >= 1602073620;

Why does the latter work and the former fail?

Goh
  • 167
  • 5
  • If `ts` is a number, then comparing it to a datetime string is obviously not going to work. – Gordon Linoff Oct 15 '20 at 16:22
  • `ts` is a field of type `DATETIME`, so I did expect it to work. – Goh Oct 15 '20 at 16:26
  • 1
    There i no DATETIME data type in SQLite. You may define it as such but it actually will have numeric affinity:https://sqlite.org/datatype3.html – forpas Oct 15 '20 at 16:26
  • @forpas Now I'm really confused, because I saw other answers that stated that queries like `SELECT * FROM test WHERE date BETWEEN '2011-01-11' AND '2011-08-11'` should work in SQLite3. Is that not the case? Is there no way to compare a `DATETIME` field to a human-readable format like a time string as described here: https://sqlite.org/lang_datefunc.html ? – Goh Oct 15 '20 at 16:30
  • This query will work if you save the dates in the table as text with format YYYY-MM-DD which is the only valid date format in SQLite. If you save them as UNIX epoch timestamps, meaning as integers then the query will not work. Your question has already been asked and answered in SO. check the duplicate link for a way to convert the integer dates to text dates. – forpas Oct 15 '20 at 16:33

0 Answers0