How to compare sqlite TIMESTAMP values

by

The issue is with the way you’ve inserted data into your table: the +0200 syntax doesn’t match any of SQLite’s time formats:

  1. YYYY-MM-DD
  2. YYYY-MM-DD HH:MM
  3. YYYY-MM-DD HH:MM:SS
  4. YYYY-MM-DD HH:MM:SS.SSS
  5. YYYY-MM-DDTHH:MM
  6. YYYY-MM-DDTHH:MM:SS
  7. YYYY-MM-DDTHH:MM:SS.SSS
  8. HH:MM
  9. HH:MM:SS
  10. HH:MM:SS.SSS
  11. now
  12. DDDDDDDDDD

Changing it to use the SS.SSS format works correctly:

sqlite> CREATE TABLE Foo (created_at TIMESTAMP);
sqlite> INSERT INTO Foo VALUES('2010-05-28T15:36:56+0200');
sqlite> SELECT * FROM Foo WHERE foo.created_at < '2010-05-28 16:20:55';
sqlite> SELECT * FROM Foo WHERE DATETIME(foo.created_at) < '2010-05-28 16:20:55';
sqlite> INSERT INTO Foo VALUES('2010-05-28T15:36:56.200');
sqlite> SELECT * FROM Foo WHERE DATETIME(foo.created_at) < '2010-05-28 16:20:55';
2010-05-28T15:36:56.200

If you absolutely can’t change the format when it is inserted, you might have to fall back to doing something “clever” and modifying the actual string (i.e. to replace the + with a ., etc.).

(original answer)

You haven’t described what kind of data is contained in your CREATED_AT column. If it indeed a datetime, it will compare correctly against a string:

sqlite> SELECT DATETIME('now');
2010-05-28 16:33:10
sqlite> SELECT DATETIME('now') < '2011-01-01 00:00:00';
1

If it is stored as a unix timestamp, you need to call DATETIME function with the second argument as 'unixepoch' to compare against a string:

sqlite> SELECT DATETIME(0, 'unixepoch');
1970-01-01 00:00:00
sqlite> SELECT DATETIME(0, 'unixepoch') < '2010-01-01 00:00:00';
1
sqlite> SELECT DATETIME(0, 'unixepoch') == DATETIME('1970-01-01 00:00:00');
1

If neither of those solve your problem (and even if they do!) you should always post some data so that other people can reproduce your problem. You should even feel free to come up with a subset of your original data that still reproduces the problem.

More Related Contents:

  1. Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine
  2. SQLite DateTime comparison
  3. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  4. What are valid table names in SQLite?
  5. Deleting duplicate rows from sqlite database
  6. What does the SQL Standard say about usage of backtick(`)?
  7. Delete column from SQLite table
  8. Declare variable in SQLite and use it
  9. sqlite database default time value ‘now’
  10. How do I insert datetime value into a SQLite database?
  11. How to get a list of column names on Sqlite3 database?
  12. basic recursive query on sqlite3?
  13. Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
  14. SQLite and custom order by
  15. SQLite auto-increment non-primary key field
  16. GROUP_CONCAT in SQLite
  17. How to do IF NOT EXISTS in SQLite
  18. Core Data Performance with Single Parent Entity
  19. Match only entire words with LIKE?
  20. What is causing Foreign Key Mismatch error?
  21. How to split comma-separated values?
  22. sqlite select with condition on date
  23. SQLite Order By Date1530019888000
  24. SQL – Inserting a row and returning primary key
  25. IF() statement alternative in SQLite
  26. SQLite query, ‘LIKE’
  27. Oracle Convert TIMESTAMP with Timezone to DATE
  28. UPSERT *not* INSERT or REPLACE
  29. Drop column from SQLite table
  30. converting int to real in sqlite

Leave a Comment