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;
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 datetime of
2038-01-19 03:14:08 to test the
year 2038 problem.