What are the sqlite equivalents of MySQL's INTERVAL and UTC_TIMESTAMP?

Question

What are the sqlite equivalents of INTERVAL and UTC_TIMESTAMP? For example, imagine you were "porting" 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, so is the SQLite equivalent of MySQL's UTC_TIMESTAMP().

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

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

Therefore, your example would look like this in SQLite:

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

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

Answer 2

There's no native timestamp support in sqlite.

I've used plain old (64-bit) integers as timestamps, representing either micro- or milliseconds since an epoch.

Therefore, assuming milliseconds:

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

and bind system time in milliseconds to the first param.