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

mysql, sqlite

Solution

`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.

Problem

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; ```

Original source