Debian Codegraph @

www/lang_datefunc.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>Date And Time Functions</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 Date And Time Functions
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#overview">1. Overview</a></div>
126 <div class="fancy-toc1"><a href="#time_values">2. Time Values</a></div>
127 <div class="fancy-toc1"><a href="#modifiers">3. Modifiers</a></div>
128 <div class="fancy-toc1"><a href="#examples">4. Examples</a></div>
129 <div class="fancy-toc1"><a href="#caveats_and_bugs">5. Caveats And Bugs</a></div>
130 </div>
131 </details>
132 </div>
133
134
135
136
137 <h1 id="overview"><span>1. </span>Overview</h1>
138
139 <p>
140 SQLite supports seven <a href="lang_corefunc.html">scalar</a> date and time functions as follows:
141 </p>
142
143 <p>
144 </p><ol>
145 <li> <b>date(</b><i>time-value, modifier, modifier, ...</i><b>)</b> </li>
146 <li> <b>time(</b><i>time-value, modifier, modifier, ...</i><b>)</b> </li>
147 <li> <b>datetime(</b><i>time-value, modifier, modifier, ...</i><b>)</b> </li>
148 <li> <b>julianday(</b><i>time-value, modifier, modifier, ...</i><b>)</b> </li>
149 <li> <b>unixepoch(</b><i>time-value, modifier, modifier, ...</i><b>)</b> </li>
150 <li> <b>strftime(</b><i>format, time-value, modifier, modifier, ...</i><b>)</b> </li>
151 <li> <b>timediff(</b><i>time-value, time-value</i><b>)</b> </li>
152 </ol>
153
154 <p>
155 The first six date and time functions take an optional <a href="lang_datefunc.html#tmval">time-value</a> as an argument, followed
156 by zero or more <a href="lang_datefunc.html#dtmods">modifiers</a>.
157 The strftime() function also takes a format string as its first argument.
158 The timediff() function takes exactly two arguments which are both <a href="lang_datefunc.html#tmval">time-values</a>.
159 </p>
160
161 <p>
162 SQLite does not have a dedicated date/time datatype. Instead,
163 date and time values can be stored as any of the following:
164
165 </p><p></p><blockquote>
166 <table border="0" cellspacing="0" cellpadding="0">
167 <tr>
168 <td><a href="http://en.wikipedia.org/wiki/ISO_8601">ISO-8601</a>
169 </td><td>A text string that is one of the ISO 8601 date/time values
170 shown in <a href="#tmval">items 1 through 10 below</a>.
171 Example: <tt>'2025-05-29 14:16:00'</tt>
172 </td></tr><tr>
173 <td><a href="http://en.wikipedia.org/wiki/Julian_day">Julian&nbsp;day&nbsp;number</a>
174 </td><td>The number of days including fractional days since -4713-11-24 12:00:00
175 Example: <tt>2460825.09444444</tt>
176 </td></tr><tr>
177 <td><a href="https://en.wikipedia.org/wiki/Unix_time">Unix timestamp</a>
178 </td><td>The number of seconds including fractional seconds since 1970-01-01 00:00:00
179 Example: <tt>1748528160</tt>
180 </td></tr></table>
181 </blockquote>
182
183 <p>
184 These three formats are collectively known as <a href="lang_datefunc.html#tmval">time-values</a>.
185 All of the date time functions accept time-values as either ISO-8601 text or
186 as Julian day numbers. They can also be made to accept unix timestamps by
187 adding optional modifier arguments <a href="lang_datefunc.html#automod">'auto'</a> or
188 <a href="lang_datefunc.html#jdmod">'unixepoch'</a>.
189 Since the timediff() function does not accept modifiers,
190 it can only use ISO-8601 and julian day number time-values.
191
192 <a name="dttm"></a>
193
194 </p><p>
195 The <b>date()</b> function returns the date as text in this format: YYYY-MM-DD.
196
197 <a name="dttm"></a>
198
199 </p><p>
200 The <b>time()</b> function returns the time as text in formatted as HH:MM:SS or as HH:MM:SS.SSS if
201 the <a href="lang_datefunc.html#subsec">subsec modifier</a> is used.
202
203 <a name="dttm"></a>
204
205 </p><p>
206 The <b>datetime()</b> function returns the date and time formatted as YYYY-MM-DD HH:MM:SS or
207 as YYYY-MM-DD HH:MM:SS.SSS if the <a href="lang_datefunc.html#subsec">subsec modifier</a> is used.
208
209 <a name="jlndy"></a>
210
211 </p><p>
212 The <b>julianday()</b> function returns the
213 <a href="http://en.wikipedia.org/wiki/Julian_day">Julian day</a> - the
214 fractional number of days since noon in Greenwich on November 24, 4714 B.C.
215 (<a href="http://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar">Proleptic Gregorian calendar</a>).
216
217 <a name="uepch"></a>
218
219 </p><p>
220 The <b>unixepoch()</b> function returns a unix timestamp - the number of seconds
221 since 1970-01-01 00:00:00 UTC. The unixepoch() function normally returns
222 an integer number of seconds, but with the optional <a href="lang_datefunc.html#subsec">subsec modifier</a> it
223 will return a floating point number which is the fractional number of seconds.
224
225 <a name="strftm"></a>
226
227 </p><p>
228 The <b>strftime()</b> function returns the date formatted according to
229 the format string specified as the first argument.
230 The format string supports the most common substitutions found in the
231 <a href="http://opengroup.org/onlinepubs/007908799/xsh/strftime.html">strftime() function</a>
232 from the standard C library plus two new substitutions, %f and %J.
233 The following is a complete list of valid strftime() substitutions
234 as of version 3.46.0 (2024-05-23). Earlier versions of SQLite
235 might not support all substitutions. If an undefined or unsupported
236 substitution is seen, the result is NULL.
237 </p>
238
239 <blockquote>
240 <table border="0" cellpadding="0" cellspacing="0">
241 <tr><td></td><td width="10"></td><td></td></tr>
242
243 <tr><td> %d </td><td></td><td> day of month: 01-31
244 </td></tr><tr><td> %e </td><td></td><td> day of month without leading zero: 1-31
245 </td></tr><tr><td> %f </td><td></td><td> fractional seconds: SS.SSS
246 </td></tr><tr><td> %F </td><td></td><td> ISO 8601 date: YYYY-MM-DD
247 </td></tr><tr><td> %G </td><td></td><td> ISO 8601 year corresponding to %V
248 </td></tr><tr><td> %g </td><td></td><td> 2-digit ISO 8601 year corresponding to %V
249 </td></tr><tr><td> %H </td><td></td><td> hour: 00-24
250 </td></tr><tr><td> %I </td><td></td><td> hour for 12-hour clock: 01-12
251 </td></tr><tr><td> %j </td><td></td><td> day of year: 001-366
252 </td></tr><tr><td> %J </td><td></td><td> Julian day number (fractional)
253 </td></tr><tr><td> %k </td><td></td><td> hour without leading zero: 0-24
254 </td></tr><tr><td> %l </td><td></td><td> %I without leading zero: 1-12
255 </td></tr><tr><td> %m </td><td></td><td> month: 01-12
256 </td></tr><tr><td> %M </td><td></td><td> minute: 00-59
257 </td></tr><tr><td> %p </td><td></td><td> "AM" or "PM" depending on the hour
258 </td></tr><tr><td> %P </td><td></td><td> "am" or "pm" depending on the hour
259 </td></tr><tr><td> %R </td><td></td><td> ISO 8601 time: HH:MM
260 </td></tr><tr><td> %s </td><td></td><td> seconds since 1970-01-01
261 </td></tr><tr><td> %S </td><td></td><td> seconds: 00-59
262 </td></tr><tr><td> %T </td><td></td><td> ISO 8601 time: HH:MM:SS
263 </td></tr><tr><td> %U </td><td></td><td> week of year (00-53) - week 01 starts on the first Sunday
264 </td></tr><tr><td> %u </td><td></td><td> day of week 1-7 with Monday==1
265 </td></tr><tr><td> %V </td><td></td><td> ISO 8601 week of year
266 </td></tr><tr><td> %w </td><td></td><td> day of week 0-6 with Sunday==0
267 </td></tr><tr><td> %W </td><td></td><td> week of year (00-53) - week 01 starts on the first Monday
268 </td></tr><tr><td> %Y </td><td></td><td> year: 0000-9999
269 </td></tr><tr><td> %% </td><td></td><td> %
270 </td></tr></table>
271 </blockquote>
272
273 <p>
274 Other date and time functions can be expressed
275 in terms of strftime():
276 </p>
277
278 <blockquote>
279 <table border="0" cellpadding="0" cellspacing="0">
280 <tr><td><b>Function</b></td><td width="30"></td><td><b>Equivalent strftime()</b>
281 </td></tr><tr><td> date(...) </td><td></td><td> strftime('%F', ...)
282 </td></tr><tr><td> time(...) </td><td></td><td> strftime('%T', ...)
283 </td></tr><tr><td> datetime(...) </td><td></td><td> strftime('%F %T', ...)
284 </td></tr><tr><td> julianday(...) </td><td></td><td> <nobr>CAST(strftime('%J', ...) as REAL)</nobr>
285 </td></tr><tr><td> unixepoch(...) </td><td></td><td> <nobr>CAST(strftime('%s', ...) as INT)</nobr>
286 </td></tr></table>
287 </blockquote>
288
289 <p>
290 The date(), time(), and datetime() functions all return text, and so their
291 strftime() equivalents are exact. However, the julianday()
292 and unixepoch() functions return numeric values. Their strftime() equivalents
293 return a string that is the text representation of the corresponding number.
294 </p>
295
296 <p>
297 The main reasons for providing functions other than strftime() are
298 for convenience and for efficiency. The julianday() and unixepoch()
299 functions return real and integer values respectively, and do not
300 incur the format conversion costs or inexactitude resulting from use
301 of the '%J' or '%s' format specifiers with the strftime() function.
302 </p>
303
304 <a name="tmdif"></a>
305
306 <p>
307 The <b>timediff(A,B)</b> function returns a string that describes the amount
308 of time that must be added to B in order to reach time A. The format of
309 the timediff() result is designed to be human-readable. The format is:
310 </p><blockquote>
311 (+|-)YYYY-MM-DD HH:MM:SS.SSS
312 </blockquote>
313 <p>
314 This time difference string is also an allowed modifier for the other
315 date/time functions. The following invariant holds for time-values A
316 and B:
317 </p><blockquote>
318 datetime(A) = datetime(B, timediff(A,B))
319 </blockquote>
320
321 <p>
322 The length of months and years vary. February is shorter than March.
323 Leap years are longer than non-leap years. The output from timediff()
324 takes this all into account. The timediff() function is intended to provide
325 a human-friendly description of the time span. If you want to know the
326 number of days or seconds between two dates, A and B, then you can always do
327 one of these:
328 </p><blockquote>
329 SELECT julianday(B) - julianday(A);<br>
330 SELECT unixepoch(B) - unixepoch(A);
331 </blockquote>
332
333 <p>
334 The timediff(A,B) might return the same result even for values A and B
335 that span a different number of days - depending on the starting date.
336 For example, both of the following two timediff() calls return the
337 same result ("-0000-01-00 00:00:00.000") even though the first timespan
338 is 28 days and the second is 31 days:
339 </p><blockquote>
340 SELECT timediff('2023-02-15','2023-03-15');<br>
341 SELECT timediff('2023-03-15','2023-04-15');
342 </blockquote>
343 <p>
344 Summary: If you want a human-friendly time span, use timediff(). If you
345 want a precise time difference (in days or seconds) use the difference
346 between two julianday() or unixepoch() calls.
347 </p>
348
349
350 <a name="tmval"></a>
351
352 <h1 id="time_values"><span>2. </span>Time Values</h1>
353
354 <p>A time-value can be in any of the following formats shown below.
355 The value is usually a string, though it can be an integer or floating
356 point number in the case of format 12.
357
358 </p><ol>
359 <li> <i>YYYY-MM-DD</i>
360 </li><li> <i>YYYY-MM-DD HH:MM</i>
361 </li><li> <i>YYYY-MM-DD HH:MM:SS</i>
362 </li><li> <i>YYYY-MM-DD HH:MM:SS.SSS</i>
363 </li><li> <i>YYYY-MM-DD</i><b>T</b><i>HH:MM</i>
364 </li><li> <i>YYYY-MM-DD</i><b>T</b><i>HH:MM:SS</i>
365 </li><li> <i>YYYY-MM-DD</i><b>T</b><i>HH:MM:SS.SSS</i>
366 </li><li> <i>HH:MM</i>
367 </li><li> <i>HH:MM:SS</i>
368 </li><li> <i>HH:MM:SS.SSS</i>
369 </li><li> <b>now</b>
370 </li><li> <i>DDDDDDDDDD</i>
371 </li></ol>
372
373 <p>
374 In formats 5 through 7, the "T" is a literal character separating
375 the date and the time, as required by
376 <a href="http://www.w3c.org/TR/NOTE-datetime">ISO-8601</a>.
377 Formats 8 through 10 that specify only a time assume a date of
378 2000-01-01. Format 11, the string 'now', is converted into the
379 current date and time as obtained from the xCurrentTime method
380 of the <a href="c3ref/vfs.html">sqlite3_vfs</a> object in use.
381 The 'now' argument to date and time functions always returns exactly the
382 same value for multiple invocations within the same <a href="c3ref/step.html">sqlite3_step()</a> call.
383 <a href="http://en.wikipedia.org/wiki/Coordinated_Universal_Time">Universal Coordinated Time (UTC)</a> is used.
384 Format 12 is the
385 <a href="http://en.wikipedia.org/wiki/Julian_day">Julian day number</a>
386 expressed as an integer or floating point value.
387 Format 12 might also be interpreted as a unix timestamp if it is immediately followed
388 either the <a href="lang_datefunc.html#automod">'auto'</a> or <a href="lang_datefunc.html#jdmod">'unixepoch'</a> modifier.
389 </p>
390
391 <p>
392 Formats 2 through 10 may be optionally followed by a timezone indicator of the form
393 "<i>&#91;+-&#93;HH:MM</i>" or just "<i>Z</i>". The date and time functions use UTC or "zulu"
394 time internally, and so the "Z" suffix is a no-op. Any non-zero "HH:MM" suffix is
395 subtracted from the indicated date and time in order to compute zulu time.
396 For example, all of the following time-values are equivalent:
397 </p>
398
399 <blockquote>
400 2013-10-07 08:23:19.120<br>
401 2013-10-07T08:23:19.120Z<br>
402 2013-10-07 04:23:19.120-04:00<br>
403 2456572.84952685
404 </blockquote>
405
406 <p>
407 In formats 4, 7, and 10, the fractional seconds value SS.SSS can have
408 one or more digits following the decimal point. Exactly three digits are
409 shown in the examples because only the first three digits are significant
410 to the result, but the input string can have fewer or more than three digits
411 and the date/time functions will still operate correctly.
412 Similarly, format 12 is shown with 10 significant digits, but the date/time
413 functions will really accept as many or as few digits as are necessary to
414 represent the Julian day number.
415 </p>
416
417 <p>
418 <a href="http://www.w3c.org/TR/NOTE-datetime">ISO-8601</a> supports a wide variety of
419 alternative date/time formats, but SQLite only supports the ones specifically
420 enumerated above.
421
422 </p><p>
423 In all functions other than timediff(),
424 the time-value (and all modifiers) may be omitted, in which case a time
425 value of 'now' is assumed.
426
427 <a name="dtmods"></a>
428
429 </p><h1 id="modifiers"><span>3. </span>Modifiers</h1>
430
431 <p>For all date/time functions other than timediff(),
432 the time-value argument can be followed by zero or more modifiers that
433 alter date and/or time. Each modifier
434 is a transformation that is applied to the time-value to its left.
435 Modifiers are applied from left to right; order is important.
436 The available modifiers are as follows.</p>
437
438 <ol>
439 <li value="1"> NNN days
440 </li><li value="2"> NNN hours
441 </li><li value="3"> NNN minutes
442 </li><li value="4"> NNN seconds
443 </li><li value="5"> NNN months
444 </li><li value="6"> NNN years
445 </li><li value="7"> &plusmn;HH:MM
446 </li><li value="8"> &plusmn;HH:MM:SS
447 </li><li value="9"> &plusmn;HH:MM:SS.SSS
448 </li><li value="10"> &plusmn;YYYY-MM-DD
449 </li><li value="11"> &plusmn;YYYY-MM-DD HH:MM
450 </li><li value="12"> &plusmn;YYYY-MM-DD HH:MM:SS
451 </li><li value="13"> &plusmn;YYYY-MM-DD HH:MM:SS.SSS
452 </li><li value="14"> ceiling
453 </li><li value="15"> floor
454 </li><li value="16"> start of month
455 </li><li value="17"> start of year
456 </li><li value="18"> start of day
457 </li><li value="19"> weekday N
458 </li><li value="20"> unixepoch
459 </li><li value="21"> julianday
460 </li><li value="22"> auto
461 </li><li value="23"> localtime
462 </li><li value="24"> utc
463 </li><li value="25"> subsec
464 </li><li value="26"> subsecond
465 </li></ol>
466
467 <p>The first thirteen modifiers (1 through 13)
468 add the specified amount of time to the date and time
469 specified by the arguments to its left.
470 The 's' character at the end of the modifier names in 1 through 6 is optional.
471 The NNN value can be any floating point number, with an optional '+' or '-' prefix.
472
473 <a name="tmshf"></a>
474
475 </p><p>The <b>time shift modifiers</b> (7 through 13) move the time-value by the
476 number of years, months, days, hours, minutes, and/or seconds specified.
477 An initial "+" or "-" is required for formats 10 through 13 but is optional
478 for formats 7, 8, and 9. The changes are applied from left to right.
479 First the year is shifted by YYYY, then the month by MM, and then day
480 by DD, and so forth. The
481 timediff(A,B) function returns a time shift in format 13 that shifts
482 the time-value B into A.</p>
483
484 <a name="dtambg"></a>
485
486 <p>Because the length of a month or year changes from one month or year
487 to the next, ambiguities can arise when shifting a date by months and/or years.
488 For example, what is the date one year after 2024-02-29? Is it 2025-02-28
489 or 2025-03-01? Or what is the date that is two months after 2023-12-31?
490 Is it 2024-02-29 or 2024-03-02? There is no consensus on how to resolve
491 this ambiguity, so the "<b>ceiling</b>" and "<b>floor</b>" modifiers
492 (14 and 15) are available to
493 let the programmer decide. If the next modifier after a time shift is
494 "ceiling", then any ambiguity in the date is resolved by choosing the
495 later date. The "floor" modifier resolves ambiguities
496 by resolving to the last day of the previous month. The default
497 behavior is "ceiling".
498
499 </p><p>The "<b>start of</b>" modifiers (16 through 18) shift the date backwards
500 to the beginning of the subject month, year or day.</p>
501
502 <p>The "<b>weekday</b>" modifier advances the date forward, if necessary,
503 to the next date where the weekday number is N. Sunday is 0, Monday is 1,
504 and so forth.
505 If the date is already on the desired weekday, the "weekday" modifier
506 leaves the date unchanged. </p>
507
508 <a name="jdmod"></a>
509
510 <p>The "<b>unixepoch</b>" modifier (20) only works if it immediately follows
511 a time-value in the DDDDDDDDDD format.
512 This modifier causes the DDDDDDDDDD to be interpreted not
513 as a Julian day number as it normally would be, but as
514 <a href="http://en.wikipedia.org/wiki/Unix_time">Unix Time</a> - the
515 number of seconds since 1970. If the "unixepoch" modifier does not
516 follow a time-value of the form DDDDDDDDDD which expresses the number
517 of seconds since 1970 or if other modifiers
518 separate the "unixepoch" modifier from prior DDDDDDDDDD then the
519 behavior is undefined.
520
521 <a name="jdmod"></a>
522
523 </p><p>The "<b>julianday</b>" modifier must immediately follow the initial
524 time-value which must be of the form DDDDDDDDD. Any other use of
525 the 'julianday' modifier is an error and causes the function to return NULL.
526 The 'julianday' modifier forces the time-value number to be interpreted
527 as a julian-day number. As this is the default behavior, the 'julianday'
528 modifier is scarcely more than a no-op. The only difference is that
529 adding 'julianday' forces the DDDDDDDDD time-value format, and causes
530 a NULL to be returned if any other time-value format is used.
531
532 <a name="automod"></a>
533
534 </p><p>The "<b>auto</b>" modifier must immediately follow the initial time-value.
535 If the time-value is numeric (the DDDDDDDDDD format) then the 'auto'
536 modifier causes the time-value to be interpreted as either a julian day
537 number or a unix timestamp, depending on its magnitude. If the value
538 is between 0.0 and 5373484.499999, then it is interpreted as a julian
539 day number (corresponding to dates between
540 -4713-11-24 12:00:00 and 9999-12-31 23:59:59, inclusive). For numeric
541 values outside of the range of valid julian day numbers, but within
542 the range of -210866760000 to 253402300799, the 'auto' modifier causes
543 the value to be interpreted as a unix timestamp. Other numeric values
544 are out of range and cause a NULL return. The 'auto' modifier is a no-op
545 for ISO 8601 text time-values.
546 The "auto" modifier is designed to work with time-values even in
547 cases where it is not known which time-value format
548 is stored in the database file, or in cases where the same column
549 stores time-values in different formats on different rows.
550 The 'auto' modifier will automatically select the
551 appropriate format. However, there is some ambiguity. Unix
552 timestamps for the first 63 days of 1970 will be interpreted as julian
553 day numbers. The 'auto' modifier is very useful when the dataset is
554 guaranteed to contain no dates within that range, but should be
555 avoided for applications that might make use of dates in the opening
556 months of 1970.
557
558 <a name="localtime"></a>
559
560 </p><p>The "<b>localtime</b>" modifier assumes the time-value to its left is in
561 Universal Coordinated Time (UTC) and adjusts that time
562 value so that it is in localtime. If "localtime"
563 follows a time that is not UTC, then the behavior is undefined.
564 The "<b>utc</b>" modifier is the opposite of "localtime".
565 "utc" assumes that the time-value
566 to its left is in the local timezone and adjusts that time-value to be in UTC.
567 If the time to the left is not in localtime, then the result of "utc" is
568 undefined.</p>
569
570 <a name="subsec"></a>
571
572 <p>
573 The "<b>subsecond</b>" modifier (which may be abbreviated as just
574 "<b>subsec</b>") increases the resolution of the output for
575 <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"
576 format string in <a href="lang_datefunc.html#strftm">strftime()</a>. The "subsecond"
577 modifier has no effect on other date/time functions.
578 The current implemention increases the resolution from seconds
579 to milliseconds, but this might increase to a higher resolution
580 in future releases of SQLite. When "subsec" is used with
581 <a href="lang_datefunc.html#dttm">datetime()</a> or <a href="lang_datefunc.html#dttm">time()</a>, the seconds field at the end is
582 followed by a decimal point and one or more digits to show
583 fractional seconds. When "subsec" is used with <a href="lang_datefunc.html#uepch">unixepoch()</a>,
584 the result is a floating point value which is the number of
585 seconds and fractional seconds since 1970-01-01.
586 The "subsecond" and "subsec" modifiers have the special property
587 that they can occur as the first argument to date/time functions
588 (or as the first argument after the format string for strftime()).
589 When this happens, the time-value that is normally in the first
590 argument is understood to be "now". For example, a short cut to
591 get the current time in seconds since 1970 with millisecond
592 precision is to say:
593
594 </p><blockquote>
595 SELECT unixepoch('subsec');
596 </blockquote>
597
598 <h1 id="examples"><span>4. </span>Examples</h1>
599
600 <p>Compute the current date.</p><p>
601
602 </p><blockquote>SELECT date();</blockquote>
603
604 <p>Compute the last day of the current month.</p>
605
606 <blockquote>SELECT date('now','start of month','+1 month','-1 day');
607 </blockquote>
608
609 <p>Compute the date and time given a unix timestamp 1092941466.</p>
610
611 <blockquote>
612 SELECT datetime(1092941466, 'unixepoch');<br>
613 SELECT datetime(1092941466, 'auto'); -- Does not work for early 1970!
614 </blockquote>
615
616 <p>Compute the date and time given a unix timestamp 1092941466, and
617 compensate for your local timezone.</p>
618
619 <blockquote>
620 SELECT datetime(1092941466, 'unixepoch', 'localtime');
621 </blockquote>
622
623 <p>Compute the current unix timestamp.</p>
624
625 <blockquote>
626 SELECT unixepoch();<br>
627 SELECT strftime('%s');
628 </blockquote>
629
630 <p>Compute the number of days since the signing of the US Declaration
631 of Independence.</p>
632
633 <blockquote>
634 SELECT julianday('now') - julianday('1776-07-04');
635 </blockquote>
636
637 <p>Compute the number of seconds since a particular moment in 2004:</p>
638
639 <blockquote>
640 SELECT unixepoch() - unixepoch('2004-01-01 02:34:56');
641 </blockquote>
642
643 <p>
644 Compute the date of the first Tuesday in October
645 for the current year.
646 </p>
647
648 <blockquote>
649 SELECT date('now','start of year','+9 months','weekday 2');
650 </blockquote>
651
652 <p>Compute the time since the unix epoch in seconds with
653 millisecond precision:</p>
654
655 <blockquote>
656 SELECT (julianday('now') - 2440587.5)*86400.0;<br>
657 SELECT unixepoch('now','subsec');
658 </blockquote>
659
660 <p>Compute how old Abraham Lincoln would be if he were still alive today:</p>
661
662 <blockquote>
663 SELECT timediff('now','1809-02-12');
664 </blockquote>
665
666 <h1 id="caveats_and_bugs"><span>5. </span>Caveats And Bugs</h1>
667
668 <p>The computation of local time depends heavily on the whim
669 of politicians and is thus difficult to get correct for
670 all locales. In this implementation, the standard C library
671 function localtime_r() is used to assist in the calculation of
672 local time. The
673 localtime_r() C function normally only works for years
674 between 1970 and 2037. For dates outside this range, SQLite
675 attempts to map the year into an equivalent year within
676 this range, do the calculation, then map the year back.</p>
677
678 <p>These functions only work for dates between 0000-01-01 00:00:00
679 and 9999-12-31 23:59:59 (julian day numbers 1721059.5 through 5373484.5).
680 For dates outside that range, the results of these
681 functions are undefined.</p>
682
683 <p>Non-Vista Windows platforms only support one set of DST rules.
684 Vista only supports two. Therefore, on these platforms,
685 historical DST calculations will be incorrect.
686 For example, in the US, in 2007 the DST rules changed.
687 Non-Vista Windows platforms apply the new 2007 DST rules
688 to all previous years as well. Vista does somewhat better
689 getting results correct back to 1986, when the rules were also changed.</p>
690
691 <p>All internal computations assume the
692 <a href="http://en.wikipedia.org/wiki/Gregorian_calendar">Gregorian calendar</a>
693 system. They also assume that every
694 day is exactly 86400 seconds in duration; no leap seconds are incorporated.</p>
695 <p align="center"><small><i>This page was last updated on 2026-01-14 14:54:01Z </small></i></p>
696