The SQLite datetime()
function enables you to return a date and time value based on a time string and any modifiers.
It returns the date in this format: YYYY-MM-DD HH:MM:SS
To use this function, you need to provide a time string, plus any (optional) modifiers. A modifier allows you to change the date, such as add a number of days, set it to local time, etc
Syntax
The syntax goes like this:
datetime(timestring, modifier, modifier, ...)
The timestring
argument must be a valid time string.
The modifier
arguments are optional. You can provide one or more modifiers. If you provide a modifier, it must be a valid modifier.
Example
Here’s an example to demonstrate the datetime()
function being used with one argument.
SELECT datetime('now');
Result:
2020-04-28 23:56:06
The now
time string is converted into the current date and time.
Add a Modifier
We can modify the previous result by using a modifier. Here’s an example.
SELECT datetime('now', '+3 hours');
Result:
2020-04-29 02:56:52
In this case, I added three hours to the time. Given the original time, this also resulted in the date being moved forward to the next day.
Multiple Modifiers
As mentioned, you can add one or more modifiers. Here’s an example of adding another modifier to the previous example.
SELECT datetime('now', '+3 hours', 'localtime');
Result:
2020-04-29 12:58:13
In my case, the localtime
modifier resulted in the time being moved forward.
The reason it does this is because the localtime
modifier assumes the time string provided is in Universal Coordinated Time (UTC). It then adjusts the time string so that it displays local time.
Therefore, you may get a different result, depending on your local time.
Here it is again comparing the original local time with the modified result:
SELECT
datetime('now', 'localtime') AS "Local",
datetime('now', '+3 hours', 'localtime') AS "Modified";
Result:
Local Modified ------------------- ------------------- 2020-04-29 10:02:09 2020-04-29 13:02:09
Date as the Time String
The above examples use now
as the time string, but you can provide any valid time string.
If you provide just the date part, the time part will be set to all zeros.
SELECT datetime('2010-08-15');
Result:
2010-08-15 00:00:00
If only the time part is supplied, then the date is set to 2000-01-01.
SELECT datetime('23:58:57');
Result:
2000-01-01 23:58:57
Here’s an example that uses the Julian Day as the time string.
SELECT datetime('2451545.49927083');
Result:
2000-01-01 23:58:57
datetime() vs strftime()
The datetime()
function returns exactly the same result that strftime('%Y-%m-%d %H:%M:%S', ...)
returns. The datetime()
function is just a more convenient way to do it.
SELECT
datetime('now'),
strftime('%Y-%m-%d %H:%M:%S', 'now');
Result:
datetime('now') strftime('%Y-%m-%d %H:%M:%S', 'now') ------------------- ------------------------------------ 2020-04-29 00:16:12 2020-04-29 00:16:12
Date Range
As with all SQLite date and time functions, datetime()
only works for dates between 0000-01-01 00:00:00 and 9999-12-31 23:59:59 (julian day numbers 1721059.5 through 5373484.5).
For dates outside that range, the results are undefined.