| 1 |
# 2022-01-27 |
| 2 |
# |
| 3 |
# The author disclaims copyright to this source code. In place of |
| 4 |
# a legal notice, here is a blessing: |
| 5 |
# |
| 6 |
# May you do good and not evil. |
| 7 |
# May you find forgiveness for yourself and forgive others. |
| 8 |
# May you share freely, never taking more than you give. |
| 9 |
# |
| 10 |
#*********************************************************************** |
| 11 |
# This file implements regression tests for SQLite library. The |
| 12 |
# focus of this file is testing date and time functions. |
| 13 |
# |
| 14 |
|
| 15 |
set testdir [file dirname $argv0] |
| 16 |
source $testdir/tester.tcl |
| 17 |
|
| 18 |
# Skip this whole file if date and time functions are omitted |
| 19 |
# at compile-time |
| 20 |
# |
| 21 |
ifcapable {!datetime} { |
| 22 |
finish_test |
| 23 |
return |
| 24 |
} |
| 25 |
|
| 26 |
proc datetest {tnum expr result} { |
| 27 |
do_test date3-$tnum [subst { |
| 28 |
execsql "SELECT coalesce($expr,'NULL')" |
| 29 |
}] [list $result] |
| 30 |
} |
| 31 |
set tcl_precision 15 |
| 32 |
|
| 33 |
# EVIDENCE-OF: R-45708-63005 unixepoch(time-value, modifier, modifier, |
| 34 |
# ...) |
| 35 |
# |
| 36 |
datetest 1.1 {unixepoch('1970-01-01')} {0} |
| 37 |
datetest 1.2 {unixepoch('1969-12-31 23:59:59')} {-1} |
| 38 |
datetest 1.3 {unixepoch('2106-02-07 06:28:15')} {4294967295} |
| 39 |
datetest 1.4 {unixepoch('2106-02-07 06:28:16')} {4294967296} |
| 40 |
datetest 1.5 {unixepoch('9999-12-31 23:59:59')} {253402300799} |
| 41 |
datetest 1.6 {unixepoch('0000-01-01 00:00:00')} {-62167219200} |
| 42 |
|
| 43 |
# EVIDENCE-OF: R-30877-63179 The unixepoch() function returns a unix |
| 44 |
# timestamp - the number of seconds since 1970-01-01 00:00:00 UTC. |
| 45 |
# |
| 46 |
for {set i 1} {$i<=100} {incr i} { |
| 47 |
set x [expr {int(rand()*0xfffffffff)-0xffffffff}] |
| 48 |
datetest 1.7.$i "unixepoch($x,'unixepoch')==$x" {1} |
| 49 |
} |
| 50 |
|
| 51 |
# EVIDENCE-OF: R-62992-54137 The unixepoch() always returns an integer, |
| 52 |
# even if the input time-value has millisecond precision. |
| 53 |
# |
| 54 |
datetest 1.8 {unixepoch('2022-01-27 12:59:28.052')} {1643288368} |
| 55 |
|
| 56 |
# EVIDENCE-OF: R-05412-24332 If the time-value is numeric (the |
| 57 |
# DDDDDDDDDD format) then the 'auto' modifier causes the time-value to |
| 58 |
# interpreted as either a julian day number or a unix timestamp, |
| 59 |
# depending on its magnitude. |
| 60 |
# |
| 61 |
# EVIDENCE-OF: R-56763-40111 If the value is between 0.0 and |
| 62 |
# 5373484.499999, then it is interpreted as a julian day number |
| 63 |
# (corresponding to dates between -4713-11-24 12:00:00 and 9999-12-31 |
| 64 |
# 23:59:59, inclusive). |
| 65 |
# |
| 66 |
# EVIDENCE-OF: R-07289-49223 For numeric values outside of the range of |
| 67 |
# valid julian day numbers, but within the range of -210866760000 to |
| 68 |
# 253402300799, the 'auto' modifier causes the value to be interpreted |
| 69 |
# as a unix timestamp. |
| 70 |
# |
| 71 |
# EVIDENCE-OF: R-20795-34947 Other numeric values are out of range and |
| 72 |
# cause a NULL return. |
| 73 |
# |
| 74 |
foreach {tn jd date} { |
| 75 |
2.1 0.0 {-4713-11-24 12:00:00} |
| 76 |
2.2 5373484.4999999 {9999-12-31 23:59:59} |
| 77 |
2.3 2440587.5 {1970-01-01 00:00:00} |
| 78 |
2.4 2440587.49998843 {1969-12-31 23:59:59} |
| 79 |
2.5 2440615.7475463 {1970-01-29 05:56:28} |
| 80 |
|
| 81 |
2.10 -1 {1969-12-31 23:59:59} |
| 82 |
2.11 5373485 {1970-03-04 04:38:05} |
| 83 |
2.12 -210866760000 {-4713-11-24 12:00:00} |
| 84 |
2.13 253402300799 {9999-12-31 23:59:59} |
| 85 |
|
| 86 |
2.20 -210866760001 {NULL} |
| 87 |
2.21 253402300800 {NULL} |
| 88 |
} { |
| 89 |
datetest $tn "datetime($jd,'auto')" $date |
| 90 |
} |
| 91 |
|
| 92 |
# EVIDENCE-OF: R-38886-35357 The 'auto' modifier is a no-op for text |
| 93 |
# time-values. |
| 94 |
# |
| 95 |
datetest 2.30 {date('2022-01-29','auto')==date('2022-01-29')} {1} |
| 96 |
|
| 97 |
# EVIDENCE-OF: R-53132-26856 The 'auto' modifier can be used to work |
| 98 |
# with date/time values even in cases where it is not known if the |
| 99 |
# julian day number or unix timestamp formats are in use. |
| 100 |
# |
| 101 |
do_execsql_test date3-2.40 { |
| 102 |
WITH tx(timeval,datetime) AS ( |
| 103 |
VALUES('2022-01-27 13:15:44','2022-01-27 13:15:44'), |
| 104 |
(2459607.05260275,'2022-01-27 13:15:44'), |
| 105 |
(1643289344,'2022-01-27 13:15:44') |
| 106 |
) |
| 107 |
SELECT datetime(timeval,'auto') == datetime FROM tx; |
| 108 |
} {1 1 1} |
| 109 |
|
| 110 |
# EVIDENCE-OF: R-49255-55373 The "unixepoch" modifier (11) only works if |
| 111 |
# it immediately follows a time value in the DDDDDDDDDD format. |
| 112 |
# |
| 113 |
# EVIDENCE-OF: R-23075-39245 This modifier causes the DDDDDDDDDD to be |
| 114 |
# interpreted not as a Julian day number as it normally would be, but as |
| 115 |
# Unix Time - the number of seconds since 1970. |
| 116 |
# |
| 117 |
datetest 3.1 {datetime(2459607.05,'+1 hour','unixepoch')} {NULL} |
| 118 |
datetest 3.2 {datetime(2459607.05,'unixepoch','+1 hour')} {1970-01-29 12:13:27} |
| 119 |
|
| 120 |
# EVIDENCE-OF: R-21150-52363 The "julianday" modifier must immediately |
| 121 |
# follow the initial time-value which must be of the form DDDDDDDDD. |
| 122 |
# |
| 123 |
# EVIDENCE-OF: R-31176-64601 Any other use of the 'julianday' modifier |
| 124 |
# is an error and causes the function to return NULL. |
| 125 |
# |
| 126 |
# EVIDENCE-OF: R-32483-36353 The 'julianday' modifier forces the |
| 127 |
# time-value number to be interpreted as a julian-day number. |
| 128 |
# |
| 129 |
# EVIDENCE-OF: R-25859-20124 The only difference is that adding |
| 130 |
# 'julianday' forces the DDDDDDDDD time-value format, and causes a NULL |
| 131 |
# to be returned if any other time-value format is used. |
| 132 |
# |
| 133 |
datetest 4.1 {datetime(2459607,'julianday')} {2022-01-27 12:00:00} |
| 134 |
datetest 4.2 {datetime(2459607,'+1 hour','julianday')} {NULL} |
| 135 |
datetest 4.3 {datetime('2022-01-27','julianday')} {NULL} |
| 136 |
|
| 137 |
|
| 138 |
|
| 139 |
# EVIDENCE-OF: R-33431-18865 Unix timestamps for the first 63 days of |
| 140 |
# 1970 will be interpreted as julian day numbers. |
| 141 |
# |
| 142 |
do_execsql_test date3-5.0 { |
| 143 |
WITH inc(x) AS (VALUES(-10) UNION ALL SELECT x+1 FROM inc WHERE x<100) |
| 144 |
SELECT count(*) FROM inc |
| 145 |
WHERE datetime('1970-01-01',format('%+d days',x)) |
| 146 |
<> datetime(unixepoch('1970-01-01',format('%+d days',x)),'auto'); |
| 147 |
} {63} |
| 148 |
|
| 149 |
finish_test |