SQLite | Functions | Date and Time Functions
SQLite provides date, time, datetime, julianday, and strftime for current or specified dates and times.
Syntax and input formats
date(timestring, modifier, ...)
time(timestring, modifier, ...)
datetime(timestring, modifier, ...)
julianday(timestring, modifier, ...)
strftime(format, timestring, modifier, ...)
The first four return YYYY-MM-DD, HH:MM:SS, YYYY-MM-DD HH:MM:SS, and a Julian day number respectively. Date/time values are treated as UTC unless modified. Inputs include ISO-like dates and times, now, and Julian day numbers. A missing time defaults to 00:00:00.000; a time without a date uses 2000-01-01.
select date('2008-04-28 09:15:42'),
time('2018-10-28 17:42:19'),
datetime('2019-11-06 12:03');
select date('now'), time('now'), datetime('now');
Julian days
Julian day zero is noon on November 24, 4714 BCE in the proleptic Gregorian calendar. Its unit is one day; for example, 0.5 is 12 hours.
select julianday('2019-08-04 08:42:19');
-- 2458699.86271991
strftime
| Token | Value |
|---|---|
%Y, %m, %d |
Year, month, day |
%W, %j, %w |
Week, day of year, weekday (Sunday=0) |
%H, %M, %S, %f |
Hour, minute, second, fractional second |
%s |
Seconds since 1970-01-01 |
%J |
Julian day |
%% |
Literal percent sign |
select strftime('%H:%M:%f', 'now');
select strftime('elapsed seconds: %s', 'now');
date, time, datetime, and julianday correspond to strftime('%Y-%m-%d', ...), strftime('%H:%M:%S', ...), strftime('%Y-%m-%d %H:%M:%S', ...), and strftime('%J', ...).
Modifiers
Available modifiers include numeric years, months, days, hours, minutes and seconds, plus start of year, start of month, start of day, weekday N, unixepoch, localtime, and utc.
Add or subtract components
select datetime('2019-11-06', '+1 days'); -- 2019-11-07 00:00:00
select datetime('2019-11-07', '-4 hours'); -- 2019-11-06 20:00:00
select datetime('2019-11-06', '+2 days', '-4 hours'); -- 2019-11-07 20:00:00
Start of a period
select datetime('2019-11-07 21:16', 'start of year'); -- 2019-01-01 00:00:00
select datetime('2019-11-07 21:16', 'start of month'); -- 2019-11-01 00:00:00
select datetime('2019-11-07 21:16', 'start of day'); -- 2019-11-07 00:00:00
Next weekday
weekday N advances to the next specified weekday, where Sunday is 0 and Saturday is 6.
select datetime('2019-11-07', 'weekday 0'); -- 2019-11-10
select datetime('2019-11-07', 'weekday 3'); -- 2019-11-13
Unix timestamps and time zones
select datetime('35'); -- Julian-day interpretation
select datetime('35', 'unixepoch'); -- 1970-01-01 00:00:35
select datetime('now'), datetime('now', 'localtime');
select datetime('2019-05-14 10:25'), datetime('2019-05-14 10:25', 'utc');
localtime converts a UTC value to local time. utc treats its input as local time and converts it to UTC.