/ Check-in [55791928]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Create "pure" versions of the date/time functions that omit the 'now' feature and are therefore deterministic and usable in an index.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | pure-date-functions
Files: files | file ages | folders
SHA3-256: 55791928f317a3063216d35f7946555791dd1b48542e24088aba135558b63873
User & Date: drh 2017-07-19 17:12:53
Context
2017-07-19
17:12
Create "pure" versions of the date/time functions that omit the 'now' feature and are therefore deterministic and usable in an index. Closed-Leaf check-in: 55791928 user: drh tags: pure-date-functions
11:52
Add further tests to unionvtab.test. check-in: c93284cd user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/date.c.

75
76
77
78
79
80
81









82
83
84
85
86
87
88
...
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
...
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
...
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
....
1232
1233
1234
1235
1236
1237
1238





1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
  char validYMD;      /* True (1) if Y,M,D are valid */
  char validHMS;      /* True (1) if h,m,s are valid */
  char validTZ;       /* True (1) if tz is valid */
  char tzSet;         /* Timezone was set explicitly */
  char isError;       /* An overflow has occurred */
};











/*
** Convert zDate into one or more integers according to the conversion
** specifier zFormat.
**
** zFormat[] contains 4 characters for each integer converted, except for
** the last integer which is specified by three characters.  The meaning
................................................................................
  DateTime *p
){
  double r;
  if( parseYyyyMmDd(zDate,p)==0 ){
    return 0;
  }else if( parseHhMmSs(zDate, p)==0 ){
    return 0;
  }else if( sqlite3StrICmp(zDate,"now")==0){
    return setDateTimeToCurrent(context, p);
  }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8) ){
    setRawDateNumber(p, r);
    return 0;
  }
  return 1;
}
................................................................................
#ifndef SQLITE_OMIT_LOCALTIME
    case 'l': {
      /*    localtime
      **
      ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
      ** show local time.
      */
      if( sqlite3_stricmp(z, "localtime")==0 ){
        computeJD(p);
        p->iJD += localtimeOffset(p, pCtx, &rc);
        clearYMD_HMS_TZ(p);
      }
      break;
    }
#endif
................................................................................
          p->iJD = (sqlite3_int64)r;
          p->validJD = 1;
          p->rawS = 0;
          rc = 0;
        }
      }
#ifndef SQLITE_OMIT_LOCALTIME
      else if( sqlite3_stricmp(z, "utc")==0 ){
        if( p->tzSet==0 ){
          sqlite3_int64 c1;
          computeJD(p);
          c1 = localtimeOffset(p, pCtx, &rc);
          if( rc==SQLITE_OK ){
            p->iJD -= c1;
            clearYMD_HMS_TZ(p);
................................................................................
  static FuncDef aDateTimeFuncs[] = {
#ifndef SQLITE_OMIT_DATETIME_FUNCS
    DFUNCTION(julianday,        -1, 0, 0, juliandayFunc ),
    DFUNCTION(date,             -1, 0, 0, dateFunc      ),
    DFUNCTION(time,             -1, 0, 0, timeFunc      ),
    DFUNCTION(datetime,         -1, 0, 0, datetimeFunc  ),
    DFUNCTION(strftime,         -1, 0, 0, strftimeFunc  ),





    DFUNCTION(current_time,      0, 0, 0, ctimeFunc     ),
    DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc),
    DFUNCTION(current_date,      0, 0, 0, cdateFunc     ),
#else
    STR_FUNCTION(current_time,      0, "%H:%M:%S",          0, currentTimeFunc),
    STR_FUNCTION(current_date,      0, "%Y-%m-%d",          0, currentTimeFunc),
    STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc),
#endif
  };
  sqlite3InsertBuiltinFuncs(aDateTimeFuncs, ArraySize(aDateTimeFuncs));
}







>
>
>
>
>
>
>
>
>







 







|







 







|







 







|







 







>
>
>
>
>











75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
...
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
...
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
...
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
....
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
  char validYMD;      /* True (1) if Y,M,D are valid */
  char validHMS;      /* True (1) if h,m,s are valid */
  char validTZ;       /* True (1) if tz is valid */
  char tzSet;         /* Timezone was set explicitly */
  char isError;       /* An overflow has occurred */
};

/*
** Based on the sqlite3_context pointer, determine if the current
** function is "pure" (that is to say, if it omits the 'now' time
** and the 'localtime' and 'utc' modifiers and is thus deterministic
** and suitable for use in indexes).
*/
static int dateFuncIsPure(sqlite3_context *pCtx){
  return sqlite3_user_data(pCtx)!=0;
}

/*
** Convert zDate into one or more integers according to the conversion
** specifier zFormat.
**
** zFormat[] contains 4 characters for each integer converted, except for
** the last integer which is specified by three characters.  The meaning
................................................................................
  DateTime *p
){
  double r;
  if( parseYyyyMmDd(zDate,p)==0 ){
    return 0;
  }else if( parseHhMmSs(zDate, p)==0 ){
    return 0;
  }else if( sqlite3StrICmp(zDate,"now")==0 && !dateFuncIsPure(context) ){
    return setDateTimeToCurrent(context, p);
  }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8) ){
    setRawDateNumber(p, r);
    return 0;
  }
  return 1;
}
................................................................................
#ifndef SQLITE_OMIT_LOCALTIME
    case 'l': {
      /*    localtime
      **
      ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
      ** show local time.
      */
      if( sqlite3_stricmp(z, "localtime")==0 && !dateFuncIsPure(pCtx) ){
        computeJD(p);
        p->iJD += localtimeOffset(p, pCtx, &rc);
        clearYMD_HMS_TZ(p);
      }
      break;
    }
#endif
................................................................................
          p->iJD = (sqlite3_int64)r;
          p->validJD = 1;
          p->rawS = 0;
          rc = 0;
        }
      }
#ifndef SQLITE_OMIT_LOCALTIME
      else if( sqlite3_stricmp(z, "utc")==0 && !dateFuncIsPure(pCtx) ){
        if( p->tzSet==0 ){
          sqlite3_int64 c1;
          computeJD(p);
          c1 = localtimeOffset(p, pCtx, &rc);
          if( rc==SQLITE_OK ){
            p->iJD -= c1;
            clearYMD_HMS_TZ(p);
................................................................................
  static FuncDef aDateTimeFuncs[] = {
#ifndef SQLITE_OMIT_DATETIME_FUNCS
    DFUNCTION(julianday,        -1, 0, 0, juliandayFunc ),
    DFUNCTION(date,             -1, 0, 0, dateFunc      ),
    DFUNCTION(time,             -1, 0, 0, timeFunc      ),
    DFUNCTION(datetime,         -1, 0, 0, datetimeFunc  ),
    DFUNCTION(strftime,         -1, 0, 0, strftimeFunc  ),
    PURE_DATE(pure_julianday,   -1, 0, 0, juliandayFunc ),
    PURE_DATE(pure_date,        -1, 0, 0, dateFunc      ),
    PURE_DATE(pure_time,        -1, 0, 0, timeFunc      ),
    PURE_DATE(pure_datetime,    -1, 0, 0, datetimeFunc  ),
    PURE_DATE(pure_strftime,    -1, 0, 0, strftimeFunc  ),
    DFUNCTION(current_time,      0, 0, 0, ctimeFunc     ),
    DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc),
    DFUNCTION(current_date,      0, 0, 0, cdateFunc     ),
#else
    STR_FUNCTION(current_time,      0, "%H:%M:%S",          0, currentTimeFunc),
    STR_FUNCTION(current_date,      0, "%Y-%m-%d",          0, currentTimeFunc),
    STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc),
#endif
  };
  sqlite3InsertBuiltinFuncs(aDateTimeFuncs, ArraySize(aDateTimeFuncs));
}

Changes to src/sqliteInt.h.

1624
1625
1626
1627
1628
1629
1630
1631







1632
1633
1634
1635
1636
1637
1638
....
1647
1648
1649
1650
1651
1652
1653
1654
1655



1656
1657
1658
1659
1660
1661
1662
**   VFUNCTION(zName, nArg, iArg, bNC, xFunc)
**     Like FUNCTION except it omits the SQLITE_FUNC_CONSTANT flag.
**
**   DFUNCTION(zName, nArg, iArg, bNC, xFunc)
**     Like FUNCTION except it omits the SQLITE_FUNC_CONSTANT flag and
**     adds the SQLITE_FUNC_SLOCHNG flag.  Used for date & time functions
**     and functions like sqlite_version() that can change, but not during
**     a single query.







**
**   AGGREGATE(zName, nArg, iArg, bNC, xStep, xFinal)
**     Used to create an aggregate function definition implemented by
**     the C functions xStep and xFinal. The first four parameters
**     are interpreted in the same way as the first 4 parameters to
**     FUNCTION().
**
................................................................................
#define FUNCTION(zName, nArg, iArg, bNC, xFunc) \
  {nArg, SQLITE_FUNC_CONSTANT|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, #zName, {0} }
#define VFUNCTION(zName, nArg, iArg, bNC, xFunc) \
  {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, #zName, {0} }
#define DFUNCTION(zName, nArg, iArg, bNC, xFunc) \
  {nArg, SQLITE_FUNC_SLOCHNG|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, #zName, {0} }



#define FUNCTION2(zName, nArg, iArg, bNC, xFunc, extraFlags) \
  {nArg,SQLITE_FUNC_CONSTANT|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL)|extraFlags,\
   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, #zName, {0} }
#define STR_FUNCTION(zName, nArg, pArg, bNC, xFunc) \
  {nArg, SQLITE_FUNC_SLOCHNG|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
   pArg, 0, xFunc, 0, #zName, }
#define LIKEFUNC(zName, nArg, arg, flags) \







|
>
>
>
>
>
>
>







 







|
|
>
>
>







1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
....
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
**   VFUNCTION(zName, nArg, iArg, bNC, xFunc)
**     Like FUNCTION except it omits the SQLITE_FUNC_CONSTANT flag.
**
**   DFUNCTION(zName, nArg, iArg, bNC, xFunc)
**     Like FUNCTION except it omits the SQLITE_FUNC_CONSTANT flag and
**     adds the SQLITE_FUNC_SLOCHNG flag.  Used for date & time functions
**     and functions like sqlite_version() that can change, but not during
**     a single query.  The iArg is ignored.  The user-data is always set
**     to a NULL pointer.  The bNC parameter is not used.
**
**   PURE_DATE(zName, nArg, iArg, bNC, xFunc)
**     Used for "pure" date/time functions, this macro is like DFUNCTION
**     except that it does set the SQLITE_FUNC_CONSTANT flags.  iArg is
**     ignored and the user-data for these functions is set to an 
**     arbitrary non-NULL pointer.  The bNC parameter is not used.
**
**   AGGREGATE(zName, nArg, iArg, bNC, xStep, xFinal)
**     Used to create an aggregate function definition implemented by
**     the C functions xStep and xFinal. The first four parameters
**     are interpreted in the same way as the first 4 parameters to
**     FUNCTION().
**
................................................................................
#define FUNCTION(zName, nArg, iArg, bNC, xFunc) \
  {nArg, SQLITE_FUNC_CONSTANT|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, #zName, {0} }
#define VFUNCTION(zName, nArg, iArg, bNC, xFunc) \
  {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, #zName, {0} }
#define DFUNCTION(zName, nArg, iArg, bNC, xFunc) \
  {nArg, SQLITE_FUNC_SLOCHNG|SQLITE_UTF8, \
   0, 0, xFunc, 0, #zName, {0} }
#define PURE_DATE(zName, nArg, iArg, bNC, xFunc) \
  {nArg, SQLITE_FUNC_SLOCHNG|SQLITE_UTF8|SQLITE_FUNC_CONSTANT, \
   (void*)xFunc, 0, xFunc, 0, #zName, {0} }
#define FUNCTION2(zName, nArg, iArg, bNC, xFunc, extraFlags) \
  {nArg,SQLITE_FUNC_CONSTANT|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL)|extraFlags,\
   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, #zName, {0} }
#define STR_FUNCTION(zName, nArg, pArg, bNC, xFunc) \
  {nArg, SQLITE_FUNC_SLOCHNG|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \
   pArg, 0, xFunc, 0, #zName, }
#define LIKEFUNC(zName, nArg, arg, flags) \

Added test/date-pure.test.





























































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
# 2017-07-19
#
# 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

# Do not use a codec for tests in this file, as the database file is
# manipulated directly using tcl scripts (using the [hexio_write] command).
#
do_not_use_codec

# 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 date-pure-$tnum [subst {
    execsql "SELECT coalesce(pure_$expr,'NULL')"
  }] [list $result]
}
set tcl_precision 15
datetest 1.1 julianday('2000-01-01') 2451544.5
datetest 1.2 julianday('1970-01-01') 2440587.5
datetest 1.3 julianday('1910-04-20') 2418781.5
datetest 1.4 julianday('1986-02-09') 2446470.5
datetest 1.5 julianday('12:00:00') 2451545.0
datetest 1.6 {julianday('2000-01-01 12:00:00')} 2451545.0
datetest 1.7 {julianday('2000-01-01 12:00')} 2451545.0
datetest 1.8 julianday('bogus') NULL
datetest 1.9 julianday('1999-12-31') 2451543.5
datetest 1.10 julianday('1999-12-32') NULL
datetest 1.11 julianday('1999-13-01') NULL
datetest 1.12 julianday('2003-02-31') 2452701.5
datetest 1.13 julianday('2003-03-03') 2452701.5
datetest 1.14 julianday('+2000-01-01') NULL
datetest 1.15 julianday('200-01-01') NULL
datetest 1.16 julianday('2000-1-01') NULL
datetest 1.17 julianday('2000-01-1') NULL
datetest 1.18.1 {julianday('2000-01-01     12:00:00')} 2451545.0
datetest 1.18.2 {julianday('2000-01-01T12:00:00')} 2451545.0
datetest 1.18.3 {julianday('2000-01-01 T12:00:00')} 2451545.0
datetest 1.18.4 {julianday('2000-01-01T 12:00:00')} 2451545.0
datetest 1.18.4 {julianday('2000-01-01 T 12:00:00')} 2451545.0
datetest 1.19 {julianday('2000-01-01 12:00:00.1')}   2451545.00000116
datetest 1.20 {julianday('2000-01-01 12:00:00.01')}  2451545.00000012
datetest 1.21 {julianday('2000-01-01 12:00:00.001')} 2451545.00000001
datetest 1.22 {julianday('2000-01-01 12:00:00.')} NULL
datetest 1.23 julianday(12345.6) 12345.6
datetest 1.23b julianday(1721059.5) 1721059.5
datetest 1.24 {julianday('2001-01-01 12:00:00 bogus')} NULL
datetest 1.25 {julianday('2001-01-01 bogus')} NULL
datetest 1.26 {julianday('2001-01-01 12:60:00')} NULL
datetest 1.27 {julianday('2001-01-01 12:59:60')} NULL
datetest 1.28 {julianday('2001-00-01')} NULL
datetest 1.29 {julianday('2001-01-00')} NULL

datetest 2.1 datetime(0,'unixepoch') {1970-01-01 00:00:00}
datetest 2.1b datetime(0,'unixepoc') NULL
datetest 2.1c datetime(0,'unixepochx') NULL
datetest 2.1d datetime('2003-10-22','unixepoch') NULL
datetest 2.2 datetime(946684800,'unixepoch') {2000-01-01 00:00:00}
datetest 2.2b datetime('946684800','unixepoch') {2000-01-01 00:00:00}
for {set i 0} {$i<1000} {incr i} {
  set sql [format {strftime('%%H:%%M:%%f',1237962480.%03d,'unixepoch')} $i]
  set res [format {06:28:00.%03d} $i]
  datetest 2.2c-$i $sql $res
}
datetest 2.3 {date('2003-10-22','weekday 0')} 2003-10-26
datetest 2.4 {date('2003-10-22','weekday 1')} 2003-10-27
datetest 2.4a {date('2003-10-22','weekday  1')} 2003-10-27
datetest 2.4b {date('2003-10-22','weekday  1x')} NULL
datetest 2.4c {date('2003-10-22','weekday  -1')} NULL
datetest 2.4d {date('2003-10-22','weakday  1x')} NULL
datetest 2.4e {date('2003-10-22','weekday ')} NULL
datetest 2.5 {date('2003-10-22','weekday 2')} 2003-10-28
datetest 2.6 {date('2003-10-22','weekday 3')} 2003-10-22
datetest 2.7 {date('2003-10-22','weekday 4')} 2003-10-23
datetest 2.8 {date('2003-10-22','weekday 5')} 2003-10-24
datetest 2.9 {date('2003-10-22','weekday 6')} 2003-10-25
datetest 2.10 {date('2003-10-22','weekday 7')} NULL
datetest 2.11 {date('2003-10-22','weekday 5.5')} NULL
datetest 2.12 {datetime('2003-10-22 12:34','weekday 0')} {2003-10-26 12:34:00}
datetest 2.13 {datetime('2003-10-22 12:34','start of month')} \
   {2003-10-01 00:00:00}
datetest 2.14 {datetime('2003-10-22 12:34','start of year')} \
   {2003-01-01 00:00:00}
datetest 2.15 {datetime('2003-10-22 12:34','start of day')} \
   {2003-10-22 00:00:00}
datetest 2.15a {datetime('2003-10-22 12:34','start of')} NULL
datetest 2.15b {datetime('2003-10-22 12:34','start of bogus')} NULL
datetest 2.16 time('12:34:56.43') 12:34:56
datetest 2.17 {datetime('2003-10-22 12:34','1 day')} {2003-10-23 12:34:00}
datetest 2.18 {datetime('2003-10-22 12:34','+1 day')} {2003-10-23 12:34:00}
datetest 2.19 {datetime('2003-10-22 12:34','+1.25 day')} {2003-10-23 18:34:00}
datetest 2.20 {datetime('2003-10-22 12:34','-1.0 day')} {2003-10-21 12:34:00}
datetest 2.21 {datetime('2003-10-22 12:34','1 month')} {2003-11-22 12:34:00}
datetest 2.22 {datetime('2003-10-22 12:34','11 month')} {2004-09-22 12:34:00}
datetest 2.23 {datetime('2003-10-22 12:34','-13 month')} {2002-09-22 12:34:00}
datetest 2.24 {datetime('2003-10-22 12:34','1.5 months')} {2003-12-07 12:34:00}
datetest 2.25 {datetime('2003-10-22 12:34','-5 years')} {1998-10-22 12:34:00}
datetest 2.26 {datetime('2003-10-22 12:34','+10.5 minutes')} \
  {2003-10-22 12:44:30}
datetest 2.27 {datetime('2003-10-22 12:34','-1.25 hours')} \
  {2003-10-22 11:19:00}
datetest 2.28 {datetime('2003-10-22 12:34','11.25 seconds')} \
  {2003-10-22 12:34:11}
datetest 2.29 {datetime('2003-10-22 12:24','+5 bogus')} NULL
datetest 2.30 {datetime('2003-10-22 12:24','+++')} NULL
datetest 2.31 {datetime('2003-10-22 12:24','+12.3e4 femtoseconds')} NULL
datetest 2.32 {datetime('2003-10-22 12:24','+12.3e4 uS')} NULL
datetest 2.33 {datetime('2003-10-22 12:24','+1 abc')} NULL
datetest 2.34 {datetime('2003-10-22 12:24','+1 abcd')} NULL
datetest 2.35 {datetime('2003-10-22 12:24','+1 abcde')} NULL
datetest 2.36 {datetime('2003-10-22 12:24','+1 abcdef')} NULL
datetest 2.37 {datetime('2003-10-22 12:24','+1 abcdefg')} NULL
datetest 2.38 {datetime('2003-10-22 12:24','+1 abcdefgh')} NULL
datetest 2.39 {datetime('2003-10-22 12:24','+1 abcdefghi')} NULL
set sqlite_current_time 1199243045
datetest 2.40 {datetime()} {2008-01-02 03:04:05}
set sqlite_current_time 0
datetest 2.41 {datetime('2003-10-22 12:24','23 seconds')} {2003-10-22 12:24:23}
datetest 2.42 {datetime('2003-10-22 12:24','345 second')} {2003-10-22 12:29:45}
datetest 2.43 {datetime('2003-10-22 12:24','4 second')} {2003-10-22 12:24:04}
datetest 2.44 {datetime('2003-10-22 12:24','56 second')} {2003-10-22 12:24:56}
datetest 2.45 {datetime('2003-10-22 12:24','60 second')} {2003-10-22 12:25:00}
datetest 2.46 {datetime('2003-10-22 12:24','70 second')} {2003-10-22 12:25:10}
datetest 2.47 {datetime('2003-10-22 12:24','8.6 seconds')} {2003-10-22 12:24:08}
datetest 2.48 {datetime('2003-10-22 12:24','9.4 second')} {2003-10-22 12:24:09}
datetest 2.49 {datetime('2003-10-22 12:24','0000 second')} {2003-10-22 12:24:00}
datetest 2.50 {datetime('2003-10-22 12:24','0001 second')} {2003-10-22 12:24:01}
datetest 2.51 {datetime('2003-10-22 12:24','nonsense')} NULL

datetest 3.1 {strftime('%d','2003-10-31 12:34:56.432')} 31
datetest 3.2.1 {strftime('pre%fpost','2003-10-31 12:34:56.432')} pre56.432post
datetest 3.2.2 {strftime('%f','2003-10-31 12:34:59.9999999')} 59.999
datetest 3.3 {strftime('%H','2003-10-31 12:34:56.432')} 12
datetest 3.4 {strftime('%j','2003-10-31 12:34:56.432')} 304
datetest 3.5 {strftime('%J','2003-10-31 12:34:56.432')} 2452944.024264259
datetest 3.6 {strftime('%m','2003-10-31 12:34:56.432')} 10
datetest 3.7 {strftime('%M','2003-10-31 12:34:56.432')} 34
datetest 3.8.1 {strftime('%s','2003-10-31 12:34:56.432')} 1067603696
datetest 3.8.2 {strftime('%s','2038-01-19 03:14:07')} 2147483647
datetest 3.8.3 {strftime('%s','2038-01-19 03:14:08')} 2147483648
datetest 3.8.4 {strftime('%s','2201-04-09 12:00:00')} 7298164800
datetest 3.8.5 {strftime('%s','9999-12-31 23:59:59')} 253402300799
datetest 3.8.6 {strftime('%s','1969-12-31 23:59:59')} -1
datetest 3.8.7 {strftime('%s','1901-12-13 20:45:52')} -2147483648
datetest 3.8.8 {strftime('%s','1901-12-13 20:45:51')} -2147483649
datetest 3.8.9 {strftime('%s','1776-07-04 00:00:00')} -6106060800
datetest 3.9 {strftime('%S','2003-10-31 12:34:56.432')} 56
datetest 3.10 {strftime('%w','2003-10-31 12:34:56.432')} 5
datetest 3.11.1 {strftime('%W','2003-10-31 12:34:56.432')} 43
datetest 3.11.2 {strftime('%W','2004-01-01')} 00
datetest 3.11.3 {strftime('%W','2004-01-02')} 00
datetest 3.11.4 {strftime('%W','2004-01-03')} 00
datetest 3.11.5 {strftime('abc%Wxyz','2004-01-04')} abc00xyz
datetest 3.11.6 {strftime('%W','2004-01-05')} 01
datetest 3.11.7 {strftime('%W','2004-01-06')} 01
datetest 3.11.8 {strftime('%W','2004-01-07')} 01
datetest 3.11.9 {strftime('%W','2004-01-08')} 01
datetest 3.11.10 {strftime('%W','2004-01-09')} 01
datetest 3.11.11 {strftime('%W','2004-07-18')} 28
datetest 3.11.12 {strftime('%W','2004-12-31')} 52
datetest 3.11.13 {strftime('%W','2007-12-31')} 53
datetest 3.11.14 {strftime('%W','2007-01-01')} 01
datetest 3.11.15 {strftime('%W %j',2454109.04140970)} {02 008}
datetest 3.11.16 {strftime('%W %j',2454109.04140971)} {02 008}
datetest 3.11.17 {strftime('%W %j',2454109.04140972)} {02 008}
datetest 3.11.18 {strftime('%W %j',2454109.04140973)} {02 008}
datetest 3.11.19 {strftime('%W %j',2454109.04140974)} {02 008}
datetest 3.11.20 {strftime('%W %j',2454109.04140975)} {02 008}
datetest 3.11.21 {strftime('%W %j',2454109.04140976)} {02 008}
datetest 3.11.22 {strftime('%W %j',2454109.04140977)} {02 008}
datetest 3.11.23 {strftime('%W %j',2454109.04140978)} {02 008}
datetest 3.11.24 {strftime('%W %j',2454109.04140979)} {02 008}
datetest 3.11.25 {strftime('%W %j',2454109.04140980)} {02 008}
datetest 3.11.99 {strftime('%W %j','2454109.04140970')} {02 008}
datetest 3.12 {strftime('%Y','2003-10-31 12:34:56.432')} 2003
datetest 3.13 {strftime('%%','2003-10-31 12:34:56.432')} %
datetest 3.14 {strftime('%_','2003-10-31 12:34:56.432')} NULL
datetest 3.15 {strftime('%Y-%m-%d','2003-10-31')} 2003-10-31
proc repeat {n txt} {
  set x {} 
  while {$n>0} {
    append x $txt
    incr n -1
  }
  return $x
}
datetest 3.16 "strftime('[repeat 200 %Y]','2003-10-31')" [repeat 200 2003]
datetest 3.17 "strftime('[repeat 200 abc%m123]','2003-10-31')" \
    [repeat 200 abc10123]

foreach c {a b c e g h i k l n o p q r t v x y z
           A B C D E F G I K L N O P Q R T U V Z
           0 1 2 3 4 5 6 6 7 9 _} {
  datetest 3.18.$c "strftime('%$c','2003-10-31')" NULL
}

# Ticket #2276.  Make sure leading zeros are inserted where appropriate.
#
datetest 3.20 \
   {strftime('%d/%f/%H/%W/%j/%m/%M/%S/%Y','0421-01-02 03:04:05.006')} \
   02/05.006/03/00/002/01/04/05/0421

set sqlite_current_time 1157124367
datetest 4.1 {date('now')} NULL
set sqlite_current_time 0

datetest 5.1 {datetime('1994-04-16 14:00:00 +05:00')} {1994-04-16 09:00:00}
datetest 5.2 {datetime('1994-04-16 14:00:00 -05:15')} {1994-04-16 19:15:00}
datetest 5.3 {datetime('1994-04-16 05:00:00 +08:30')} {1994-04-15 20:30:00}
datetest 5.4 {datetime('1994-04-16 14:00:00 -11:55')} {1994-04-17 01:55:00}
datetest 5.5 {datetime('1994-04-16 14:00:00 -11:60')} NULL
datetest 5.6 {datetime('1994-04-16 14:00:00 -11:55  ')} {1994-04-17 01:55:00}
datetest 5.7 {datetime('1994-04-16 14:00:00 -11:55 x')} NULL
datetest 5.8 {datetime('1994-04-16T14:00:00Z')} {1994-04-16 14:00:00}
datetest 5.9 {datetime('1994-04-16 14:00:00z')} {1994-04-16 14:00:00}
datetest 5.10 {datetime('1994-04-16 14:00:00 Z')} {1994-04-16 14:00:00}
datetest 5.11 {datetime('1994-04-16 14:00:00z    ')} {1994-04-16 14:00:00}
datetest 5.12 {datetime('1994-04-16 14:00:00     z    ')} {1994-04-16 14:00:00}
datetest 5.13 {datetime('1994-04-16 14:00:00Zulu')} NULL
datetest 5.14 {datetime('1994-04-16 14:00:00Z +05:00')} NULL
datetest 5.15 {datetime('1994-04-16 14:00:00 +05:00 Z')} NULL

# localtime->utc and utc->localtime conversions.  These tests only work
# if the localtime is in the US Eastern Time (the time in Charlotte, NC
# and in New York.)
#
# On non-Vista Windows platform, '2006-03-31' is treated incorrectly as being
# in DST giving a 4 hour offset instead of 5.  In 2007, DST was extended to 
# start three weeks earlier (second Sunday in March) and end one week
# later (first Sunday in November).  Older Windows systems apply this
# new rule incorrectly to dates prior to 2007.
#
# It might be argued that this is masking a problem on non-Vista Windows
# platform.  A ticket has already been opened for this issue 
# (http://www.sqlite.org/cvstrac/tktview?tn=2322).  This is just to prevent
# more confusion/reports of the issue.
#

# $tzoffset_old should be 5 if DST is working correctly.
set tzoffset_old [db one {
  SELECT CAST(24*(julianday('2006-03-31') -
                  julianday('2006-03-31','localtime'))+0.5
              AS INT)
}]

# $tzoffset_new should be 4 if DST is working correctly.
set tzoffset_new [db one {
  SELECT CAST(24*(julianday('2007-03-31') -
                  julianday('2007-03-31','localtime'))+0.5
              AS INT)
}]

# Warn about possibly broken Windows DST implementations.
if {$::tcl_platform(platform)=="windows" && $tzoffset_new==4 && $tzoffset_old==4} {
  puts "******************************************************************"
  puts "N.B.:  The DST support provided by your current O/S seems to be"
  puts "suspect in that it is reporting incorrect DST values for dates"
  puts "prior to 2007.  This is the known case for most (all?) non-Vista"
  puts "Windows versions.  Please see ticket #2322 for more information."
  puts "******************************************************************"
}

if {$tzoffset_new==4} {
  datetest 6.1 {datetime('2000-10-29 05:59:00','localtime')} NULL
  datetest 6.1.1 {datetime('2006-10-29 05:59:00','localtime')} NULL
  datetest 6.1.2 {datetime('2007-11-04 05:59:00','localtime')} NULL

  # If the new and old DST rules seem to be working correctly...
  if {$tzoffset_new==4 && $tzoffset_old==5} {
    datetest 6.2 {datetime('2000-10-29 06:00:00','localtime')} NULL
    datetest 6.2.1 {datetime('2006-10-29 06:00:00','localtime')} NULL
  }
  datetest 6.2.2 {datetime('2007-11-04 06:00:00','localtime')} NULL

  # If the new and old DST rules seem to be working correctly...
  if {$tzoffset_new==4 && $tzoffset_old==5} {
    datetest 6.3 {datetime('2000-04-02 06:59:00','localtime')} NULL
    datetest 6.3.1 {datetime('2006-04-02 06:59:00','localtime')} NULL
  }
  datetest 6.3.2 {datetime('2007-03-11 07:00:00','localtime')} NULL
  datetest 6.4 {datetime('2000-04-02 07:00:00','localtime')} NULL
  datetest 6.4.1 {datetime('2006-04-02 07:00:00','localtime')} NULL
  datetest 6.4.2 {datetime('2007-03-11 07:00:00','localtime')} NULL
      
  datetest 6.5 {datetime('2000-10-29 01:59:00','utc')} NULL
  datetest 6.5.1 {datetime('2006-10-29 01:59:00','utc')} NULL
  datetest 6.5.2 {datetime('2007-11-04 01:59:00','utc')} NULL

  # If the new and old DST rules seem to be working correctly...
  if {$tzoffset_new==4 && $tzoffset_old==5} {
    datetest 6.6 {datetime('2000-10-29 02:00:00','utc')} NULL
    datetest 6.6.1 {datetime('2006-10-29 02:00:00','utc')} NULL
  }
  datetest 6.6.2 {datetime('2007-11-04 02:00:00','utc')} NULL

  # If the new and old DST rules seem to be working correctly...
  if {$tzoffset_new==4 && $tzoffset_old==5} {
    datetest 6.7 {datetime('2000-04-02 01:59:00','utc')} NULL
    datetest 6.7.1 {datetime('2006-04-02 01:59:00','utc')} NULL
  }
  datetest 6.7.2 {datetime('2007-03-11 01:59:00','utc')} NULL

  datetest 6.8 {datetime('2000-04-02 02:00:00','utc')} NULL
  datetest 6.8.1 {datetime('2006-04-02 02:00:00','utc')} NULL
  datetest 6.8.2 {datetime('2007-03-11 02:00:00','utc')} NULL

  # The 'utc' modifier is a no-op if the LHS is known to already be in UTC
  datetest 6.9.1 {datetime('2015-12-23 12:00:00','utc')} NULL
  datetest 6.9.2 {datetime('2015-12-23 12:00:00z','utc')} NULL
  datetest 6.9.3 {datetime('2015-12-23 12:00:00-03:00','utc')} NULL
  datetest 6.9.4 {datetime('2015-12-23 12:00:00','utc','utc','utc')} NULL


  datetest 6.10 {datetime('2000-01-01 12:00:00','localtime')} NULL
  datetest 6.11 {datetime('1969-01-01 12:00:00','localtime')} NULL
  datetest 6.12 {datetime('2039-01-01 12:00:00','localtime')} NULL
  datetest 6.13 {datetime('2000-07-01 12:00:00','localtime')} NULL
  datetest 6.14 {datetime('1969-07-01 12:00:00','localtime')} NULL
  datetest 6.15 {datetime('2039-07-01 12:00:00','localtime')} NULL
  set sqlite_current_time \
     [db eval {SELECT strftime('%s','2000-07-01 12:34:56')}]
  datetest 6.16 {datetime('now','localtime')} NULL
  datetest 6.17 {datetime('now','localtimex')} NULL
  datetest 6.18 {datetime('now','localtim')} NULL
  set sqlite_current_time 0
}

# These two are a bit of a scam. They are added to ensure that 100% of
# the date.c file is covered by testing, even when the time-zone
# is not -0400 (the condition for running of the block of tests above).
#
datetest 6.19 {datetime('2039-07-01 12:00:00','localtime',null)} NULL
datetest 6.20 {datetime('2039-07-01 12:00:00','utc',null)} NULL

# Date-time functions that contain NULL arguments return a NULL
# result.
#
datetest 7.1 {datetime(null)} NULL
datetest 7.2 {datetime('now',null)} NULL
datetest 7.3 {datetime('now','localtime',null)} NULL
datetest 7.4 {time(null)} NULL
datetest 7.5 {time('now',null)} NULL
datetest 7.6 {time('now','localtime',null)} NULL
datetest 7.7 {date(null)} NULL
datetest 7.8 {date('now',null)} NULL
datetest 7.9 {date('now','localtime',null)} NULL
datetest 7.10 {julianday(null)} NULL
datetest 7.11 {julianday('now',null)} NULL
datetest 7.12 {julianday('now','localtime',null)} NULL
datetest 7.13 {strftime(null,'now')} NULL
datetest 7.14 {strftime('%s',null)} NULL
datetest 7.15 {strftime('%s','now',null)} NULL
datetest 7.16 {strftime('%s','now','localtime',null)} NULL

# Test modifiers when the date begins as a julian day number - to
# make sure the HH:MM:SS is preserved.  Ticket #551.
#
set sqlite_current_time [db eval {SELECT strftime('%s','2003-10-22 12:34:00')}]
datetest 8.1 {datetime('now','weekday 0')} NULL
datetest 8.2 {datetime('now','weekday 1')} NULL
datetest 8.3 {datetime('now','weekday 2')} NULL
datetest 8.4 {datetime('now','weekday 3')} NULL
datetest 8.5 {datetime('now','start of month')} NULL
datetest 8.6 {datetime('now','start of year')} NULL
datetest 8.7 {datetime('now','start of day')} NULL
datetest 8.8 {datetime('now','1 day')} NULL
datetest 8.9 {datetime('now','+1 day')} NULL
datetest 8.10 {datetime('now','+1.25 day')} NULL
datetest 8.11 {datetime('now','-1.0 day')} NULL
datetest 8.12 {datetime('now','1 month')} NULL
datetest 8.13 {datetime('now','11 month')} NULL
datetest 8.14 {datetime('now','-13 month')} NULL
datetest 8.15 {datetime('now','1.5 months')} NULL
datetest 8.16 {datetime('now','-5 years')} NULL
datetest 8.17 {datetime('now','+10.5 minutes')} NULL
datetest 8.18 {datetime('now','-1.25 hours')} NULL
datetest 8.19 {datetime('now','11.25 seconds')} NULL
datetest 8.90 {datetime('now','abcdefghijklmnopqrstuvwyxzABCDEFGHIJLMNOP')} NULL
set sqlite_current_time 0

# Negative years work.  Example:  '-4713-11-26' is JD 1.5.
#
datetest 9.1 {julianday('-4713-11-24 12:00:00')} {0.0}
datetest 9.2 {julianday(datetime(5))} {5.0}
datetest 9.3 {julianday(datetime(10))} {10.0}
datetest 9.4 {julianday(datetime(100))} {100.0}
datetest 9.5 {julianday(datetime(1000))} {1000.0}
datetest 9.6 {julianday(datetime(10000))} {10000.0}
datetest 9.7 {julianday(datetime(100000))} {100000.0}

# datetime() with just an HH:MM:SS correctly inserts the date 2000-01-01.
#
datetest 10.1 {datetime('01:02:03')}  {2000-01-01 01:02:03}
datetest 10.2 {date('01:02:03')}  {2000-01-01}
datetest 10.3 {strftime('%Y-%m-%d %H:%M','01:02:03')} {2000-01-01 01:02}

# Test the new HH:MM:SS modifier
#
datetest 11.1 {datetime('2004-02-28 20:00:00', '-01:20:30')} \
   {2004-02-28 18:39:30}
datetest 11.2 {datetime('2004-02-28 20:00:00', '+12:30:00')} \
   {2004-02-29 08:30:00}
datetest 11.3 {datetime('2004-02-28 20:00:00', '+12:30')} \
   {2004-02-29 08:30:00}
datetest 11.4 {datetime('2004-02-28 20:00:00', '12:30')} \
   {2004-02-29 08:30:00}
datetest 11.5 {datetime('2004-02-28 20:00:00', '-12:00')} \
   {2004-02-28 08:00:00}
datetest 11.6 {datetime('2004-02-28 20:00:00', '-12:01')} \
   {2004-02-28 07:59:00}
datetest 11.7 {datetime('2004-02-28 20:00:00', '-11:59')} \
   {2004-02-28 08:01:00}
datetest 11.8 {datetime('2004-02-28 20:00:00', '11:59')} \
   {2004-02-29 07:59:00}
datetest 11.9 {datetime('2004-02-28 20:00:00', '12:01')} \
   {2004-02-29 08:01:00}
datetest 11.10 {datetime('2004-02-28 20:00:00', '12:60')} NULL

# Ticket #1964
datetest 12.1 {datetime('2005-09-01')} {2005-09-01 00:00:00}
datetest 12.2 {datetime('2005-09-01','+0 hours')} {2005-09-01 00:00:00}

# Ticket #1991
do_test date-13.1 {
  execsql {
    SELECT strftime('%Y-%m-%d %H:%M:%f', julianday('2006-09-24T10:50:26.047'))
  }
} {{2006-09-24 10:50:26.047}}

# Ticket #2153
datetest 13.2 {strftime('%Y-%m-%d %H:%M:%S', '2007-01-01 12:34:59.6')} \
  {2007-01-01 12:34:59}
datetest 13.3 {strftime('%Y-%m-%d %H:%M:%f', '2007-01-01 12:34:59.6')} \
  {2007-01-01 12:34:59.600}
datetest 13.4 {strftime('%Y-%m-%d %H:%M:%S', '2007-01-01 12:59:59.6')} \
  {2007-01-01 12:59:59}
datetest 13.5 {strftime('%Y-%m-%d %H:%M:%f', '2007-01-01 12:59:59.6')} \
  {2007-01-01 12:59:59.600}
datetest 13.6 {strftime('%Y-%m-%d %H:%M:%S', '2007-01-01 23:59:59.6')} \
  {2007-01-01 23:59:59}
datetest 13.7 {strftime('%Y-%m-%d %H:%M:%f', '2007-01-01 23:59:59.6')} \
  {2007-01-01 23:59:59.600}

# Ticket #3618
datetest 13.11 {julianday(2454832.5,'-1 day')} {2454831.5}
datetest 13.12 {julianday(2454832.5,'+1 day')} {2454833.5}
datetest 13.13 {julianday(2454832.5,'-1.5 day')} {2454831.0}
datetest 13.14 {julianday(2454832.5,'+1.5 day')} {2454834.0}
datetest 13.15 {julianday(2454832.5,'-3 hours')} {2454832.375}
datetest 13.16 {julianday(2454832.5,'+3 hours')} {2454832.625}
datetest 13.17 {julianday(2454832.5,'-45 minutes')} {2454832.46875}
datetest 13.18 {julianday(2454832.5,'+45 minutes')} {2454832.53125}
datetest 13.19 {julianday(2454832.5,'-675 seconds')} {2454832.4921875}
datetest 13.20 {julianday(2454832.5,'+675 seconds')} {2454832.5078125}
datetest 13.21 {julianday(2454832.5,'-1.5 months')} {2454786.5}
datetest 13.22 {julianday(2454832.5,'+1.5 months')} {2454878.5}
datetest 13.23 {julianday(2454832.5,'-1.5 years')} {2454284.0}
datetest 13.24 {julianday(2454832.5,'+1.5 years')} {2455380.0}

datetest 13.30 {date('2000-01-01','+1.5 years')} {2001-07-02}
datetest 13.31 {date('2001-01-01','+1.5 years')} {2002-07-02}
datetest 13.32 {date('2002-01-01','+1.5 years')} {2003-07-02}
datetest 13.33 {date('2002-01-01','-1.5 years')} {2000-07-02}
datetest 13.34 {date('2001-01-01','-1.5 years')} {1999-07-02}

# Test for issues reported by BareFeet (list.sql at tandb.com.au)
# on mailing list on 2008-06-12.
#
# Put a floating point number in the database so that we can manipulate
# raw bits using the hexio interface.
#
if {0==[sqlite3 -has-codec]} {
  do_test date-14.1 {
    execsql {
      PRAGMA auto_vacuum=OFF;
      PRAGMA page_size = 1024;
      CREATE TABLE t1(x);
      INSERT INTO t1 VALUES(1.1);
    }
    db close
    hexio_write test.db 2040 4142ba32bffffff9
    sqlite3 db test.db
    db eval {SELECT * FROM t1}
  } {2454629.5}
  
  # Changing the least significant byte of the floating point value between
  # 00 and FF should always generate a time of either 23:59:59 or 00:00:00,
  # never 24:00:00
  #
  for {set i 0} {$i<=255} {incr i} {
    db close
    hexio_write test.db 2047 [format %02x $i]
    sqlite3 db test.db
    do_test date-14.2.$i {
      set date [db one {SELECT datetime(x) FROM t1}]
      expr {$date eq "2008-06-12 00:00:00" || $date eq "2008-06-11 23:59:59"}
    } {1}
  }
}

# Verify that multiple calls to date functions with 'now' return the
# same answer.
#
# EVIDENCE-OF: R-34818-13664 The 'now' argument to date and time
# functions always returns exactly the same value for multiple
# invocations within the same sqlite3_step() call.
#
proc sleeper {} {after 100}
do_test date-15.1 {
  db func sleeper sleeper
  db eval {
     SELECT c - a FROM (SELECT julianday('now') AS a,
                               sleeper(), julianday('now') AS c);
  }
} {0.0}
do_test date-15.2 {
  db eval {
     SELECT a==b FROM (SELECT current_timestamp AS a,
                               sleeper(), current_timestamp AS b);
  }
} {1}

# Tests of extreme values in date/time functions.  Run with UBSan or the
# equivalent to verify no signed interger overflow warnings.
#
datetest 16.1 {date(147483649)} NULL
datetest 16.2 {datetime(0)} {-4713-11-24 12:00:00}
datetest 16.3 {datetime(5373484.49999999)} {9999-12-31 23:59:59}
datetest 16.4 {julianday('-4713-11-24 12:00:00')} 0.0
datetest 16.5 {julianday('9999-12-31 23:59:59.999')} 5373484.49999999
datetest 16.6 {datetime(0,'+464269060799 seconds')} {9999-12-31 23:59:59}
datetest 16.7 {datetime(0,'+464269060800 seconds')} NULL
datetest 16.8 {datetime(0,'+7737817679 minutes')} {9999-12-31 23:59:00}
datetest 16.9 {datetime(0,'+7737817680 minutes')} NULL
datetest 16.10 {datetime(0,'+128963627 hours')} {9999-12-31 23:00:00}
datetest 16.11 {datetime(0,'+128963628 hours')} NULL
datetest 16.12 {datetime(0,'+5373484 days')} {9999-12-31 12:00:00}
datetest 16.13 {datetime(0,'+5373485 days')} NULL
datetest 16.14 {datetime(0,'+176545 months')} {9999-12-24 12:00:00}
datetest 16.15 {datetime(0,'+176546 months')} NULL
datetest 16.16 {datetime(0,'+14712 years')} {9999-11-24 12:00:00}
datetest 16.17 {datetime(0,'+14713 years')} NULL
datetest 16.20 {datetime(5373484.4999999,'-464269060799 seconds')} \
                {-4713-11-24 12:00:00}
datetest 16.21 {datetime(5373484,'-464269060800 seconds')} NULL
datetest 16.22 {datetime(5373484.4999999,'-7737817679 minutes')} \
               {-4713-11-24 12:00:59}
datetest 16.23 {datetime(5373484,'-7737817680 minutes')} NULL
datetest 16.24 {datetime(5373484.4999999,'-128963627 hours')} \
               {-4713-11-24 12:59:59}
datetest 16.25 {datetime(5373484,'-128963628 hours')} NULL
datetest 16.26 {datetime(5373484,'-5373484 days')} {-4713-11-24 12:00:00}
datetest 16.27 {datetime(5373484,'-5373485 days')} NULL
datetest 16.28 {datetime(5373484,'-176545 months')} {-4713-12-01 12:00:00}
datetest 16.29 {datetime(5373484,'-176546 months')} NULL
datetest 16.30 {datetime(5373484,'-14712 years')} {-4713-12-31 12:00:00}
datetest 16.31 {datetime(5373484,'-14713 years')} NULL

# 2017-03-02: Wrong 'start of day' computation.
# https://www.sqlite.org/src/info/6097cb92745327a1
#
datetest 17.1 {datetime(2457754, 'start of day')} {2016-12-31 00:00:00}
datetest 17.2 {datetime(2457828)} {2017-03-15 12:00:00}
datetest 17.3 {datetime(2457828,'start of day')} {2017-03-15 00:00:00}
datetest 17.4 {datetime(2457828,'start of month')} {2017-03-01 00:00:00}
datetest 17.5 {datetime(2457828,'start of year')} {2017-01-01 00:00:00}
datetest 17.6 {datetime(37,'start of year')} NULL
datetest 17.7 {datetime(38,'start of year')} {-4712-01-01 00:00:00}



finish_test