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
|
# 2022-01-27
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The
# focus of this file is testing date and time functions.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
# Skip this whole file if date and time functions are omitted
# at compile-time
#
ifcapable {!datetime} {
finish_test
return
}
proc datetest {tnum expr result} {
do_test date3-$tnum [subst {
execsql "SELECT coalesce($expr,'NULL')"
}] [list $result]
}
set tcl_precision 15
# EVIDENCE-OF: R-45708-63005 unixepoch(time-value, modifier, modifier,
# ...)
#
datetest 1.1 {unixepoch('1970-01-01')} {0}
datetest 1.2 {unixepoch('1969-12-31 23:59:59')} {-1}
datetest 1.3 {unixepoch('2106-02-07 06:28:15')} {4294967295}
datetest 1.4 {unixepoch('2106-02-07 06:28:16')} {4294967296}
datetest 1.5 {unixepoch('9999-12-31 23:59:59')} {253402300799}
datetest 1.6 {unixepoch('0000-01-01 00:00:00')} {-62167219200}
# EVIDENCE-OF: R-30877-63179 The unixepoch() function returns a unix
# timestamp - the number of seconds since 1970-01-01 00:00:00 UTC.
#
for {set i 1} {$i<=100} {incr i} {
set x [expr {int(rand()*0xfffffffff)-0xffffffff}]
datetest 1.7.$i "unixepoch($x,'unixepoch')==$x" {1}
}
# EVIDENCE-OF: R-62992-54137 The unixepoch() always returns an integer,
# even if the input time-value has millisecond precision.
#
datetest 1.8 {unixepoch('2022-01-27 12:59:28.052')} {1643288368}
# EVIDENCE-OF: R-05412-24332 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.
#
# EVIDENCE-OF: R-56763-40111 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).
#
# EVIDENCE-OF: R-07289-49223 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.
#
# EVIDENCE-OF: R-20795-34947 Other numeric values are out of range and
# cause a NULL return.
#
foreach {tn jd date} {
2.1 0.0 {-4713-11-24 12:00:00}
2.2 5373484.4999999 {9999-12-31 23:59:59}
2.3 2440587.5 {1970-01-01 00:00:00}
2.4 2440587.49998843 {1969-12-31 23:59:59}
2.5 2440615.7475463 {1970-01-29 05:56:28}
2.10 -1 {1969-12-31 23:59:59}
2.11 5373485 {1970-03-04 04:38:05}
2.12 -210866760000 {-4713-11-24 12:00:00}
2.13 253402300799 {9999-12-31 23:59:59}
2.20 -210866760001 {NULL}
2.21 253402300800 {NULL}
} {
datetest $tn "datetime($jd,'auto')" $date
}
# EVIDENCE-OF: R-38886-35357 The 'auto' modifier is a no-op for text
# time-values.
#
datetest 2.30 {date('2022-01-29','auto')==date('2022-01-29')} {1}
# EVIDENCE-OF: R-53132-26856 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.
#
do_execsql_test date3-2.40 {
WITH tx(timeval,datetime) AS (
VALUES('2022-01-27 13:15:44','2022-01-27 13:15:44'),
(2459607.05260275,'2022-01-27 13:15:44'),
(1643289344,'2022-01-27 13:15:44')
)
SELECT datetime(timeval,'auto') == datetime FROM tx;
} {1 1 1}
# EVIDENCE-OF: R-49255-55373 The "unixepoch" modifier (11) only works if
# it immediately follows a time value in the DDDDDDDDDD format.
#
# EVIDENCE-OF: R-23075-39245 This modifier causes the DDDDDDDDDD to be
# interpreted not as a Julian day number as it normally would be, but as
# Unix Time - the number of seconds since 1970.
#
datetest 3.1 {datetime(2459607.05,'+1 hour','unixepoch')} {NULL}
datetest 3.2 {datetime(2459607.05,'unixepoch','+1 hour')} {1970-01-29 12:13:27}
# EVIDENCE-OF: R-21150-52363 The "julianday" modifier must immediately
# follow the initial time-value which must be of the form DDDDDDDDD.
#
# EVIDENCE-OF: R-31176-64601 Any other use of the 'julianday' modifier
# is an error and causes the function to return NULL.
#
# EVIDENCE-OF: R-32483-36353 The 'julianday' modifier forces the
# time-value number to be interpreted as a julian-day number.
#
# EVIDENCE-OF: R-25859-20124 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.
#
datetest 4.1 {datetime(2459607,'julianday')} {2022-01-27 12:00:00}
datetest 4.2 {datetime(2459607,'+1 hour','julianday')} {NULL}
datetest 4.3 {datetime('2022-01-27','julianday')} {NULL}
# EVIDENCE-OF: R-33431-18865 Unix timestamps for the first 63 days of
# 1970 will be interpreted as julian day numbers.
#
do_execsql_test date3-5.0 {
WITH inc(x) AS (VALUES(-10) UNION ALL SELECT x+1 FROM inc WHERE x<100)
SELECT count(*) FROM inc
WHERE datetime('1970-01-01',format('%+d days',x))
<> datetime(unixepoch('1970-01-01',format('%+d days',x)),'auto');
} {63}
finish_test
|
