Date And Time Functions
2. Time Values
5. Caveats And Bugs
SQLite supports seven date and time functions as follows:
- date(time-value, modifier, modifier, ...)
- time(time-value, modifier, modifier, ...)
- datetime(time-value, modifier, modifier, ...)
- julianday(time-value, modifier, modifier, ...)
- unixepoch(time-value, modifier, modifier, ...)
- strftime(format, time-value, modifier, modifier, ...)
- timediff(time-value, time-value)
The first six date and time functions take an optional time value as an argument, followedby zero or more modifiers.The strftime() function also takes a format string as its first argument.The timediff() function takes exactly two arguments which are both time values.
Date and time values can be stored as
- text in a subset of the ISO-8601 format,
- numbers representing the Julian day, or
- numbers representing the number of seconds since (or before) 1970-01-01 00:00:00 UTC (the unix timestamp).
All of the date time functions access time-values as either ISO-8601 strings orJulian day numbers. They also access unix timestamps with optional arguments(the 'auto' and 'unixepoch' modifiers described below). Since the timediff()function does not accept any optional argument, it can only use ISO-8601 andJulian day number time values.
The unixepoch() function returns a unix timestamp - the number of secondssince 1970-01-01 00:00:00 UTC. The unixepoch() function normally returnsan integer number of seconds, but with the optional subsec modifier itwill return a floating point number which is the fractional number of seconds.
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 the strftime() 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 (fractional) %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 %% %
Other date and time functions can be expressedin terms of strftime():
Function Equivalent (or nearly) strftime() date(...) strftime('%Y-%m-%d', ...) time(...) strftime('%H:%M:%S', ...) datetime(...) strftime('%Y-%m-%d %H:%M:%S', ...) julianday(...) strftime('%J', ...) -- (numeric return) unixepoch(...) strftime('%s', ...) -- (numeric return)
The date(), time(), and datetime() functions all return text, and so theirstrftime() equivalents are exact. However, the julianday()and unixepoch() functions return numeric values. Their strftime() equivalentsreturn a string that is the text representation of the corresponding number.
The main reasons for providing functions other than strftime() arefor convenience and for efficiency. The julianday() and unixepoch()functions return real and integer values respectively, and do notincur the format conversion costs or inexactitude resulting from useof the '%J' or '%s' format specifiers with the strftime() function.
The timediff(A,B) routine returns the a string that describe the amountof time that must be added to B in order to reach time A. The format ofthe timediff() result is designed to be human-readable. The format is:
This time difference string is also an allowed modifier for the otherdate/time functions. The following invariant holds for time values Aand B:
datetime(A) = datetime(B, timediff(A,B))
The length of months and years vary. February is shorter than March.Leap years are longer than non-leap years. The output from timediff()takes this all into account. The timediff() function is intended to providea human-friendly description of the time span. If you want to know thenumber of days or seconds between two dates, A and B, then you can always doone of these:
SELECT julianday(B) - julianday(A);
SELECT unixepoch(B) - unixepoch(A);
The timediff(A,B) might return the same result even for values A and Bthat span a different number of days - depending on the starting date.For example, both of the following two timediff() calls return thesame result ("+0000-01-00 00:00:00.000") even though the first timespanis 28 days and the seconds is 31 days:
Summary: If you want a human-friendly time span, use timediff(). If youwhat a precise time difference (in days or seconds) use the differencebetween two julianday() or unixepoch() calls.
A time value can be in any of the following formats shown below.The value is usually a string, though it can be an integer or floatingpoint number in the case of format 12.
- YYYY-MM-DD HH:MM
- YYYY-MM-DD HH:MM:SS
- YYYY-MM-DD HH:MM:SS.SSS
In formats 5 through 7, the "T" is a literal character separating the date and the time, as required by ISO-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 methodof the sqlite3_vfs object in use.The 'now' argument to date and time functions always returns exactly thesame value for multiple invocations within the same sqlite3_step() call.Universal Coordinated Time (UTC) is used. Format 12 is the Julian day numberexpressed as an integer or floating point value.Format 12 might also be interpreted as a unix timestamp if it is immediately followedeither the 'auto' or 'unixepoch' modifier.
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 issubtracted from the indicated date and time in order to compute zulu time.For example, all of the following time values are equivalent:
In formats 4, 7, and 10, the fractional seconds value SS.SSS can haveone or more digits following the decimal point. Exactly three digits areshown in the examples because only the first three digits are significantto the result, but the input string can have fewer or more than three digitsand the date/time functions will still operate correctly.Similarly, format 12 is shown with 10 significant digits, but the date/timefunctions will really accept as many or as few digits as are necessary torepresent the Julian day number.
For all date/time functions other than timediff(),the time value argument can be followed by zero or more modifiers that alter date and/or time. Each modifieris a transformation that is applied to the time value to its left.Modifiers are applied from left to right; order is important.The available modifiers are as follows.
- NNN days
- NNN hours
- NNN minutes
- NNN seconds
- NNN months
- NNN years
- ±YYYY-MM-DD HH:MM
- ±YYYY-MM-DD HH:MM:SS
- ±YYYY-MM-DD HH:MM:SS.SSS
- start of month
- start of year
- start of day
- weekday N
The first thirteen modifiers (1 through 13) add the specified amount of time to the date and time specified by the arguments to its left.The 's' character at the end of the modifier names in 1 through 6 is optional.The NNN value can be any floating point number, with an optional '+' or '-' prefix.Note that "±NNN months" works by rendering the original date intothe YYYY-MM-DD format, adding the ±NNN to the MM month value, thennormalizing the result. Thus, for example, the date 2001-03-31 modifiedby '+1 month' initially yields 2001-04-31, but April only has 30 daysso the date is normalized to 2001-05-01. A similar effect occurs whenthe original date is February 29 of a leapyear and the modifier is±N years where N is not a multiple of four.
The time shift modifiers (7 through 13) move the time value by thenumber of years, months, days, hours, minutes, and/or seconds specified.An initial "+" or "-" is required for formats 10 through 13 but is optionalfor formats 7, 8, and 9. The changes are applies from left to right.First the year is shifted by YYYY, then the month by MM, and then dayby DD, and so forth. The normalization and rounding due to differing monthlengths and leap years is applied after each step. Thetimediff(A,B) function returns a time shift in format 13 that shiftsthe time value B into A.
The "start of" modifiers (14 through 16) shift the date backwards to the beginning of the subject month, year or day.
The "weekday" modifier advances the date forward, if necessary,to the next date where the weekday number is N. Sunday is 0, Monday is 1,and so forth.If the date is already on the desired weekday, the "weekday" modifierleaves the date unchanged.
The "unixepoch" modifier (18) only works if it immediately follows a time value in the DDDDDDDDDD format. This modifier causes the DDDDDDDDDD to be interpreted not as a Julian day number as it normally would be, but asUnix Time - the number of seconds since 1970. If the "unixepoch" modifier does notfollow a time value of the form DDDDDDDDDD which expresses the numberof seconds since 1970 or if other modifiersseparate the "unixepoch" modifier from prior DDDDDDDDDD then thebehavior is undefined.For SQLite versions before 3.16.0 (2017-01-02), the "unixepoch" modifier only works fordates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (unix timesof -62167219200 through 106751991167).
The "julianday" modifier must immediately follow the initialtime-value which must be of the form DDDDDDDDD. Any other use ofthe 'julianday' modifier is an error and causes the function to return NULL.The 'julianday' modifier forces the time-value number to be interpretedas a julian-day number. As this is the default behavior, the 'julianday'modifier is scarcely more than a no-op. The only difference is thatadding 'julianday' forces the DDDDDDDDD time-value format, and causesa NULL to be returned if any other time-value format is used.
The "auto" modifier must immediately follow the initial time-value.If the time-value is numeric (the DDDDDDDDDD format) then the 'auto'modifier causes the time-value to interpreted as either a julian daynumber or a unix timestamp, depending on its magnitude. If the valueis between 0.0 and 5373484.499999, then it is interpreted as a julianday number (corresponding to dates between-4713-11-24 12:00:00 and 9999-12-31 23:59:59, inclusive). For numericvalues outside of the range of valid julian day numbers, but within the range of -210866760000 to 253402300799, the 'auto' modifier causesthe value to be interpreted as a unix timestamp. Other numeric valuesare out of range and cause a NULL return. The 'auto' modifier is a no-op for text time-values.
The 'auto' modifier can be used to work with date/time values even incases where it is not known if the julian day number or unix timestampformats are in use. The 'auto' modifier will automatically select theappropriate format. However, there is a region of ambiguity. Unixtimestamps for the first 63 days of 1970 will be interpreted as julianday numbers. The 'auto' modifier is very useful when the dataset isguaranteed to not contain any dates within that region, but should beavoided for applications that might make use of dates in the openingmonths of 1970.
The "localtime" modifier (21) assumes the time value to its left is inUniversal Coordinated Time (UTC) and adjusts that timevalue so that it is in localtime. If "localtime"follows a time that is not UTC, then the behavior is undefined.The "utc" modifier is the opposite of "localtime". "utc" assumes that the time valueto its left is in the local timezone and adjusts that time value to be in UTC.If the time to the left is not in localtime, then the result of "utc" isundefined.
The "subsecond" modifier (which may be abbreviated as just"subsec") increases the resolution of the output fordatetime(), time(), and unixepoch(), and for the "%s"format string in strftime(). The "subsecond"modifier has no effect on other date/time functions.The current implemention increases the resolution from secondsto milliseconds, but this might increase to a higher resolutionin future releases of SQLite. When "subsec" is used withdatetime() or time(), the seconds field at the end isfollowed by a decimal point and one or more digits to showfractional seconds. When "subsec" is used with unixepoch(),the result is a floating point value which is the number ofseconds and fractional seconds since 1970-01-01.
The "subsecond" and "subsec" modifiers have the special propertythat they can occur as the first argument to date/time functions(or as the first argument after the format string for strftime()).When this happens, the time value that is normally in the firstargument is understood to be "now". For example, a short cut toget the current time in seconds since 1970 with millisecondprecision is to say:
Compute the current date.
Compute the last day of the current month.
SELECT date('now','start of month','+1 month','-1 day');
Compute the date and time given a unix timestamp 1092941466.
SELECT datetime(1092941466, 'unixepoch');
SELECT datetime(1092941466, 'auto'); -- Does not work for early 1970!
Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.
SELECT datetime(1092941466, 'unixepoch', 'localtime');
Compute the current unix timestamp.
Compute the number of days since the signing of the US Declarationof Independence.
SELECT julianday('now') - julianday('1776-07-04');
Compute the number of seconds since a particular moment in 2004:
SELECT unixepoch() - unixepoch('2004-01-01 02:34:56');
Compute the date of the first Tuesday in Octoberfor the current year.
SELECT date('now','start of year','+9 months','weekday 2');
Compute the time since the unix epoch in seconds withmillisecond precision:
SELECT (julianday('now') - 2440587.5)*86400.0;
Compute how old Abraham Lincoln would be if he were still alive today:
The computation of local time depends heavily on the whim of politicians and is thus difficult to get correct for all locales. In this implementation, the standard C library function localtime_r() is used to assist in the calculation of local time. The localtime_r() C function normally only works for yearsbetween 1970 and 2037. For dates outside this range, SQLite attempts to map the year into an equivalent year within this range, do the calculation, then map the year back.
These functions only work for dates between 0000-01-01 00:00:00and 9999-12-31 23:59:59 (julian day numbers 1721059.5 through 5373484.5).For dates outside that range, the results of thesefunctions are undefined.
Non-Vista Windows platforms only support one set of DST rules. Vista only supports two. Therefore, on these platforms, historical DST calculations will be incorrect. For example, in the US, in 2007 the DST rules changed. Non-Vista Windows platforms apply the new 2007 DST rules to all previous years as well. Vista does somewhat bettergetting results correct back to 1986, when the rules were also changed.
All internal computations assume the Gregorian calendarsystem. They also assume that everyday is exactly 86400 seconds in duration; no leap seconds are incorporated.
This page last modified on 2023-08-01 13:58:54 UTC