/ Check-in [8482b8c4]
Login

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

Overview
Comment:Add localtime<-->UTC conversions to the date functions. (CVS 1146)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:8482b8c44766e7f80fc449b3dbdd3f37396c332b
User & Date: drh 2003-12-23 16:22:18
References
2011-06-21
12:59 Ticket [bd484a09] Date/time functions do not detect failure of localtime_r() status still Open with 4 other changes artifact: d51061ca user: dan
Context
2003-12-23
16:34
Update date/time functions so that they correctly handle NULL arguments. (CVS 1147) check-in: a5a5fbd6 user: drh tags: trunk
16:22
Add localtime<-->UTC conversions to the date functions. (CVS 1146) check-in: 8482b8c4 user: drh tags: trunk
03:06
Test functions tolerate an "0x" before a pointer value. Ticket #452. (CVS 1145) check-in: c6c5e07b user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/date.c.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
..
47
48
49
50
51
52
53

54
55
56
57
58
59
60
...
362
363
364
365
366
367
368














































369
370
371
372
373
374
375
...
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
** This file contains the C functions that implement date and time
** functions for SQLite.  
**
** There is only one exported symbol in this file - the function
** sqliteRegisterDateTimeFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: date.c,v 1.2 2003/12/23 02:17:35 drh Exp $
**
** NOTES:
**
** SQLite processes all times and dates as Julian Day numbers.  The
** dates and times are stored as the number of days since noon
** in Greenwich on November 24, 4714 B.C. according to the Gregorian
** calendar system.
................................................................................
**      Willmann-Bell, Inc
**      Richmond, Virginia (USA)
*/
#ifndef SQLITE_OMIT_DATETIME_FUNCS
#include <ctype.h>
#include <stdlib.h>
#include <assert.h>

#include "sqliteInt.h"
#include "os.h"

/*
** A structure for holding a single date and time.
*/
typedef struct DateTime DateTime;
................................................................................
  p->s -= s;
  p->h = s/3600;
  s -= p->h*3600;
  p->m = s/60;
  p->s += s - p->m*60;
  p->validHMS = 1;
}















































/*
** Process a modifier to a date-time stamp.  The modifiers are
** as follows:
**
**     NNN days
**     NNN hours
................................................................................
**     NNN years
**     start of month
**     start of year
**     start of week
**     start of day
**     weekday N
**     unixepoch


**
** Return 0 on success and 1 if there is any kind of error.
*/
static int parseModifier(const char *zMod, DateTime *p){
  int rc = 1;
  int n;
  double r;
  char z[30];
  for(n=0; n<sizeof(z)-1; n++){
    z[n] = tolower(zMod[n]);
  }
  z[n] = 0;
  switch( z[0] ){
















    case 'u': {
      /*
      **    unixepoch
      **
      ** Treat the current value of p->rJD as the number of
      ** seconds since 1970.  Convert to a real julian day number.
      */
      if( strcmp(z, "unixepoch")==0 && p->validJD ){
        p->rJD = p->rJD/86400.0 + 2440587.5;
        p->validYMD = 0;
        p->validHMS = 0;
        p->validTZ = 0;













        rc = 0;
      }
      break;
    }
    case 'w': {
      /*
      **    weekday N







|







 







>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>













>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>












>
>
>
>
>
>
>
>
>
>
>
>
>







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
..
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
...
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
...
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
** This file contains the C functions that implement date and time
** functions for SQLite.  
**
** There is only one exported symbol in this file - the function
** sqliteRegisterDateTimeFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: date.c,v 1.3 2003/12/23 16:22:18 drh Exp $
**
** NOTES:
**
** SQLite processes all times and dates as Julian Day numbers.  The
** dates and times are stored as the number of days since noon
** in Greenwich on November 24, 4714 B.C. according to the Gregorian
** calendar system.
................................................................................
**      Willmann-Bell, Inc
**      Richmond, Virginia (USA)
*/
#ifndef SQLITE_OMIT_DATETIME_FUNCS
#include <ctype.h>
#include <stdlib.h>
#include <assert.h>
#include <time.h>
#include "sqliteInt.h"
#include "os.h"

/*
** A structure for holding a single date and time.
*/
typedef struct DateTime DateTime;
................................................................................
  p->s -= s;
  p->h = s/3600;
  s -= p->h*3600;
  p->m = s/60;
  p->s += s - p->m*60;
  p->validHMS = 1;
}

/*
** Compute the difference (in days) between localtime and UTC (a.k.a. GMT)
** for the time value p where p is in UTC.
*/
static double localtimeOffset(DateTime *p){
  DateTime x, y;
  time_t t;
  struct tm *pTm;
  computeYMD(p);
  computeHMS(p);
  x = *p;
  if( x.Y<1971 || x.Y>=2038 ){
    x.Y = 2000;
    x.M = 1;
    x.D = 1;
    x.h = 0;
    x.m = 0;
    x.s = 0.0;
  } else {
    int s = x.s + 0.5;
    x.s = s;
  }
  x.tz = 0;
  x.validJD = 0;
  computeJD(&x);
  t = (x.rJD-2440587.5)*86400.0 + 0.5;
  sqliteOsEnterMutex();
  pTm = localtime(&t);
  y.Y = pTm->tm_year + 1900;
  y.M = pTm->tm_mon + 1;
  y.D = pTm->tm_mday;
  y.h = pTm->tm_hour;
  y.m = pTm->tm_min;
  y.s = pTm->tm_sec;
  sqliteOsLeaveMutex();
  y.validYMD = 1;
  y.validHMS = 1;
  y.validJD = 0;
  y.validTZ = 0;
  computeJD(&y);
  /* printf("x=%d-%02d-%02d %02d:%02d:%02d\n",x.Y,x.M,x.D,x.h,x.m,(int)x.s); */
  /* printf("y=%d-%02d-%02d %02d:%02d:%02d\n",y.Y,y.M,y.D,y.h,y.m,(int)y.s); */
  /* printf("diff=%.17g\n", y.rJD - x.rJD); */
  return y.rJD - x.rJD;
}

/*
** Process a modifier to a date-time stamp.  The modifiers are
** as follows:
**
**     NNN days
**     NNN hours
................................................................................
**     NNN years
**     start of month
**     start of year
**     start of week
**     start of day
**     weekday N
**     unixepoch
**     localtime
**     utc
**
** Return 0 on success and 1 if there is any kind of error.
*/
static int parseModifier(const char *zMod, DateTime *p){
  int rc = 1;
  int n;
  double r;
  char z[30];
  for(n=0; n<sizeof(z)-1; n++){
    z[n] = tolower(zMod[n]);
  }
  z[n] = 0;
  switch( z[0] ){
    case 'l': {
      /*    localtime
      **
      ** Assuming the current time value is UTC (a.k.a. GMT), shift it to
      ** show local time.
      */
      if( strcmp(z, "localtime")==0 ){
        computeJD(p);
        p->rJD += localtimeOffset(p);
        p->validYMD = 0;
        p->validHMS = 0;
        p->validTZ = 0;
        rc = 0;
      }
      break;
    }
    case 'u': {
      /*
      **    unixepoch
      **
      ** Treat the current value of p->rJD as the number of
      ** seconds since 1970.  Convert to a real julian day number.
      */
      if( strcmp(z, "unixepoch")==0 && p->validJD ){
        p->rJD = p->rJD/86400.0 + 2440587.5;
        p->validYMD = 0;
        p->validHMS = 0;
        p->validTZ = 0;
        rc = 0;
      }else if( strcmp(z, "utc")==0 ){
        double c1;
        computeJD(p);
        c1 = localtimeOffset(p);
        p->rJD -= c1;
        p->validYMD = 0;
        p->validHMS = 0;
        p->validTZ = 0;
        p->rJD += c1 - localtimeOffset(p);
        p->validYMD = 0;
        p->validHMS = 0;
        p->validTZ = 0;
        rc = 0;
      }
      break;
    }
    case 'w': {
      /*
      **    weekday N

Changes to test/date.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
115
116
117
118
119
120
121
122
































123
#    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.
#
# $Id: date.test,v 1.1 2003/11/01 01:53:54 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

proc datetest {tnum expr result} {
  do_test date-$tnum [subst {
    execsql "SELECT coalesce($expr,'NULL')"
................................................................................
set now [clock format [clock seconds] -format "%Y-%m-%d" -gmt 1]
datetest 4.1 {date('now')} $now

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}

































finish_test







|







 








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
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
#    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.
#
# $Id: date.test,v 1.2 2003/12/23 16:22:18 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

proc datetest {tnum expr result} {
  do_test date-$tnum [subst {
    execsql "SELECT coalesce($expr,'NULL')"
................................................................................
set now [clock format [clock seconds] -format "%Y-%m-%d" -gmt 1]
datetest 4.1 {date('now')} $now

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}

# 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.)
#
if {[clock format 0 -format %Z]=="EST"} {
  datetest 6.1 {datetime('2000-10-29 05:59:00','localtime')}\
      {2000-10-29 01:59:00}
  datetest 6.2 {datetime('2000-10-29 06:00:00','localtime')}\
      {2000-10-29 01:00:00}
  datetest 6.3 {datetime('2000-04-02 06:59:00','localtime')}\
      {2000-04-02 01:59:00}
  datetest 6.4 {datetime('2000-04-02 07:00:00','localtime')}\
      {2000-04-02 03:00:00}
  datetest 6.5 {datetime('2000-10-29 01:59:00','utc')} {2000-10-29 05:59:00}
  datetest 6.6 {datetime('2000-10-29 02:00:00','utc')} {2000-10-29 07:00:00}
  datetest 6.7 {datetime('2000-04-02 01:59:00','utc')} {2000-04-02 06:59:00}
  datetest 6.8 {datetime('2000-04-02 02:00:00','utc')} {2000-04-02 06:00:00}

  datetest 6.10 {datetime('2000-01-01 12:00:00','localtime')} \
      {2000-01-01 07:00:00}
  datetest 6.11 {datetime('1969-01-01 12:00:00','localtime')} \
      {1969-01-01 07:00:00}
  datetest 6.12 {datetime('2039-01-01 12:00:00','localtime')} \
      {2039-01-01 07:00:00}
  datetest 6.13 {datetime('2000-07-01 12:00:00','localtime')} \
      {2000-07-01 08:00:00}
  datetest 6.14 {datetime('1969-07-01 12:00:00','localtime')} \
      {1969-07-01 07:00:00}
  datetest 6.15 {datetime('2039-07-01 12:00:00','localtime')} \
      {2039-07-01 07:00:00}
}

finish_test