Tag Archives: sqlite

SQLite and datetime notes

In SQLite3 there is no such datatype as DateTime or Date or Time. As noted here:

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

  • 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.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

So a simple example:

1) To insert

insert into sometable values( null, datetime('now') );

which will save smth like: 2013-09-08 19:41:16

or

insert into sometable values( null, strftime('%s', 'now'));

which will save the UNIX time in seconds: 1378669277

Note that in Android for example you can use SQLiteDatabase.insert method and pass System.currentTimeMillis() in the ContentValues, however do not forget to trim the miliseconds.

2) To select

So depends how the date is saved you will get string or a number. I personnaly like to use the UNIX time and so save the date and integer type.

In that case simple:

select my_datetime from sometable

will return just numbers. It is useful when you need it for comparison or ordering but for the user it is better to get is as readable datetime string as:

select datetime(my_datetime, 'unixepoch') from sometable