1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
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>
|
