sqlite Data types Date/time types

Example

SQLite has no separate data type for date or time values.

ISO8601 strings

The built-in keywords CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP return strings in ISO8601 format:

> SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;
CURRENT_DATE  CURRENT_TIME  CURRENT_TIMESTAMP  
------------  ------------  -------------------
2016-07-08    12:34:56      2016-07-08 12:34:56

Such values are also understood by all built-in date/time functions:

> SELECT strftime('%Y', '2016-07-08');
2016

Julian day numbers

The built-in date/time functions interpret numbers as Julian days:

> SELECT datetime(2457578.02425926);
2016-07-08 12:34:56

The julianday() function converts any supported date/time value into a Julian day number:

> SELECT julianday('2016-07-08 12:34:56');
2457578.02425926

Unix timestamps

The built-in date/time functions can interpret numbers as Unix timestamps with the unixepoch modifier:

> SELECT datetime(0, 'unixepoch');
1970-01-01 00:00:00 

The strftime() function can convert any supported date/time value into a Unix timestamp:

> SELECT strftime('%s', '2016-07-08 12:34:56');
1467981296 

unsupported formats

It would be possible to store date/time values in any other format in the database, but the built-in date/time functions will not parse them, and return NULL:

> SELECT time('1:30:00');   -- not two digits

> SELECT datetime('8 Jul 2016');