SQLite does not have official datetime type. It is possible to store dates and times with 3 different approaches:
- INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
- TEXT as ISO8601 strings (“YYYY-MM-DD HH:MM:SS.SSS”).
- REAL as Julian timestamp, the number of days since noon in Greenwich on November 24, 4714 BC.
Create a column with integer type column:
CREATE TABLE unix_date(
timestamp INTEGER
);
Use strftime()
function to insert a new value:
INSERT INTO unix_date(timestamp)
VALUES (strftime('%s', 'now'));
The actual time in Unix Time should be inserted, for example: 1619784112
.
To read the value in different formats, use the functions datetime()
, date()
and time()
:
SELECT
datetime(timestamp,'unixepoch'),
date(timestamp,'unixepoch'),
time(timestamp,'unixepoch')
FROM
unix_date;
The result should show:
2021-04-30 12:01:52, 2021-04-30, 12:01:52
Create a column with text type column:
CREATE TABLE iso_date(
date_time TEXT
);
Use datetime()
function to insert a new value:
INSERT INTO iso_date(date_time)
VALUES (datetime('now'));
The actual time is inserted in the ISO8601 format, for example: 2021-04-30 12:05:43
.
To read the value in different formats:
SELECT
date(date_time),
time(date_time)
FROM
iso_date;
The result should show:
2021-04-30, 12:05:43
Create a column with real type column:
CREATE TABLE julian_date(
date_time REAL
);
Use datetime()
function to insert a new value:
INSERT INTO julian_date(date_time)
VALUES (julianday('now'));
The actual time is inserted in the Julian format, for example: 2459334.88291
.
To read the value in different formats:
SELECT
date(date_time),
time(date_time)
FROM
julian_date;
The result should show:
2021-04-30, 09:11:23