The table below contains the valid format string substitutions that you can use with the strftime()
function in SQLite.
%d | Day of month: 00 |
%f | Fractional seconds: SS.SSS |
%H | Hour: 00-24 |
%j | Day of year: 001-366 |
%J | Julian day number |
%m | Month: 01-12 |
%M | Minute: 00-59 |
%s | Seconds since 1970-01-01 |
%S | Seconds: 00-59 |
%w | Day of week 0-6 with Sunday==0 |
%W | Week of year: 00-53 |
%Y | Year: 0000-9999 |
%% | % |
How do These Work?
In SQLite, the strftime()
function returns a date/time value in a given format.
You specify the format at the time you call the function. You do this by providing a format string, which consists of one or more “format string substitutions”.
Each format string substitution is like a placeholder for a given date part. For example, %m
is for the month, %Y
is for year, etc.
Example
Here’s an example to demonstrate:
.mode line
SELECT
strftime('%Y-%m-%d', 'now') AS "Date",
strftime('%H:%M:%S', 'now') AS "Time",
strftime('%Y-%m-%d %H:%M:%S', 'now') AS "Date & Time",
strftime('%d/%m/%Y', 'now') AS "Date 2",
strftime('%s', 'now') AS "Epoch Time",
strftime('%J', 'now') AS "Julian Day";
Result:
Date = 2020-04-27 Time = 00:11:45 Date & Time = 2020-04-27 00:11:45 Date 2 = 27/04/2020 Epoch Time = 1587946305 Julian Day = 2458966.508165996