diff options
Diffstat (limited to 'www/lang_datefunc.html')
| -rw-r--r-- | www/lang_datefunc.html | 541 |
1 files changed, 541 insertions, 0 deletions
diff --git a/www/lang_datefunc.html b/www/lang_datefunc.html new file mode 100644 index 0000000..c2ab1a5 --- /dev/null +++ b/www/lang_datefunc.html @@ -0,0 +1,541 @@ +<!DOCTYPE html> +<html><head> +<meta name="viewport" content="width=device-width, initial-scale=1.0"> +<meta http-equiv="content-type" content="text/html; charset=UTF-8"> +<link href="sqlite.css" rel="stylesheet"> +<title>Date And Time Functions</title> +<!-- path= --> +</head> +<body> +<div class=nosearch> +<a href="index.html"> +<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0"> +</a> +<div><!-- IE hack to prevent disappearing logo --></div> +<div class="tagline desktoponly"> +Small. Fast. Reliable.<br>Choose any three. +</div> +<div class="menu mainmenu"> +<ul> +<li><a href="index.html">Home</a> +<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> +<li class='wideonly'><a href='about.html'>About</a> +<li class='desktoponly'><a href="docs.html">Documentation</a> +<li class='desktoponly'><a href="download.html">Download</a> +<li class='wideonly'><a href='copyright.html'>License</a> +<li class='desktoponly'><a href="support.html">Support</a> +<li class='desktoponly'><a href="prosupport.html">Purchase</a> +<li class='search' id='search_menubutton'> +<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> +</ul> +</div> +<div class="menu submenu" id="submenu"> +<ul> +<li><a href='about.html'>About</a> +<li><a href='docs.html'>Documentation</a> +<li><a href='download.html'>Download</a> +<li><a href='support.html'>Support</a> +<li><a href='prosupport.html'>Purchase</a> +</ul> +</div> +<div class="searchmenu" id="searchmenu"> +<form method="GET" action="search"> +<select name="s" id="searchtype"> +<option value="d">Search Documentation</option> +<option value="c">Search Changelog</option> +</select> +<input type="text" name="q" id="searchbox" value=""> +<input type="submit" value="Go"> +</form> +</div> +</div> +<script> +function toggle_div(nm) { +var w = document.getElementById(nm); +if( w.style.display=="block" ){ +w.style.display = "none"; +}else{ +w.style.display = "block"; +} +} +function toggle_search() { +var w = document.getElementById("searchmenu"); +if( w.style.display=="block" ){ +w.style.display = "none"; +} else { +w.style.display = "block"; +setTimeout(function(){ +document.getElementById("searchbox").focus() +}, 30); +} +} +function div_off(nm){document.getElementById(nm).style.display="none";} +window.onbeforeunload = function(e){div_off("submenu");} +/* Disable the Search feature if we are not operating from CGI, since */ +/* Search is accomplished using CGI and will not work without it. */ +if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ +document.getElementById("search_menubutton").style.display = "none"; +} +/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ +function hideorshow(btn,obj){ +var x = document.getElementById(obj); +var b = document.getElementById(btn); +if( x.style.display!='none' ){ +x.style.display = 'none'; +b.innerHTML='show'; +}else{ +x.style.display = ''; +b.innerHTML='hide'; +} +return false; +} +var antiRobot = 0; +function antiRobotGo(){ +if( antiRobot!=3 ) return; +antiRobot = 7; +var j = document.getElementById("mtimelink"); +if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); +} +function antiRobotDefense(){ +document.body.onmousedown=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousedown=null; +} +document.body.onmousemove=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousemove=null; +} +setTimeout(function(){ +antiRobot |= 1; +antiRobotGo(); +}, 100) +antiRobotGo(); +} +antiRobotDefense(); +</script> +<div class=fancy> +<div class=nosearch> +<div class="fancy_title"> +Date And Time Functions +</div> +<div class="fancy_toc"> +<a onclick="toggle_toc()"> +<span class="fancy_toc_mark" id="toc_mk">►</span> +Table Of Contents +</a> +<div id="toc_sub"><div class="fancy-toc1"><a href="#overview">1. Overview</a></div> +<div class="fancy-toc1"><a href="#time_values">2. Time Values</a></div> +<div class="fancy-toc1"><a href="#modifiers">3. Modifiers</a></div> +<div class="fancy-toc1"><a href="#examples">4. Examples</a></div> +<div class="fancy-toc1"><a href="#caveats_and_bugs">5. Caveats And Bugs</a></div> +</div> +</div> +<script> +function toggle_toc(){ +var sub = document.getElementById("toc_sub") +var mk = document.getElementById("toc_mk") +if( sub.style.display!="block" ){ +sub.style.display = "block"; +mk.innerHTML = "▼"; +} else { +sub.style.display = "none"; +mk.innerHTML = "►"; +} +} +</script> +</div> + + + + +<h1 id="overview"><span>1. </span>Overview</h1> + +<p> +SQLite supports six date and time functions as follows: +</p> + +<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 +</p><ul> +<li> text in a subset of the <a href="http://en.wikipedia.org/wiki/ISO_8601">ISO-8601</a> format, +</li><li> numbers representing the <a href="http://en.wikipedia.org/wiki/Julian_day">Julian day</a>, or +</li><li> numbers representing the number of seconds since (or before) 1970-01-01 00:00:00 UTC + (the unix timestamp). +</li></ul> + +<p> +All of the date time functions access time-values in any of the above time formats. + +</p><p> +The date() function returns the date as text in this format: YYYY-MM-DD. + +</p><p> +The time() function returns the time as text in this format: HH:MM:SS. + +</p><p> +The datetime() function returns the date and time as text in their same formats: YYYY-MM-DD HH:MM:SS. + +</p><p> +The julianday() function returns the +<a href="http://en.wikipedia.org/wiki/Julian_day">Julian day</a> - the +fractional number of days since noon in Greenwich on November 24, 4714 B.C. +(<a href="http://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar">Proleptic Gregorian calendar</a>). + +<a name="uepch"></a> + +</p><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><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 +<a href="http://opengroup.org/onlinepubs/007908799/xsh/strftime.html">strftime() function</a> +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></td><td width="10"></td><td></td></tr> + +<tr><td> %d </td><td></td><td> day of month: 00 +</td></tr><tr><td> %f </td><td></td><td> fractional seconds: SS.SSS +</td></tr><tr><td> %H </td><td></td><td> hour: 00-24 +</td></tr><tr><td> %j </td><td></td><td> day of year: 001-366 +</td></tr><tr><td> %J </td><td></td><td> Julian day number (fractional) +</td></tr><tr><td> %m </td><td></td><td> month: 01-12 +</td></tr><tr><td> %M </td><td></td><td> minute: 00-59 +</td></tr><tr><td> %s </td><td></td><td> seconds since 1970-01-01 +</td></tr><tr><td> %S </td><td></td><td> seconds: 00-59 +</td></tr><tr><td> %w </td><td></td><td> day of week 0-6 with Sunday==0 +</td></tr><tr><td> %W </td><td></td><td> week of year: 00-53 +</td></tr><tr><td> %Y </td><td></td><td> year: 0000-9999 +</td></tr><tr><td> %% </td><td></td><td> % +</td></tr></table> +</blockquote> + +<p> +All other date and time functions can be expressed +in terms of strftime(): +</p> + +<blockquote> +<table border="0" cellpadding="0" cellspacing="0"> +<tr><td><b>Function</b></td><td width="30"></td><td><b>Equivalent (or nearly) strftime()</b> +</td></tr><tr><td> date(...) </td><td></td><td> strftime('%Y-%m-%d', ...) +</td></tr><tr><td> time(...) </td><td></td><td> strftime('%H:%M:%S', ...) +</td></tr><tr><td> datetime(...) </td><td></td><td> strftime('%Y-%m-%d %H:%M:%S', ...) +</td></tr><tr><td> julianday(...) </td><td></td><td> <nobr>strftime('%J', ...) -- note-1</nobr> +</td></tr><tr><td> unixepoch(...) </td><td></td><td> <nobr>strftime('%s', ...) -- note-1</nobr> +</td></tr></table> +</blockquote> + +<p> +The date(), time(), and datetime() functions all return text, and so their +strftime() equivalents are exact. However (<b>note-1</b>) the julianday() +and unixepoch() functions return numeric values. Their strftime() equivalents +return strings that is the text representation of the corresponding number. +</p> + +<p> +The main reasons for providing functions other than strftime() are +for convenience and for efficiency. The julianday() and unixepoch() +functions return real and integer values respectively, and do not +incur the format conversion costs or inexactitude resulting from use +of the '%J' or '%s' format specifiers with the strftime() function. +</p> + +<h1 id="time_values"><span>2. </span>Time Values</h1> + +<p>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 floating +point number in the case of format 12. + +</p><ol> +<li> <i>YYYY-MM-DD</i> +</li><li> <i>YYYY-MM-DD HH:MM</i> +</li><li> <i>YYYY-MM-DD HH:MM:SS</i> +</li><li> <i>YYYY-MM-DD HH:MM:SS.SSS</i> +</li><li> <i>YYYY-MM-DD</i><b>T</b><i>HH:MM</i> +</li><li> <i>YYYY-MM-DD</i><b>T</b><i>HH:MM:SS</i> +</li><li> <i>YYYY-MM-DD</i><b>T</b><i>HH:MM:SS.SSS</i> +</li><li> <i>HH:MM</i> +</li><li> <i>HH:MM:SS</i> +</li><li> <i>HH:MM:SS.SSS</i> +</li><li> <b>now</b> +</li><li> <i>DDDDDDDDDD</i> +</li></ol> + +<p> +In formats 5 through 7, the "T" is a literal character separating +the date and the time, as required by +<a href="http://www.w3c.org/TR/NOTE-datetime">ISO-8601</a>. +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 the <a href="c3ref/vfs.html">sqlite3_vfs</a> object in use. +The 'now' argument to date and time functions always returns exactly the +same value for multiple invocations within the same <a href="c3ref/step.html">sqlite3_step()</a> call. +<a href="http://en.wikipedia.org/wiki/Coordinated_Universal_Time">Universal Coordinated Time (UTC)</a> is used. +Format 12 is the +<a href="http://en.wikipedia.org/wiki/Julian_day">Julian day number</a> +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>[+-]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. +For example, all of the following time values are equivalent: +</p> + +<blockquote> +2013-10-07 08:23:19.120<br> +2013-10-07T08:23:19.120Z<br> +2013-10-07 04:23:19.120-04:00<br> +2456572.84952685 +</blockquote> + +<p> +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. +</p> + +<p> +The time-value (and all modifiers) may be omitted, in which case a time +value of 'now' is assumed. + +<a name="dtmods"></a> + +</p><h1 id="modifiers"><span>3. </span>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><li> NNN hours +</li><li> NNN minutes +</li><li> NNN.NNNN seconds +</li><li> NNN months +</li><li> NNN years +</li><li> start of month +</li><li> start of year +</li><li> start of day +</li><li> weekday N +</li><li> unixepoch +</li><li> julianday +</li><li> auto +</li><li> localtime +</li><li> utc +</li></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. +The 's' character at the end of the modifier names is optional. +Note that "±NNN months" works by rendering the original date into +the YYYY-MM-DD format, adding the ±NNN to the MM month value, then +normalizing the result. Thus, for example, the date 2001-03-31 modified +by '+1 month' initially yields 2001-04-31, but April only has 30 days +so the date is normalized to 2001-05-01. A similar effect occurs when +the original date is February 29 of a leapyear and the modifier is +±N years where N is not a multiple of four.</p> + +<p>The "start of" modifiers (7 through 9) shift the date backwards +to the beginning of the subject month, year or day.</p> + +<p>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" modifier +leaves the date unchanged. </p> + +<p>The "unixepoch" modifier (11) 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 as +<a href="http://en.wikipedia.org/wiki/Unix_time">Unix Time</a> - the +number of seconds since 1970. If the "unixepoch" modifier does not +follow a time value of the form DDDDDDDDDD which expresses the number +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 (2017-01-02), +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> + +<a name="jdmod"></a> + +<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. + +<a name="automod"></a> + +</p><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><p>The 'auto' modifier can be used to work with date/time values even in +cases where it is not known if the julian day number or 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 when 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. + +<a name="localtime"></a> + +</p><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 id="examples"><span>4. </span>Examples</h1> + +<p>Compute the current date.</p><p> + +</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 id="caveats_and_bugs"><span>5. </span>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. +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.</p> + +<p>All internal computations assume the +<a href="http://en.wikipedia.org/wiki/Gregorian_calendar">Gregorian calendar</a> +system. They also assume that every +day is exactly 86400 seconds in duration; no leap seconds are incorporated.</p> +<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/lang_datefunc.in?m=5e55e49a95b10d0d9">2022-01-27 15:08:03</a> UTC </small></i></p> + |