Date And Time Functions

sqlite supports five date and time functions as follows:

1. date(timestring,modifier,...)
2. time(timestring,...)
3. datetime(timestring,...)
4. julianday(timestring,...)
5. strftime(format,timestring,...)

All five date and time functions take a time string as an argument. The time string is 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 ofIS0-8601date and time formats. The date() function returns the date in this format: YYYY-MM-DD. The time() function returns the time as HH:MM:SS. The datetime() function returns "YYYY-MM-DD HH:MM:SS". The julianday() function returns theJulian day- the number of days since noon in Greenwich on November 24,4714 B.C. (Proleptic Gregorian calendar). The strftime() routine returns the date formatted according to the format string specified as the first argument. The format string supports the most common substitutions found in thestrftime() functionfrom the standard C library plus two new substitutions,%f and %J. The following is a complete list of valid strftime() substitutions:

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

Notice that all other date and time functions can be expressed in terms of strftime():

Function		Equivalent strftime()
date(...)		strftime('%Y-%m-%d',...)
time(...)		strftime('%H:%M:%S',...)
datetime(...)		strftime('%Y-%m-%d %H:%M:%S',...)
julianday(...)		strftime('%J',...)

The only reasons for providing functions other than strftime() is for convenience and for efficiency.

Time Strings

A time string can be in any of the following formats:

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now
12. DDDDDDDDDD

In formats 5 through 7,the "T" is a literal character separating the date and the time,as required byISO-8601. Formats 8 through 10 that specify only a time assume a date of 2000-01-01. Format 11,the string 'now',is converted into the current date and time as obtained from the xCurrentTime method of thesqlite3_vfsobject in use. The 'now' argument to date and time functions always returns exactly the same value for multiple invocations within the samesqlite3_step()call.Universal Coordinated Time (UTC)is used. Format 12 is theJulian day numberexpressed as a floating point value.

Formats 2 through 10 may be optionally followed by a timezone indicator of the form "[+-]HH:MM" or just "Z". The date and time functions use UTC or "zulu" time internally,and so the "Z" suffix is a no-op. Any non-zero "HH:MM" suffix is subtracted from the indicated date and time in order to compute zulu time. For example,all of the following time strings are equivalent:

2013-10-07 08:23:19.120
2013-10-07T08:23:19.120Z
2013-10-07 08:23:19.120-04:00
2456572.84952685

In formats 4,7,and 10,the fractional seconds value SS.SSS can have one or more digits following the decimal point. Exactly three digits are shown in the examples because only the first three digits are significant to the result,but the input string can have fewer or more than three digits and the date/time functions will still operate correctly. Similarly,format 12 is shown with 10 significant digits,but the date/time functions will really accept as many or as few digits as are necessary to represent the Julian day number.