A while back, I was greatly confused by SQLite date and time functions. It took me a while to figure out what was wrong. (It was my error: I hadn’t observed the rule that dates must have this form “YYYY-MM-DD” – four digit year, two-digit month and day.)
Nevertheless, I found that the documentation wasn’t quite clear, so I wrote up these notes as an adjunct to SQLite Datatypes and the SQLite Date and Time Functions pages.
2.2. Date and Time Datatype
SQLite does not have a storage class set aside for storing dates and/or times.
The conventional way to store dates is as a string in a TEXT field.
These fields can be compared directly (as strings) to determine equality or order.
For other date-as-string formats, see Date Strings on the Date And Time Functions page.
For further manipulations on dates and times, the built-in Date And Time Functions of SQLite convert dates and times between TEXT, REAL, or INTEGER values:
- TEXT as strings (“YYYY-MM-DD HH:MM:SS.SSS” – with leading zero where required, and four-digit year – a so-called “timestring”)
- REAL as Julian day numbers, the number of days (with fractional part) 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.
Date And Time Functions
The Date and Time Functions page doesn’t really define the the arguments or the return types, so I make them explicit below.
Timestring: The conventional way to store dates is as a timestring – a TEXT field (e.g., “YYYY-MM-DD HH:MM:SS.SSS”). These fields can be compared directly (as strings) to determine equality or order.
To convert to other date representations, SQLite supports five date and time functions. All take a timestring (a subset of IS0 8601 date and time formats, listed below) as an argument. The timestring is followed by zero or more modifiers. The strftime() function also takes a format string as its first argument.
- date(timestring, modifier, modifier, …) Returns the date as a string: “YYYY-MM-DD”.
- time(timestring, modifier, modifier, …) Returns the time as a string: “HH:MM:SS”.
- datetime(timestring, modifier, modifier, …) Returns a string: “YYYY-MM-DD HH:MM:SS”.
- julianday(timestring, modifier, modifier, …) Returns the Julian day as an REAL – the number of days (and fractional part) since noon in Greenwich on November 24, 4714 B.C. (Proleptic Gregorian calendar).
- strftime(format, timestring, modifier, modifier, …) Returns the date formatted according to the format string specified as the first argument. The format string supports the most common substitutions found in the strftime() function from the standard C library plus two new substitutions, %f and %J.
… see the original SQLite page for modifiers and legal timestring formats …
Examples
This section replicates the examples of the original page, but includes the results and types of the function.
Compute the current date. Returns timestring.
SELECT date('now'); -- Result: 2018-03-07
Compute the last day of the current month. Returns timestring.
SELECT date('now','start of month','+1 month','-1 day'); -- Result: 2018-03-31
Compute the date and time given a unix timestamp 1092941466. Returns timestring.
SELECT datetime(1092941466, 'unixepoch'); -- Result: 2004-08-19 18:51:06
Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone. Returns timestring.
SELECT datetime(1092941466, 'unixepoch', 'localtime'); -- Result: 2004-08-19 14:51:06
Compute the current unix timestamp. Returns INTEGER.
SELECT strftime('%s','now'); -- Result: 1520444198
Compute the number of days since the signing of the US Declaration of Independence. Returns REAL – days and fractions of a day.
SELECT julianday('now') - julianday('1776-07-04'); -- Result: 88269.7339379285
Compute the number of seconds since a particular moment in 2004: Returns INTEGER.
SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56'); -- Result: 447519729
Compute the date of the first Tuesday in October for the current year. Returns timestring.
SELECT date('now','start of year','+9 months','weekday 2'); -- Result: 2018-10-02
Compute the time since the unix epoch in seconds (like strftime(‘%s’,’now’) except includes fractional part). Returns REAL – days and fractions of a day.
SELECT (julianday('now') - 2440587.5)*86400.0; -- Result: 1520444280.01899
A Practical Tutorial for SQLite Date Functions
The SQLite document doesn’t really show how to use date functions in actual code. Here is an example of inserting and retrieving dates in a table.
Note: It is good practice to store dates as text in a datestring format – YYYY-DD-MM. The “BMW” entry below is inserted as an integer number of seconds, and doesn’t work right when trying to use the julianday() function
bash-3.2$ sqlite3
SQLite version 3.29.0 2019-07-10 17:32:03
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table car_table(car_name text, car_date text);
sqlite> insert into car_table values ("Ford", date('now'));
sqlite> insert into car_table values ("Toyota", date('now','7 days'));
sqlite> insert into car_table values ("BMW", strftime('%s','now'));
sqlite> select * from car_table;
Ford|2019-12-15
Toyota|2019-12-22
BMW|1576431883 <-- uh oh, this is in seconds, not a datestring
sqlite> select car_name, julianday(car_date) from car_table;
Ford|2458832.5
Toyota|2458839.5
BMW| <-- uh oh, this isn't a julianday, as expected
sqlite> ^D to exit