SQLite Date and Time Functions

Last update on November 14 2023 12:30:52 (UTC/GMT +8 hours)

Description

SQLite supports five date and time functions as follows:

Name	Syntax
date	date(timestring, modifier, modifier, ...)
time	time(timestring, modifier, modifier, ...)
datetime	datetime(timestring, modifier, modifier, ...)
julianday	julianday(timestring, modifier, modifier, ...)
strftime	strftime(format, timestring, modifier, modifier, ...)

The above mentioned date and time functions take a time string as an argument and followed by zero or more modifiers. The strftime() function also takes a format string as its first argument. The date and time functions use a subset date and time formats.

Here is the list of timestring format :

Name	Description
YYYY-MM-DD	Year Month and Date
YYYY-MM-DD HH:MM	Year Month Date Hour Minute
YYYY-MM-DD HH:MM:SS	Year Month Date Hour Minute Second
YYYY-MM-DD HH:MM:SS.SSS	Year Month Date Hour Minute Second miliseconds
YYYY-MM-DDTHH:MM	"T" is a literal character separating the date and the time,
YYYY-MM-DDTHH:MM:SS	"T" is a literal character separating the date and the time,
YYYY-MM-DDTHH:MM:SS.SSS	"T" is a literal character separating the date and the time,
HH:MM	specify only a time assume a date of 2000-01-01.
HH:MM:SS	specify only a time assume a date of 2000-01-01.
HH:MM:SS.SSS	specify only a time assume a date of 2000-01-01.
now	current date and time
DDDDDDDDDD	A Julian day number expressed as a floating point value.

Here is the list of modifiers:

Name	Description
NNN days	number of days
NNN hours	number of hours
NNN minutes	number of minutes
NNN.NNNN seconds	numhber of miliseconds
NNN months	numher of months
NNN years	number of years
start of month	The "start of" modifiers shift the date backwards to the beginning of the current month.
start of year	The "start of" modifiers shift the date backwards to the beginning of the current year.
start of day	The "start of" modifiers shift the date backwards to the beginning of the current day.
weekday N	The "weekday" modifier advances the date forward to the next date where the weekday number is N. Sunday is 0, Monday is 1, and so forth.
unixepoch	The "unixepoch" modifier only works if it immediately follows a timestring in the DDDDDDDDDD format.
localtime	The "localtime" modifier assumes the time string to its left is in Universal Coordinated Time (UTC) and adjusts the time string so that it displays localtime.
utc	The "utc" is the opposite of "localtime". "utc" assumes that the string to its left is in the local timezone and adjusts that string to be in UTC.

Here is the list of format specifiers:

Format	Description
%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
%%	%

Previous: upper()
Next: DATE