This information is obsolete. You are looking at the
CVSTrac source management system display
for SQLite that was replaced by
Fossil on 2009-08-11. The
information shown here has not been updated since that cut-over.
These pages are retained for historical
reference only.

The document describes default date and time functions in SQLite.
This document is a supplement to the
function documentation found on the
SQL Expression Syntax page.

Function Overview

Five date and time functions are available, as follows:

date( timestring, modifier, modifier, ...)

time( timestring, modifier, modifier, ...)

datetime( timestring, modifier, modifier, ...)

julianday( timestring, modifier, modifier, ...)

strftime( format, timestring, modifier, modifier, ...)

All five functions take a time string as an argument. This
time string may be followed by zero or more modifiers. The
strftime() function also takes a format string as its first
argument.

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 the number of days since noon in Greenwich on November 24, 4714 B.C.
The julian day number is the preferred internal representation of
dates. The strftime() routine returns the date formatted according
to the format string specified as the first argument. The format string
supports most, but not all, of the more common substitutions found in
the strftime() function from the standard C library:

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:

YYYY-MM-DD

YYYY-MM-DD HH:MM

YYYY-MM-DD HH:MM:SS

YYYY-MM-DD HH:MM:SS.SSS

YYYY-MM-DDTHH:MM

YYYY-MM-DDTHH:MM:SS

YYYY-MM-DDTHH:MM:SS.SSS

HH:MM

HH:MM:SS

HH:MM:SS.SSS

now

DDDD.DDDD

In formats 5 through 7, the "T" is a literal character separating the date and the time, as required by the ISO-8601 standard. These formats are supported in SQLite 3.2.0 and later.
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.
Universal Coordinated Time (UTC) is used.
Format 12 is the julian day number expressed as a floating point value.

Modifiers

The time string can be followed by zero or more modifiers that alter the
date or alter the interpretation of the date. The available modifiers
are as follows.

NNN days

NNN hours

NNN minutes

NNN.NNNN seconds

NNN months (see #551 and [1163])

NNN years (see #551 and [1163])

start of month

start of year

start of week (withdrawn -- will not be implemented)

start of day

weekday N (see #551 and [1163])

unixepoch

localtime

utc

The first six modifiers (1 through 6) simply add the specified amount
of time to the date specified by the preceding timestring.

The "start of" modifiers (7 through 10) shift the date backwards to
the beginning of the current month, year or day.

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.

The "unixepoch" modifier (12) only works if it immediately follows
a timestring in the DDDDDDDDDD format. This modifier causes the DDDDDDDDDD
to be interpreted not as a julian day number as it normally would be, but
as the number of seconds since 1970. This modifier allows unix-based times
to be converted to julian day numbers easily.

The "localtime" modifier (13) adjusts the previous time string so that it
displays the correct local time. "utc" undoes this.

Examples

Compute the current date.

SELECT date('now');

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');

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.

SELECT strftime('%s','now');

Compute the number of days since the battle of Hastings.

SELECT julianday('now') - julianday('1066-10-14','gregorian');

Compute the number of seconds between two dates:

SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');

Compute the date of the first Tuesday in October (January + 9) for the current
year.

SELECT date('now','start of year','+9 months','weekday 2');

Compute the time since the epoch in seconds (like strftime('%s','now') except includes fractional part):

SELECT (julianday('now') - 2440587.5)*86400.0;

Caveats And Bugs

The computation of local time depends heavily on the whim of local
politicians and is thus difficult to get correct for all locales. In
this implementation, the standard C library function localtime() (or thread-safe equivalents) is
used to assist in the calculation of local time.
Also, the localtime() C function normally only works for years between
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.

Date computations do not give correct results for dates before Julian
day number 0 (-4713-11-24 12:00:00).

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 better getting results correct back to 1986, when the rules were also changed.