diff options
Diffstat (limited to 'www/lang_datefunc.html')
| -rw-r--r-- | www/lang_datefunc.html | 206 |
1 files changed, 117 insertions, 89 deletions
diff --git a/www/lang_datefunc.html b/www/lang_datefunc.html index 24d0338..7f4ff38 100644 --- a/www/lang_datefunc.html +++ b/www/lang_datefunc.html @@ -126,7 +126,6 @@ Date And Time Functions Table Of Contents </a> <div id="toc_sub"><div class="fancy-toc1"><a href="#overview">1. Overview</a></div> -<div class="fancy-toc2"><a href="#timediff_">1.1. Timediff()</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> @@ -169,47 +168,63 @@ SQLite supports seven <a href="lang_corefunc.html">scalar</a> date and time func </ol> <p> -The first six date and time functions take an optional time value as an argument, followed -by zero or more modifiers. +The first six date and time functions take an optional <a href="lang_datefunc.html#tmval">time-value</a> as an argument, followed +by zero or more <a href="lang_datefunc.html#dtmods">modifiers</a>. The strftime() function also takes a format string as its first argument. -The timediff() function takes exactly two arguments which are both time values. +The timediff() function takes exactly two arguments which are both <a href="lang_datefunc.html#tmval">time-values</a>. </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> +SQLite does not have a dedicated date/time datatype. Instead, +date and time values can stored as any of the following: + +</p><p></p><blockquote> +<table border="0" cellspacing="0" cellpadding="0"> +<tr> +<td><a href="http://en.wikipedia.org/wiki/ISO_8601">ISO-8601</a> +</td><td>A text string that is an ISO 8601 date/time value. +Example: <tt>'2025-05-29 14:16:00'</tt> +</td></tr><tr> +<td><a href="http://en.wikipedia.org/wiki/Julian_day">Julian day number</a> +</td><td>The number of days including fractional days since -4713-11-24 12:00:00 +Example: <tt>2460825.09444444</tt> +</td></tr><tr> +<td><a href="https://en.wikipedia.org/wiki/Unix_time">Unix timestamp</a> +</td><td>The number of seconds including fractional seconds since 1970-01-01 00:00:00 +Example: <tt>1748528160</tt> +</td></tr></table> +</blockquote> <p> -All of the date time functions access time-values as either ISO-8601 strings or -Julian 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 and -Julian day number time values. +These three formats are collectively known as <a href="lang_datefunc.html#tmval">time-values</a>. +All of the date time functions accept time-values as either ISO-8601 text or +as Julian day numbers. They can also be made to accept unix timestamps by +adding optional modifiers arguments <a href="lang_datefunc.html#automod">'auto'</a> or +<a href="lang_datefunc.html#jdmod">'unixepoch'</a>. +Since the timediff() function does not accept modifiers, +it can only use ISO-8601 and julian day number time-values. <a name="dttm"></a> </p><p> -The date() function returns the date as text in this format: YYYY-MM-DD. +The <b>date()</b> function returns the date as text in this format: YYYY-MM-DD. <a name="dttm"></a> </p><p> -The time() function returns the time as text in this format: HH:MM:SS. +The <b>time()</b> function returns the time as text in formatted as HH:MM:SS or as HH:MM:SS.SSS if +the <a href="lang_datefunc.html#subsec">subsec modifier</a> is used. <a name="dttm"></a> </p><p> -The datetime() function returns the date and time as text in this formats: YYYY-MM-DD HH:MM:SS. +The <b>datetime()</b> function returns the date and time formatted as YYYY-MM-DD HH:MM:SS or +as YYYY-MM-DD HH:MM:SS.SSS if the <a href="lang_datefunc.html#subsec">subsec modifier</a> is used. <a name="jlndy"></a> </p><p> -The julianday() function returns the +The <b>julianday()</b> 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>). @@ -217,7 +232,7 @@ fractional number of days since noon in Greenwich on November 24, 4714 B.C. <a name="uepch"></a> </p><p> -The unixepoch() function returns a unix timestamp - the number of seconds +The <b>unixepoch()</b> function returns a unix timestamp - the number of seconds since 1970-01-01 00:00:00 UTC. The unixepoch() function normally returns an integer number of seconds, but with the optional <a href="lang_datefunc.html#subsec">subsec modifier</a> it will return a floating point number which is the fractional number of seconds. @@ -225,22 +240,27 @@ will return a floating point number which is the fractional number of seconds. <a name="strftm"></a> </p><p> -The strftime() routine returns the date formatted according to +The <b>strftime()</b> function 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: +The following is a complete list of valid strftime() substitutions +as of version 3.46.0 (2024-05-23). Earlier versions of SQLite +might not support all substitutions. If an undefined or unsupported +substitution is seen, the result is NULL. </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-31 -</td></tr><tr><td> %e </td><td></td><td> day of month without leading zero: 0-31 +<tr><td> %d </td><td></td><td> day of month: 01-31 +</td></tr><tr><td> %e </td><td></td><td> day of month without leading zero: 1-31 </td></tr><tr><td> %f </td><td></td><td> fractional seconds: SS.SSS </td></tr><tr><td> %F </td><td></td><td> ISO 8601 date: YYYY-MM-DD +</td></tr><tr><td> %G </td><td></td><td> ISO 8601 year corresponding to %V +</td></tr><tr><td> %g </td><td></td><td> 2-digit ISO 8601 year corresponding to %V </td></tr><tr><td> %H </td><td></td><td> hour: 00-24 </td></tr><tr><td> %I </td><td></td><td> hour for 12-hour clock: 01-12 </td></tr><tr><td> %j </td><td></td><td> day of year: 001-366 @@ -255,9 +275,11 @@ The following is a complete list of valid strftime() substitutions: </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> %T </td><td></td><td> ISO 8601 time: HH:MM:SS +</td></tr><tr><td> %U </td><td></td><td> week of year (00-53) - week 01 starts on the first Sunday </td></tr><tr><td> %u </td><td></td><td> day of week 1-7 with Monday==1 +</td></tr><tr><td> %V </td><td></td><td> ISO 8601 week of year </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> %W </td><td></td><td> week of year (00-53) - week 01 starts on the first Monday </td></tr><tr><td> %Y </td><td></td><td> year: 0000-9999 </td></tr><tr><td> %% </td><td></td><td> % </td></tr></table> @@ -270,12 +292,12 @@ in terms of strftime(): <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> +<tr><td><b>Function</b></td><td width="30"></td><td><b>Equivalent strftime()</b> </td></tr><tr><td> date(...) </td><td></td><td> strftime('%F', ...) </td></tr><tr><td> time(...) </td><td></td><td> strftime('%T', ...) </td></tr><tr><td> datetime(...) </td><td></td><td> strftime('%F %T', ...) -</td></tr><tr><td> julianday(...) </td><td></td><td> <nobr>strftime('%J', ...) -- (numeric return)</nobr> -</td></tr><tr><td> unixepoch(...) </td><td></td><td> <nobr>strftime('%s', ...) -- (numeric return)</nobr> +</td></tr><tr><td> julianday(...) </td><td></td><td> <nobr>CAST(strftime('%J', ...) as REAL)</nobr> +</td></tr><tr><td> unixepoch(...) </td><td></td><td> <nobr>CAST(strftime('%s', ...) as INT)</nobr> </td></tr></table> </blockquote> @@ -296,10 +318,8 @@ of the '%J' or '%s' format specifiers with the strftime() function. <a name="tmdif"></a> -<h2 id="timediff_"><span>1.1. </span>Timediff()</h2> - <p> -The timediff(A,B) routine returns a string that describes the amount +The <b>timediff(A,B)</b> function returns a string that describes the amount of time that must be added to B in order to reach time A. The format of the timediff() result is designed to be human-readable. The format is: </p><blockquote> @@ -307,7 +327,7 @@ the timediff() result is designed to be human-readable. The format is: </blockquote> <p> This time difference string is also an allowed modifier for the other -date/time functions. The following invariant holds for time values A +date/time functions. The following invariant holds for time-values A and B: </p><blockquote> datetime(A) = datetime(B, timediff(A,B)) @@ -342,9 +362,11 @@ between two julianday() or unixepoch() calls. </p> +<a name="tmval"></a> + <h1 id="time_values"><span>2. </span>Time Values</h1> -<p>A time value can be in any of the following formats shown below. +<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. @@ -378,7 +400,7 @@ 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. +either the <a href="lang_datefunc.html#automod">'auto'</a> or <a href="lang_datefunc.html#jdmod">'unixepoch'</a> modifier. </p> <p> @@ -386,7 +408,7 @@ Formats 2 through 10 may be optionally followed by a timezone indicator of the f "<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: +For example, all of the following time-values are equivalent: </p> <blockquote> @@ -417,9 +439,9 @@ value of 'now' is assumed. </p><h1 id="modifiers"><span>3. </span>Modifiers</h1> <p>For all date/time functions other than timediff(), -the time value argument can be followed by zero or more modifiers that +the time-value argument 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. +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> @@ -437,17 +459,19 @@ The available modifiers are as follows.</p> </li><li value="11"> ±YYYY-MM-DD HH:MM </li><li value="12"> ±YYYY-MM-DD HH:MM:SS </li><li value="13"> ±YYYY-MM-DD HH:MM:SS.SSS -</li><li value="14"> start of month -</li><li value="15"> start of year -</li><li value="16"> start of day -</li><li value="17"> weekday N -</li><li value="18"> unixepoch -</li><li value="19"> julianday -</li><li value="20"> auto -</li><li value="21"> localtime -</li><li value="22"> utc -</li><li value="23"> subsec -</li><li value="24"> subsecond +</li><li value="14"> ceiling +</li><li value="15"> floor +</li><li value="16"> start of month +</li><li value="17"> start of year +</li><li value="18"> start of day +</li><li value="19"> weekday N +</li><li value="20"> unixepoch +</li><li value="21"> julianday +</li><li value="22"> auto +</li><li value="23"> localtime +</li><li value="24"> utc +</li><li value="25"> subsec +</li><li value="26"> subsecond </li></ol> <p>The first thirteen modifiers (1 through 13) @@ -455,53 +479,58 @@ 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 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> <a name="tmshf"></a> -<p>The time shift modifiers (7 through 13) move the time value by the +</p><p>The <b>time shift modifiers</b> (7 through 13) move the time-value by the number of years, months, days, hours, minutes, and/or seconds specified. An initial "+" or "-" is required for formats 10 through 13 but is optional for 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 day -by DD, and so forth. The normalization and rounding due to differing month -lengths and leap years is applied after each step. The +by DD, and so forth. The timediff(A,B) function returns a time shift in format 13 that shifts -the time value B into A.</p> - -<p>The "start of" modifiers (14 through 16) shift the date backwards +the time-value B into A.</p> + +<a name="dtambg"></a> + +<p>Because the length of a month or year changes from one month or year +to the next, ambiguities can arise when shifting a date by months and/or years. +For example, what is the date one year after 2024-02-29? Is it 2025-02-28 +or 2025-03-01? Or what is the date that is two months after 2023-12-31? +Is it 2024-02-29 or 2024-03-02? There is no consensus on how to resolve +this ambiguity, so the "<b>ceiling</b>" and "<b>floor</b>" modifiers +(14 and 15) are available to +let the programmer decide. If the next modifier after a time shift is +"ceiling", then any ambiguity in the date is resolved by choosing the +later date. The "floor" modifier resolves ambiguities +by resolving to the last day of the previous month. The default +behavior is "ceiling". + +</p><p>The "<b>start of</b>" modifiers (16 through 18) 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, +<p>The "<b>weekday</b>" 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 (18) only works if it immediately follows -a time value in the DDDDDDDDDD format. +<a name="jdmod"></a> + +<p>The "<b>unixepoch</b>" modifier (20) 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 +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 +</p><p>The "<b>julianday</b>" 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 @@ -512,7 +541,7 @@ 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. +</p><p>The "<b>auto</b>" 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 @@ -523,35 +552,36 @@ 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 +for ISO 8601 text time-values. +The "auto" modifier is designed to work with time-values even in +cases where it is not known which time-value format +is stored in the database file, or in cases where the same column +stores time-values in different formats on different rows. +The 'auto' modifier will automatically select the +appropriate format. However, there is some 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 +guaranteed to contain no dates within that range, 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 (21) assumes the time value to its left is in +</p><p>The "<b>localtime</b>" modifier 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. +The "<b>utc</b>" 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> <a name="subsec"></a> <p> -The "subsecond" modifier (which may be abbreviated as just -"subsec") increases the resolution of the output for +The "<b>subsecond</b>" modifier (which may be abbreviated as just +"<b>subsec</b>") increases the resolution of the output for <a href="lang_datefunc.html#dttm">datetime()</a>, <a href="lang_datefunc.html#dttm">time()</a>, and <a href="lang_datefunc.html#uepch">unixepoch()</a>, and for the "%s" format string in <a href="lang_datefunc.html#strftm">strftime()</a>. The "subsecond" modifier has no effect on other date/time functions. @@ -563,12 +593,10 @@ followed by a decimal point and one or more digits to show fractional seconds. When "subsec" is used with <a href="lang_datefunc.html#uepch">unixepoch()</a>, the result is a floating point value which is the number of seconds and fractional seconds since 1970-01-01. - -</p><p> The "subsecond" and "subsec" modifiers have the special property that 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 first +When this happens, the time-value that is normally in the first argument is understood to be "now". For example, a short cut to get the current time in seconds since 1970 with millisecond precision is to say: @@ -674,5 +702,5 @@ getting results correct back to 1986, when the rules were also changed.</p> <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=91b979fe1e">2024-04-16 16:29:07</a> UTC </small></i></p> |