| 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 |