What are the sqlite equivalents for MySQL INTERVAL and UTC_TIMESTAMP?

Question

What are the sqlite equivalents for INTERVAL and UTC_TIMESTAMP ? For example, imagine you “ported” the following SQL from MySQL to sqlite:

 SELECT mumble FROM blah WHERE blah.heart_beat_time > utc_timestamp() - INTERVAL 600 SECOND; 

Answer 1

datetime('now') provides you the current date and time in UTC, as well as the SQLite equivalent of MySQL UTC_TIMESTAMP() .

It may also be useful to know that given the date and time, a datetime string can convert it from localtime to UTC using datetime('2011-09-25 18:18', 'utc') .

You can also use the datetime() function to apply modifiers such as +1 day, beginning of month, 10 years, and many others.

Therefore, your example would look like this: SQLite:

 SELECT mumble FROM blah WHERE blah.heart_beat_time > datetime('now', '-600 seconds'); 

You can find more modifiers on the SQLite Date and Time Functions page.

Answer 2

Sqlite has no built-in timestamp support.

I used plain old (64-bit) integers as timestamps representing either micro- or milliseconds from the era.

Therefore, if you take milliseconds:

 SELECT mumble FROM blah WHERE blah.heart_beat_time_millis > ? - 600*1000; 

and bind the system time in milliseconds to the first parameter.

Answer 3

SQLite has LOCAL_TIMESTAMP, but it is GMT.