The document describes default date and time functions in SQLite.
This document is a supplement to the
function documentation found on the
{link: /lang_expr.html#corefunctions SQL Expression Syntax} page.

*Function Overview*

Five date and time functions are available, as follows:

1: date( _timestring_, _modifier_, _modifier_, ...)
2: time( _timestring_, _modifier_, _modifier_, ...)
3: datetime( _timestring_, _modifier_, _modifier_, ...)
4: julianday( _timestring_, _modifier_, _modifier_, ...)
5: 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:

   %d  day of month
   %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  sunday==0
   %W  week of year 00-53
   %Y  year 0000-9999
   %%  %

The %f and %J conversions are new.  Notice that all of the other four
functions could be expressed in terms of *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: 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.

1: NNN days
2: NNN hours
3: NNN minutes
4: NNN.NNNN seconds
5: NNN months  (see #551 and [1163])
6: NNN years  (see #551 and [1163])
7: start of month
8: start of year
9: start of week  (withdrawn -- will not be implemented)
10: start of day
11: weekday N  (see #551 and [1163])
12: unixepoch
13: localtime
14: 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 julianday('now')*86400 - julianday('2004-01-01 02:34:56')*86400;

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

*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() is
used to assist in the calculation of local time.
Note that localtime() is not
threadsafe, so use of the "localtime" modifier is not threadsafe.
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.

_Please surround uses of localtime() with sqliteOsEnterMutex() and sqliteOsLeaveMutex() so threads
using SQLite are protected, at least!
-- e_  It is so. --drh

_[Consider instead, using localtime_r which is reentrant and may be used
*without* expensive mutex locking. Although non-standard it's available
on most Unixes --hauk]_ But it is not available on windows, as far as I
am aware. --drh On windows localtime() is thread-safe if the MT C runtime is used. The MT runtime uses thread-local storage for the static variables, the kind functions use.--gr _[What about using localtime_r, and on systems where it
is unavailable defining it as sqliteOsEnterMutext() ; locatime() ; sqliteOsLeaveMutex()
so that non-windows systems get the maximum advantage, with almost zero
code impact?]_ The autoconfigury and patch for localtime_r is here: http://www.sqlite.org/cvstrac/tktview?tn=1906 . I'm curious why this obvious fix is not applied. gmtime() also suffers from this same threadsafety problem.

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

All internal computations assume the Gregorian calendar system.

----
_An anonymous user adds:_ {linebreak}
For my use I added new functions and functionalities to the date functions that
come with the sqlite 3.3.0 (can be used in older versions as well with small effort).

In main lines they are as follows:

1: NNN days
2: NNN hours
3: NNN minutes
4: NNN.NNNN seconds
5: NNN months  (see #551 and [1163])
6: NNN years  (see #551 and [1163])
7: start of month
8: start of year
9: start of week  (!!! implemented)
10: start of day
11: weekday N  (see #551 and [1163])
12: unixepoch
13: localtime
14: utc
15: julian  (not implemented as of 2004-01-05)
16: gregorian  (not implemented as of 2004-01-05)
17: start of minute
18: start of hour
19: end of minute
20: end of hour
21: end of day
22: end of week
23: end of month
24: end of year
25: group seconds by
26: group minutes by
27: group hours by
28: group days by
29: group weeks by
30: group months by
31: group years by

The "start of" modifiers (7 through 10 and 17 through 18) shift the date backwards to the beginning of the current minute, hour, week, month, year or day.

The "end of" modifiers (19 through 24) shift the date forwards to
the end of the current minute, hour, week, month, year or day.

The "group * by" modifiers (25 through 31) round the date to the closest backward multiple supplied, with some limitations, to the current seconds (1 through 30), minutes (1 through 30), hours (1 through 12), days (1 through 15), weeks (1 through 26), months (1 through 6), years (1 through 100), these limitations are due to dont complicate the calculations when a multiple can span beyound the unit modified.

Ex:

SELECT datetime('2006-02-04 20:09:23','group hours by 3'); => '2006-02-04 18:00:00'

SELECT datetime('2006-02-05 20:09:23','group days by 3'); => '2006-02-04 00:00:00'

New functions "week_number(date)" returns the week number of the year on the supplied date parameter, "datetime2seconds(datetime)" return the number of seconds from the supplied datetime parameter.

The diff file ready to be applied to the file "date.c" in the src directory of sqlite 3.3.0 is at http://dad-it.com:8080/date.c.diff, I hope it's considered valuable to be merged in the official distribution.