How to use DATETIME value in SQLite?

SQLite does not have an official datetime type. Instead, it stores dates and times as TEXT, REAL or INTEGER values using Date and Time Functions.

TEXT as ISO8601 strings (“YYYY-MM-DD HH:MM:SS.SSS”).
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

1. Using TEXT datatype

If you use TEXT storage class to store date and time value, you need to use the ISO8601 string format as follows:

YYYY-MM-DD HH:MM:SS.SSS

Let’s demonstrate how to use datetime values as TEXT in an example:

Create a table with TEXT column to store data & time values:

CREATE TABLE date_n_time (
	id int,
	d1 text
);

Now use the datetime() function to insert value into the table:

INSERT INTO date_n_time
		VALUES(1, datetime('now'));

Now the data is inserted:

id	d1
1	2019-10-14 13:57:01

To extract date and time value from d1, use date() and time() functions:

SELECT
    date(d1),
    time(d1)
FROM
    date_n_time;

The result is:

date(d1)	time(d1)
2019-10-14	13:57:01

2. Use REAL data type

You can use REAL data type to store data and time values as the Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.

Example:

Creata a table with REAL type column:

CREATE TABLE real_date (
	id int,
	d2 REAL
);

Now use julianday() function to insert data to the new table:

INSERT INTO real_date
		VALUES(1, julianday ('now'));

And we have the first row of data:

id	d2
1	2458771.11100336

It’s a Julian Day, so it’s not readable. We can use the datetime(), date() and time() function to extract the date and time values from d2:

SELECT
	datetime(d2),
	date(d2),
	time(d2)
FROM
	real_date;

The result is:

datetime(d2)	date(d2)	time(d2)
2019-10-14 14:39:50	2019-10-14	14:39:50

3. Using INTEGER data type

You can also use INTEGER data type to store data and time values in SQLite.

Example:

Create a new table with integer type column:

CREATE TABLE int_date (
	d3 int
);

Now use the strftime() function to insert value into the new table:

INSERT INTO int_date (d3)
		VALUES(strftime ('%s', 'now'));

We have the first row of data:

d3
1571064443

The result is an integer, so in term of date and time, it’s not a readable value. We can use the datetime(), date(), and time() functions to convert it to the date format:

SELECT
	datetime (d3,'unixepoch'),
	date (d3,'unixepoch'),
	time (d3,'unixepoch')
FROM
	int_date;

And the result is:

datetime (d3,’unixepoch’)	date (d3,’unixepoch’)	time (d3,’unixepoch’)
2019-10-14 14:47:23	2019-10-14	14:47:23

Need a good GUI tool for SQLite? Check out TablePlus. It’s native, beautiful, and available for free.