Documentation Source Text

  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  if {[regexp {\(([0-9.]+)\)} $date all vers]} {
    set xrefChng($vers) $nChng
  }
  incr nChng
}

chng {2022-03-39 (3.38.0)} {
<li> Enhancements to date/time functions:
     <ol type="a">
     <li> Added the unixepoch() function.
     <li> Added modifiers 'auto' and 'julianday'
     </ol>

<p><b>Hashes:</b>
<li>SQLITE_SOURCE_ID: (pending)
<li>SHA3-256 for sqlite3.c: (pending)
}

chng {2021-11-27 (3.37.0)} {
<li> [STRICT tables] provide a prescriptive style of data type management,
     for developers who prefer that kind of thing.
<li> When adding columns that contain a
     [CHECK constraint] or a [generated column] containing a [NOT NULL constraint],
     the [ALTER TABLE ADD COLUMN] now checks new constraints against

#    ORDER BY mtime DESC;
#
# A small amount of manual editing and de-duplication followed.
#
# Manually edit the list for each subsequent release.
#
foreach line [split {
xxxxxxxxxx|pending|Version 3.38.0
bd41822c74|2021-11-27|Version 3.37.0
5c9a6c0687|2021-06-18|Version 3.36.0
1b256d97b5|2021-04-19|Version 3.35.5
5d4c65779d|2021-04-02|Version 3.35.4
4c5e6c200a|2021-03-26|Version 3.35.3
ea80f3002f|2021-03-17|Version 3.35.2
aea12399bf|2021-03-15|Version 3.35.1

<p>
SQLite [https://sqlite.org/src|source code]
is in the [public-domain] and is free to 
everyone to use for any purpose.

<h3>Latest Release</h3>
<a href="releaselog/3_37_0.html">Version 3.38.0</a> ([dateof:3.38.0]).
<a class="button" href="download.html">Download</a>
<a class="button" href="chronology.html">Prior Releases</a>

<div class="mobileonly">
<h3>Common Links</h3>
<tcl>common_links</tcl>
</div>

<title>Date And Time Functions</title>
<tcl>
hd_keywords *datefunc {date and time functions} {Date And Time Functions}
hd_keywords {date() SQL function} {time() SQL function}
hd_keywords {datetime() SQL function} {julianday() SQL function}
hd_keywords {strftime() SQL function}
</tcl>

<table_of_contents>
<h1>Overview</h1>

<p>
SQLite supports six date and time functions as follows:
</p>

<p>
<ol>
<li> ^(<b>date(</b><i>time-value, modifier, modifier, ...</i><b>)</b>)^ </li>
<li> ^(<b>time(</b><i>time-value, modifier, modifier, ...</i><b>)</b>)^ </li>
<li> ^(<b>datetime(</b><i>time-value, modifier, modifier, ...</i><b>)</b>)^ </li>
<li> ^(<b>julianday(</b><i>time-value, modifier, modifier, ...</i><b>)</b>)^ </li>
<li> ^(<b>unixepoch(</b><i>time-value, modifier, modifier, ...</i><b>)</b>)^ </li>
<li> ^(<b>strftime(</b><i>format, time-value, modifier, modifier, ...</i><b>)</b>)^ </li>
</ol>

<p>
^All six date and time functions take an optional time value as an argument, followed
by zero or more modifiers.
^The strftime() function also takes a format string as its first argument.
</p>

<p>
Date and time values can be stored as
<ul>
<li> text in a subset of the [http://en.wikipedia.org/wiki/ISO_8601 | IS0-8601] format,
<li> numbers representing the [http://en.wikipedia.org/wiki/Julian_day | Julian day], or
<li> numbers representing the number of seconds since (or before) 1970-01-01 00:00:00 UTC
     (the unix timestamp).
</ul>

<p>
All of the date time functions access time-values in any of the above time formats.

<p>
^The date() function returns the date as text in this format: YYYY-MM-DD.

<p>
^The time() function returns the time as text HH:MM:SS.

<p>
^The datetime() function returns the date and time as text like this: "YYYY-MM-DD HH:MM:SS". 

<p>
^(The julianday() function returns the 
[http://en.wikipedia.org/wiki/Julian_day | Julian day] - the
number of days since noon in Greenwich on November 24, 4714 B.C. 
([http://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar | Proleptic Gregorian calendar]).)^

<p>
^The unixepoch() function returns a unix timestamp - the number of seconds
since 1970-01-01 00:00:00 UTC.  ^The unixepoch() always returns an integer,
even if the input time-value has millisecond precision.

<p>
^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 
[http://opengroup.org/onlinepubs/007908799/xsh/strftime.html | strftime() function]
from the standard C library plus two new substitutions, %f and %J.
^(The following is a complete list of valid strftime() substitutions:
</p>

<blockquote>
<table border="0" cellpadding="0" cellspacing="0">
<tr><td><b>Function</b><td width="30"><td><b>Equivalent strftime()</b>
<tr><td>   date(...)      <td><td>  strftime('%Y-%m-%d', ...)
<tr><td>   time(...)      <td><td>  strftime('%H:%M:%S', ...)
<tr><td>   datetime(...)  <td><td>  strftime('%Y-%m-%d %H:%M:%S', ...)
<tr><td>   julianday(...) <td><td>  strftime('%J', ...)
<tr><td>   unixepoch(...) <td><td>  strftime('%s', ...)
</table>
</blockquote>)^

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

of the [sqlite3_vfs] object in use.
^The 'now' argument to date and time functions always returns exactly the
same value for multiple invocations within the same [sqlite3_step()] call.
^[http://en.wikipedia.org/wiki/Coordinated_Universal_Time | Universal Coordinated Time (UTC)] is used. 
^Format 12 is the 
[http://en.wikipedia.org/wiki/Julian_day | Julian day number]
expressed as an integer or floating point value.
^Format 12 might also be interpreted as a unix timestamp if it is immediately followed
either the 'auto' or 'unixepoch' modifier.
</p>

<p>
Formats 2 through 10 may be optionally followed by a timezone indicator of the form
"<i>&#91;+-&#93;HH:MM</i>" or just "<i>Z</i>".  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.

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.
</p>

<p>
^The time-value (and all modifiers) may be omitted, in which case a time
value of 'now' is assumed.

<h1>Modifiers</h1>

<p>^The time value can be followed by zero or more modifiers that 
alter date and/or time. ^Each modifier
is 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.</p>

<ol>
<li> NNN days
<li> NNN hours
<li> NNN minutes
<li> NNN.NNNN seconds
<li> NNN months
<li> NNN years
<li> start of month
<li> start of year
<li> start of day
<li> weekday N
<li> unixepoch
<li> julianday
<li> auto
<li> localtime
<li> utc 
</ol>)^

<p>^The first six modifiers (1 through 6) 
simply add the specified amount of time to the date and time 
specified by the arguments to the left.

of seconds since 1970 or if other modifiers
separate the "unixepoch" modifier from prior DDDDDDDDDD then the
behavior is undefined.
For SQLite versions before 3.16.0 ([dateof:3.16.0]), 
the "unixepoch" modifier only works for
dates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (unix times
of -62167219200 through 106751991167).</p>

<p>^The "julianday" modifier must immediately follow the initial
time-value which must be of the form DDDDDDDDD.  ^Any other use of
the 'julianday' modifier is an error and causes the function to return NULL.
^The 'julianday' modifier forces the time-value number to be interpreted
as a julian-day number.  As this is the default behavior, the 'julianday'
modifier is scarcely more than a no-op.  ^The only difference is that
adding 'julianday' forces the DDDDDDDDD time-value format, and causes
a NULL to be returned if any other time-value format is used.

<p>^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 day
number or a unix timestamp, depending on its magnitude.  If the value
is between 0.0 and 5373484.499999, then it is interpreted as a julian
day number (corresponding to dates between
-4713-11-24 12:00:00 and 9999-12-31 23:59:59, inclusive).  For numeric
values outside of the range of valid julian day numbers, but within 
the range of -210866760000 to 253402300799, the 'auto' modifier causes
the value to be interpreted as a unix timestamp.  Other numeric values
are out of range and cause a NULL return.  The 'auto' modifier is a no-op 
for text time-values.

<p>The 'auto' modifier can be used to work with date/time values even in
cases where it is not know if the julian day number of unix timestamp
formats are in use.  The 'auto' modifier will automatically select the
appropriate format.  However, there is a region of ambiguity.  Unix
timestamps for the first 63 days of 1970 will be interpreted as julian
day numbers.  The 'auto' modifier is very useful is the dataset is
guaranteed to not contain any dates within that region, but should be
avoided for applications that might make use of dates in the opening
months of 1970.

<tcl>hd_fragment localtime {localtime modifier} {'utc' modifier}</tcl>
<p>^The "localtime" modifier (14) assumes the time value to its left is in
Universal Coordinated Time (UTC) and adjusts that time
value 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 value
to 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" is
undefined.</p>

<h1>Examples</h1>

^(<p>Compute the current date.<p>

<blockquote>SELECT date();</blockquote>)^

^(<p>Compute the last day of the current month.</p>

<blockquote>SELECT date('now','start of month','+1 month','-1 day');
</blockquote>)^

^(<p>Compute the date and time given a unix timestamp 1092941466.</p>

<blockquote>
    SELECT datetime(1092941466, 'unixepoch');<br>
    SELECT datetime(1092941466, 'auto'); -- Does not work for early 1970!
</blockquote>)^

^(<p>Compute the date and time given a unix timestamp 1092941466, and 
compensate for your local timezone.</p>

<blockquote>
  SELECT datetime(1092941466, 'unixepoch', 'localtime');
</blockquote>)^

^(<p>Compute the current unix timestamp.</p>

<blockquote>
  SELECT unixepoch();<br>
  SELECT strftime('%s');
</blockquote>)^

^(<p>Compute the number of days since the signing of the US Declaration
of Independence.</p>

<blockquote>
  SELECT julianday('now') - julianday('1776-07-04');
</blockquote>)^

^(<p>Compute the number of seconds since a particular moment in 2004:</p>

<blockquote>
  SELECT unixepoch() - unixepoch('2004-01-01 02:34:56');
</blockquote>)^

^(<p>
Compute the date of the first Tuesday in October
for the current year.
</p>

<blockquote>
  SELECT date('now','start of year','+9 months','weekday 2');
</blockquote>)^

^(<p>Compute the time since the unix epoch in seconds with
millisecond precision:</p>

<blockquote>
  SELECT (julianday('now') - 2440587.5)*86400.0;
</blockquote>)^

<h1>Caveats And Bugs</h1>

<p>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 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.)^</p>


<p>^(These functions only work for dates between 0000-01-01 00:00:00
and 9999-12-31 23:59:59 (julian day numbers 1721059.5 through 5373484.5).)^
For dates outside that range, the results of these
functions are undefined.</p>

<p>Non-Vista Windows platforms only support one set of DST rules.