[Using Datetime in SQLite] How to manage date and time in SQLite tables #SQLite #datetime

SQLiteDatetime.md

DATETIME in SQLite

SQLite does not have official datetime type. It is possible to store dates and times with 3 different approaches:

1. INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
2. TEXT as ISO8601 strings (“YYYY-MM-DD HH:MM:SS.SSS”).
3. REAL as Julian timestamp, the number of days since noon in Greenwich on November 24, 4714 BC.

Using INTEGER data type

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

Using TEXT data type

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

Using REAL data type

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