Search notes:

SQLite: date and time data type

SQLite does not have an explicit date and/or time data type.
However, it's possible to store dates using one of the numeric data types integer or real.

Storing dates as Julian days

julianday(date_string) returns a real.
The value of the returned julian day can then be expressed as a date string with strftime(fmt_string, julian_day).

Unixepoch

Another possiblity to store dates or times in SQLIte is to store them as seconds since January 1st, 1970 (that is: the Unix time).
The following example first creates the table d to store some dates in text format.
It then uses the create table as select statement together with strftime('%s', …) to convert the date string to the unix epoch and store these values in the table s.
Finally, it selects these seconds from s and uses strftime(fmt, secs, 'unixepoch') to create a date time string again: 
create table d (
   date_string text
);

insert into d values ('1970-01-01 00:00:01');
insert into d values ('1969-12-31 23:59:59');
insert into d values ('2038-01-19 03:14:07');
insert into d values ('2038-01-19 03:14:08');

create table s as
select
   date_string,
   cast(strftime('%s', date_string) as integer) as secs_since_1970_01_01
from
  d;

.mode column
.width 20 20 20 20
.header on
select
   date_string                                                               ,
   secs_since_1970_01_01                                                     ,
   printf('%x', secs_since_1970_01_01)                               secs_hex,
   strftime('%Y-%m-%d %H:%M:%S', secs_since_1970_01_01, 'unixepoch') t
from
   s;
Github repository about-sqlite, path: /datatypes/date-time/unix-epoch.sql
The select statement returns 
date_string           secs_since_1970_01_0  secs_hex              t                   
--------------------  --------------------  --------------------  --------------------
1970-01-01 00:00:01   1                     1                     1970-01-01 00:00:01 
1969-12-31 23:59:59   -1                    ffffffffffffffff      1969-12-31 23:59:59 
2038-01-19 03:14:07   2147483647            7fffffff              2038-01-19 03:14:07 
2038-01-19 03:14:08   2147483648            80000000              2038-01-19 03:14:08
Note, that the example stores the the datetime of 2038-01-19 03:14:08 to test the year 2038 problem.

Index