Debian Codegraph @

test/timediff1.test

raw
1 # 2023-05-30
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 timediff-$tnum [subst {
28 execsql "SELECT coalesce($expr,'NULL')"
29 }] [list $result]
30 }
31 set tcl_precision 15
32
33
34 # February overflow on a leap year
35 datetest 1.1 {datetime('2000-01-31','+1 month')} {2000-03-02 00:00:00}
36 datetest 1.2 {datetime('2004-01-29','+1 month')} {2004-02-29 00:00:00}
37 datetest 1.3 {datetime('2000-03-01','-1 day')} {2000-02-29 00:00:00}
38 datetest 1.4 {datetime('2000-03-31','-1 month')} {2000-03-02 00:00:00}
39 datetest 1.5 {datetime('2000-03-30','-1 month')} {2000-03-01 00:00:00}
40 datetest 1.6 {datetime('2000-03-29','-1 month')} {2000-02-29 00:00:00}
41 datetest 1.7 {datetime('2000-03-28','-1 month')} {2000-02-28 00:00:00}
42 datetest 1.8 {datetime('2000-02-29','+1 year')} {2001-03-01 00:00:00}
43 datetest 1.9 {datetime('2000-02-29','+4 years')} {2004-02-29 00:00:00}
44
45 datetest 1.10 {datetime('1998-11-10','+0001-03-19 12:34:56')} \
46 {2000-02-29 12:34:56}
47 datetest 1.11 {datetime('2000-01-31','+0004-01-00 12:34:56')} \
48 {2004-03-02 12:34:56}
49 datetest 1.12 {datetime('2000-01-29','+0008-01-00 12:34:56')} \
50 {2008-02-29 12:34:56}
51 datetest 1.13 {datetime('2001-03-31','-0001-01-00 06:10')} \
52 {2000-03-01 17:50:00}
53
54
55 # February overflow on a non-leap year
56 datetest 2.1 {datetime('2001-01-31','+1 month')} {2001-03-03 00:00:00}
57 datetest 2.2 {datetime('2005-01-29','+1 month')} {2005-03-01 00:00:00}
58 datetest 2.3 {datetime('2001-03-01','-1 day')} {2001-02-28 00:00:00}
59 datetest 2.4 {datetime('2001-03-31','-1 month')} {2001-03-03 00:00:00}
60 datetest 2.5 {datetime('2001-03-30','-1 month')} {2001-03-02 00:00:00}
61 datetest 2.6 {datetime('2001-03-29','-1 month')} {2001-03-01 00:00:00}
62 datetest 2.7 {datetime('2001-03-28','-1 month')} {2001-02-28 00:00:00}
63
64 datetest 2.10 {datetime('1999-11-10','+0001-03-19 12:34:56')} \
65 {2001-03-01 12:34:56}
66 datetest 2.11 {datetime('2000-01-31','+0005-01-00 12:34:56')} \
67 {2005-03-03 12:34:56}
68 datetest 2.12 {datetime('2000-01-29','+0009-01-00 12:34:56')} \
69 {2009-03-01 12:34:56}
70 datetest 2.13 {datetime('2002-03-31','-0001-01-00 06:10')} \
71 {2001-03-02 17:50:00}
72
73 # timediff
74 datetest 3.1 {timediff('2000-03-02','2000-01-31')} {+0000-01-00 00:00:00.000}
75 datetest 3.2 {timediff('2000-01-31','2000-03-02')} {-0000-01-02 00:00:00.000}
76 datetest 3.3 {timediff('2000-03-02','1999-01-31')} {+0001-01-00 00:00:00.000}
77 datetest 3.4 {timediff('1999-01-31','2000-03-02')} {-0001-01-02 00:00:00.000}
78
79 unset -nocomplain p1
80 unset -nocomplain p2
81 set p1 {
82 0 {-4713-11-24 12:00:00}
83 1 {-2000-04-30 05:19:26}
84 2 {0000-01-01 12:34:56}
85 3 {1776-07-04 13:00:00}
86 4 {1969-07-20 20:17}
87 5 {2440587.5}
88 6 {2000-05-29 14:26}
89 7 {2023-05-29 18:11}
90 8 {2050-05-29 14:26}
91 9 {4796-02-29 11:23:55.46}
92 }
93 set p2 {
94 A {1066-10-14}
95 B {1900-02-28 11:00}
96 C {1900-03-01 12:00}
97 D {1904-02-29 11:25}
98 E {2000-02-29 13:00}
99 E {2000-03-01 14:00}
100 F {2001-03-31 15:15}
101 G {2002-04-01 16:59}
102 H {2003-04-30 17:00}
103 I {2004-05-01 23:59:59}
104 J {2005-06-01}
105 K {2006-06-30 01:23:45}
106 L {2007-12-31 02:00}
107 M {2008-01-01 01:59}
108 N {3152-07-04 12:00}
109 P {9999-12-31 23:59:59}
110 }
111
112 foreach {x1 d1} $p1 {
113 foreach {x2 d2} $p2 {
114 set r1 [db one {SELECT datetime($d1)}]
115 do_execsql_test timediff-4-$x1$x2 {
116 SELECT datetime($d2, timediff($d1,$d2));
117 } [list $r1]
118 set r2 [db one {SELECT datetime($d2)}]
119 do_execsql_test timediff-4-$x2$x1 {
120 SELECT datetime($d1, timediff($d2,$d1));
121 } [list $r2]
122 }
123 }
124
125 # Partial time-diffs as modifiers
126 #
127 datetest 5-1 {datetime('2000-01-01','+0001-02-03')} {2001-03-04 00:00:00}
128 datetest 5-2 {datetime('2000-01-01','+0001-02-03x')} {NULL}
129 datetest 5-3 {datetime('2000-01-01','+0001-11-03')} {2001-12-04 00:00:00}
130 datetest 5-4 {datetime('2000-01-01','+0001-12-03')} {NULL}
131 datetest 5-5 {datetime('2000-01-01','+0001-02-30')} {2001-03-31 00:00:00}
132 datetest 5-6 {datetime('2000-01-01','+0001-02-31')} {NULL}
133 datetest 5-7 {datetime('2000-01-01','+0001-02-03 0')} {NULL}
134 datetest 5-8 {datetime('2000-01-01','+0001-02-03 01')} {NULL}
135 datetest 5-9 {datetime('2000-01-01','+0001-02-03 01:')} {NULL}
136 datetest 5-10 {datetime('2000-01-01','+0001-02-03 01:0')} {NULL}
137 datetest 5-11 {datetime('2000-01-01','+0001-02-03 01:02')} {2001-03-04 01:02:00}
138 datetest 5-12 {datetime('2000-01-01','+0001-02-03 01:02:')} {NULL}
139 datetest 5-13 {datetime('2000-01-01','+0001-02-03 01:02:0')} {NULL}
140 datetest 5-14 {datetime('2000-01-01','+0001-02-03 01:02:03')} \
141 {2001-03-04 01:02:03}
142 datetest 5-15 {datetime('2000-01-01','+0001-02-03 01:02:03.')} NULL
143 datetest 5-16 {datetime('2000-01-01','+0001-02-03 01:02:03.5')} \
144 {2001-03-04 01:02:03}
145 datetest 5-17 {datetime('2000-01-01','+0001-02-03 01:02:03.50')} \
146 {2001-03-04 01:02:03}
147 datetest 5-18 {datetime('2000-01-01','+0001-02-03 01:02:03.500')} \
148 {2001-03-04 01:02:03}
149 datetest 5-19 {datetime('2000-01-01','+0001-02-03 01:02:03.500x')} {NULL}
150 datetest 5-20 {datetime('2000-01-01','+0001-02-03 01:02:03.500 x')} {NULL}
151
152 unset -nocomplain p1
153 unset -nocomplain p2
154 set p1 {
155 a {2000-01-01 00:00:00}
156 b {2000-01-31 23:59:59}
157 c {2000-02-01 00:00:00}
158 d {2000-02-29 23:59:59}
159 e {2000-03-01 00:00:00}
160 f {2000-03-31 23:59:59}
161 g {2000-04-01 00:00:00}
162 h {2000-04-30 23:59:59}
163 i {2000-05-01 00:00:00}
164 j {2000-05-31 23:59:59}
165 k {2000-06-01 00:00:00}
166 l {2000-06-30 23:59:59}
167 m {2000-07-01 00:00:00}
168 n {2000-07-31 23:59:59}
169 o {2000-08-01 00:00:00}
170 p {2000-08-31 23:59:59}
171 q {2000-09-01 00:00:00}
172 r {2000-09-30 23:59:59}
173 s {2000-10-01 00:00:00}
174 t {2000-10-31 23:59:59}
175 u {2000-11-01 00:00:00}
176 v {2000-11-30 23:59:59}
177 w {2000-12-01 00:00:00}
178 x {2000-12-31 23:59:59}
179 }
180 set p2 {
181 A {2001-01-01 00:00:00}
182 B {2001-01-31 23:59:59}
183 C {2001-02-01 00:00:00}
184 D {2001-02-28 23:59:59}
185 E {2001-03-01 00:00:00}
186 F {2001-03-31 23:59:59}
187 G {2001-04-01 00:00:00}
188 H {2001-04-30 23:59:59}
189 I {2001-05-01 00:00:00}
190 J {2001-05-31 23:59:59}
191 K {2001-06-01 00:00:00}
192 L {2001-06-30 23:59:59}
193 M {2001-07-01 00:00:00}
194 N {2001-07-31 23:59:59}
195 O {2001-08-01 00:00:00}
196 P {2001-08-31 23:59:59}
197 Q {2001-09-01 00:00:00}
198 R {2001-09-30 23:59:59}
199 S {2001-10-01 00:00:00}
200 T {2001-10-31 23:59:59}
201 U {2001-11-01 00:00:00}
202 V {2001-11-30 23:59:59}
203 W {2001-12-01 00:00:00}
204 X {2001-12-31 23:59:59}
205 }
206
207 foreach {x1 d1} $p1 {
208 foreach {x2 d2} $p2 {
209 set r1 [db one {SELECT datetime($d1)}]
210 do_execsql_test timediff-6-$x1$x2 {
211 SELECT datetime($d2, timediff($d1,$d2));
212 } [list $r1]
213 set r2 [db one {SELECT datetime($d2)}]
214 do_execsql_test timediff-6-$x2$x1 {
215 SELECT datetime($d1, timediff($d2,$d1));
216 } [list $r2]
217 }
218 }
219
220
221
222 finish_test