AryaWu/sqlite
0
1# 2023-05-302#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this file is testing date and time functions.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17 18# Skip this whole file if date and time functions are omitted19# at compile-time20#21ifcapable {!datetime} {22 finish_test23 return24}25 26proc datetest {tnum expr result} {27 do_test timediff-$tnum [subst {28 execsql "SELECT coalesce($expr,'NULL')"29 }] [list $result]30}31set tcl_precision 1532 33 34# February overflow on a leap year35datetest 1.1 {datetime('2000-01-31','+1 month')} {2000-03-02 00:00:00}36datetest 1.2 {datetime('2004-01-29','+1 month')} {2004-02-29 00:00:00}37datetest 1.3 {datetime('2000-03-01','-1 day')} {2000-02-29 00:00:00}38datetest 1.4 {datetime('2000-03-31','-1 month')} {2000-03-02 00:00:00}39datetest 1.5 {datetime('2000-03-30','-1 month')} {2000-03-01 00:00:00}40datetest 1.6 {datetime('2000-03-29','-1 month')} {2000-02-29 00:00:00}41datetest 1.7 {datetime('2000-03-28','-1 month')} {2000-02-28 00:00:00}42datetest 1.8 {datetime('2000-02-29','+1 year')} {2001-03-01 00:00:00}43datetest 1.9 {datetime('2000-02-29','+4 years')} {2004-02-29 00:00:00}44 45datetest 1.10 {datetime('1998-11-10','+0001-03-19 12:34:56')} \46 {2000-02-29 12:34:56}47datetest 1.11 {datetime('2000-01-31','+0004-01-00 12:34:56')} \48 {2004-03-02 12:34:56}49datetest 1.12 {datetime('2000-01-29','+0008-01-00 12:34:56')} \50 {2008-02-29 12:34:56}51datetest 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 year56datetest 2.1 {datetime('2001-01-31','+1 month')} {2001-03-03 00:00:00}57datetest 2.2 {datetime('2005-01-29','+1 month')} {2005-03-01 00:00:00}58datetest 2.3 {datetime('2001-03-01','-1 day')} {2001-02-28 00:00:00}59datetest 2.4 {datetime('2001-03-31','-1 month')} {2001-03-03 00:00:00}60datetest 2.5 {datetime('2001-03-30','-1 month')} {2001-03-02 00:00:00}61datetest 2.6 {datetime('2001-03-29','-1 month')} {2001-03-01 00:00:00}62datetest 2.7 {datetime('2001-03-28','-1 month')} {2001-02-28 00:00:00}63 64datetest 2.10 {datetime('1999-11-10','+0001-03-19 12:34:56')} \65 {2001-03-01 12:34:56}66datetest 2.11 {datetime('2000-01-31','+0005-01-00 12:34:56')} \67 {2005-03-03 12:34:56}68datetest 2.12 {datetime('2000-01-29','+0009-01-00 12:34:56')} \69 {2009-03-01 12:34:56}70datetest 2.13 {datetime('2002-03-31','-0001-01-00 06:10')} \71 {2001-03-02 17:50:00}72 73# timediff74datetest 3.1 {timediff('2000-03-02','2000-01-31')} {+0000-01-00 00:00:00.000}75datetest 3.2 {timediff('2000-01-31','2000-03-02')} {-0000-01-02 00:00:00.000}76datetest 3.3 {timediff('2000-03-02','1999-01-31')} {+0001-01-00 00:00:00.000}77datetest 3.4 {timediff('1999-01-31','2000-03-02')} {-0001-01-02 00:00:00.000}78 79unset -nocomplain p180unset -nocomplain p281set 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}93set 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 112foreach {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 modifiers126#127datetest 5-1 {datetime('2000-01-01','+0001-02-03')} {2001-03-04 00:00:00}128datetest 5-2 {datetime('2000-01-01','+0001-02-03x')} {NULL}129datetest 5-3 {datetime('2000-01-01','+0001-11-03')} {2001-12-04 00:00:00}130datetest 5-4 {datetime('2000-01-01','+0001-12-03')} {NULL}131datetest 5-5 {datetime('2000-01-01','+0001-02-30')} {2001-03-31 00:00:00}132datetest 5-6 {datetime('2000-01-01','+0001-02-31')} {NULL}133datetest 5-7 {datetime('2000-01-01','+0001-02-03 0')} {NULL}134datetest 5-8 {datetime('2000-01-01','+0001-02-03 01')} {NULL}135datetest 5-9 {datetime('2000-01-01','+0001-02-03 01:')} {NULL}136datetest 5-10 {datetime('2000-01-01','+0001-02-03 01:0')} {NULL}137datetest 5-11 {datetime('2000-01-01','+0001-02-03 01:02')} {2001-03-04 01:02:00}138datetest 5-12 {datetime('2000-01-01','+0001-02-03 01:02:')} {NULL}139datetest 5-13 {datetime('2000-01-01','+0001-02-03 01:02:0')} {NULL}140datetest 5-14 {datetime('2000-01-01','+0001-02-03 01:02:03')} \141 {2001-03-04 01:02:03}142datetest 5-15 {datetime('2000-01-01','+0001-02-03 01:02:03.')} NULL143datetest 5-16 {datetime('2000-01-01','+0001-02-03 01:02:03.5')} \144 {2001-03-04 01:02:03}145datetest 5-17 {datetime('2000-01-01','+0001-02-03 01:02:03.50')} \146 {2001-03-04 01:02:03}147datetest 5-18 {datetime('2000-01-01','+0001-02-03 01:02:03.500')} \148 {2001-03-04 01:02:03}149datetest 5-19 {datetime('2000-01-01','+0001-02-03 01:02:03.500x')} {NULL}150datetest 5-20 {datetime('2000-01-01','+0001-02-03 01:02:03.500 x')} {NULL}151 152unset -nocomplain p1153unset -nocomplain p2154set 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}180set 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 207foreach {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 222finish_test223