The first four differ from the last one in that they use a default format for the returned value while strftime allows (and requires) to explicitly define the returned format.
Another difference is that julianday returns a value in the datatypereal while the others (even strftime('%J', …)) returns a value as text.
Thus, the following to expressions are equivalent:
select date ( 'now');
select strftime('%Y-%m-%d', 'now');
TODO: Version 3.38.0 (2022-02-22) added the sixth function unixepoch().
datetimestring
The datetimestring specifies a point in time on which the function is applied.
The arguably simplest datetimestring is 'now': it specifies the current UTC date and time. The following example shows how the same date-time is formatted differently with the time, date and datetime functions. It also demonstrates how strftime is used to return a user defined format:
.mode column
.header on
select
time ( 'now') "time",
date ( 'now') "date",
datetime( 'now') "datetime",
strftime('Minutes past the hour: %M', 'now') "strftime";
--
-- time date datetime strftime
-- ---------- ---------- ------------------- -------------------------
-- 15:32:24 2019-09-22 2019-09-22 15:32:24 Minutes past the hour: 32
The following example uses strftime to render the same date (which is specified by the datetimestring '2018-03-05') in a British, American and Swiss format
.mode column
.header on
select
strftime('%d/%m/%Y', '2018-03-05') british,
strftime('%m/%d/%Y', '2018-03-05') american,
strftime('%d.%m.%Y', '2018-03-05') swiss
;
--
-- british american swiss
-- ---------- ---------- ----------
-- 05/03/2018 03/05/2018 05.03.2018
All functions allow to modify the date time that was specified with the datetimestring.
One modifier is localtime. This modifier interprets a date value as UTC and converts it to local time. Thus, it might be useful to print the current date and time in local time:
The following example prints now in UTC and localtime. Because the computer on which it was run is configured with UTC+2 (see date +%z), there is a (positive) 2 hour difference between the UTC and localtime in the result:
A special modifier is 'unixepoch'. It interprets datetimestring as seconds per 1970-01-01 in UTC. In the following example, the datetime of 0 and a calculated one are shown:
2021-11-30 is a Tuesday, the start (Sunday) of the corresponding week is 2021-11-28. The following steps try to explain how this date can be calculated:
select date('2021-11-30', 'weekday 0') ; -- Next sunday: 2021-12-15
select julianday(date('2021-11-30', 'weekday 0')) ; -- Julian Day is 2459553.5
select julianday(date('2021-11-30', 'weekday 0'))-7 ; -- Subtracting 7 days returns Julian Day 2459546.5
select date(julianday(date('2021-11-30', 'weekday 0'))-7) ; -- Convert Julian Day to date: 2021-11-28 (which is 2021-11-28)
select strftime('%Y-%W', date(julianday(date('2021-11-30', 'weekday 0'))-7)) ; -- Select date's year and week (2021-47)
julianday(…) returns a number (a real) that specifies the (fractional) days since the start of the proleptic Gregorian calendar (noon of November 24th, 4714 B.C).
The two strftime format strings %J and %s a Julian Day or a Unix Epoch, respectively. Note, although the returned values are numerical in nature, their data type is text. However, the data type of julianday() is real: