000001 # 2022-01-27
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 # This file implements regression tests for SQLite library. The
000012 # focus of this file is testing date and time functions.
000013 #
000014
000015 set testdir [file dirname $argv0]
000016 source $testdir/tester.tcl
000017
000018 # Skip this whole file if date and time functions are omitted
000019 # at compile-time
000020 #
000021 ifcapable {!datetime} {
000022 finish_test
000023 return
000024 }
000025
000026 proc datetest {tnum expr result} {
000027 do_test date3-$tnum [subst {
000028 execsql "SELECT coalesce($expr,'NULL')"
000029 }] [list $result]
000030 }
000031 set tcl_precision 15
000032
000033 # EVIDENCE-OF: R-45708-63005 unixepoch(time-value, modifier, modifier,
000034 # ...)
000035 #
000036 datetest 1.1 {unixepoch('1970-01-01')} {0}
000037 datetest 1.2 {unixepoch('1969-12-31 23:59:59')} {-1}
000038 datetest 1.3 {unixepoch('2106-02-07 06:28:15')} {4294967295}
000039 datetest 1.4 {unixepoch('2106-02-07 06:28:16')} {4294967296}
000040 datetest 1.5 {unixepoch('9999-12-31 23:59:59')} {253402300799}
000041 datetest 1.6 {unixepoch('0000-01-01 00:00:00')} {-62167219200}
000042
000043 # EVIDENCE-OF: R-30877-63179 The unixepoch() function returns a unix
000044 # timestamp - the number of seconds since 1970-01-01 00:00:00 UTC.
000045 #
000046 for {set i 1} {$i<=100} {incr i} {
000047 set x [expr {int(rand()*0xfffffffff)-0xffffffff}]
000048 datetest 1.7.$i "unixepoch($x,'unixepoch')==$x" {1}
000049 }
000050
000051 # EVIDENCE-OF: R-62992-54137 The unixepoch() always returns an integer,
000052 # even if the input time-value has millisecond precision.
000053 #
000054 datetest 1.8 {unixepoch('2022-01-27 12:59:28.052')} {1643288368}
000055
000056 # EVIDENCE-OF: R-05412-24332 If the time-value is numeric (the
000057 # DDDDDDDDDD format) then the 'auto' modifier causes the time-value to
000058 # interpreted as either a julian day number or a unix timestamp,
000059 # depending on its magnitude.
000060 #
000061 # EVIDENCE-OF: R-56763-40111 If the value is between 0.0 and
000062 # 5373484.499999, then it is interpreted as a julian day number
000063 # (corresponding to dates between -4713-11-24 12:00:00 and 9999-12-31
000064 # 23:59:59, inclusive).
000065 #
000066 # EVIDENCE-OF: R-07289-49223 For numeric values outside of the range of
000067 # valid julian day numbers, but within the range of -210866760000 to
000068 # 253402300799, the 'auto' modifier causes the value to be interpreted
000069 # as a unix timestamp.
000070 #
000071 # EVIDENCE-OF: R-20795-34947 Other numeric values are out of range and
000072 # cause a NULL return.
000073 #
000074 foreach {tn jd date} {
000075 2.1 0.0 {-4713-11-24 12:00:00}
000076 2.2 5373484.4999999 {9999-12-31 23:59:59}
000077 2.3 2440587.5 {1970-01-01 00:00:00}
000078 2.4 2440587.49998843 {1969-12-31 23:59:59}
000079 2.5 2440615.7475463 {1970-01-29 05:56:28}
000080
000081 2.10 -1 {1969-12-31 23:59:59}
000082 2.11 5373485 {1970-03-04 04:38:05}
000083 2.12 -210866760000 {-4713-11-24 12:00:00}
000084 2.13 253402300799 {9999-12-31 23:59:59}
000085
000086 2.20 -210866760001 {NULL}
000087 2.21 253402300800 {NULL}
000088 } {
000089 datetest $tn "datetime($jd,'auto')" $date
000090 }
000091
000092 # EVIDENCE-OF: R-38886-35357 The 'auto' modifier is a no-op for text
000093 # time-values.
000094 #
000095 datetest 2.30 {date('2022-01-29','auto')==date('2022-01-29')} {1}
000096
000097 # EVIDENCE-OF: R-53132-26856 The 'auto' modifier can be used to work
000098 # with date/time values even in cases where it is not known if the
000099 # julian day number or unix timestamp formats are in use.
000100 #
000101 do_execsql_test date3-2.40 {
000102 WITH tx(timeval,datetime) AS (
000103 VALUES('2022-01-27 13:15:44','2022-01-27 13:15:44'),
000104 (2459607.05260275,'2022-01-27 13:15:44'),
000105 (1643289344,'2022-01-27 13:15:44')
000106 )
000107 SELECT datetime(timeval,'auto') == datetime FROM tx;
000108 } {1 1 1}
000109
000110 # EVIDENCE-OF: R-49255-55373 The "unixepoch" modifier (11) only works if
000111 # it immediately follows a time value in the DDDDDDDDDD format.
000112 #
000113 # EVIDENCE-OF: R-23075-39245 This modifier causes the DDDDDDDDDD to be
000114 # interpreted not as a Julian day number as it normally would be, but as
000115 # Unix Time - the number of seconds since 1970.
000116 #
000117 datetest 3.1 {datetime(2459607.05,'+1 hour','unixepoch')} {NULL}
000118 datetest 3.2 {datetime(2459607.05,'unixepoch','+1 hour')} {1970-01-29 12:13:27}
000119
000120 # EVIDENCE-OF: R-21150-52363 The "julianday" modifier must immediately
000121 # follow the initial time-value which must be of the form DDDDDDDDD.
000122 #
000123 # EVIDENCE-OF: R-31176-64601 Any other use of the 'julianday' modifier
000124 # is an error and causes the function to return NULL.
000125 #
000126 # EVIDENCE-OF: R-32483-36353 The 'julianday' modifier forces the
000127 # time-value number to be interpreted as a julian-day number.
000128 #
000129 # EVIDENCE-OF: R-25859-20124 The only difference is that adding
000130 # 'julianday' forces the DDDDDDDDD time-value format, and causes a NULL
000131 # to be returned if any other time-value format is used.
000132 #
000133 datetest 4.1 {datetime(2459607,'julianday')} {2022-01-27 12:00:00}
000134 datetest 4.2 {datetime(2459607,'+1 hour','julianday')} {NULL}
000135 datetest 4.3 {datetime('2022-01-27','julianday')} {NULL}
000136
000137
000138
000139 # EVIDENCE-OF: R-33431-18865 Unix timestamps for the first 63 days of
000140 # 1970 will be interpreted as julian day numbers.
000141 #
000142 do_execsql_test date3-5.0 {
000143 WITH inc(x) AS (VALUES(-10) UNION ALL SELECT x+1 FROM inc WHERE x<100)
000144 SELECT count(*) FROM inc
000145 WHERE datetime('1970-01-01',format('%+d days',x))
000146 <> datetime(unixepoch('1970-01-01',format('%+d days',x)),'auto');
000147 } {63}
000148
000149 finish_test