/ Check-in [ed35f8a9]
Login

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

Overview
Comment:Convert the date/time functions to work with milliseconds since the julian epoch internally (instead of days since the epoch) in order to avoid problems with floating-point roundoff error. The interface is unchanged. (CVS 5215)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ed35f8a98323dadb64b423615287fb24ea262ffb
User & Date: drh 2008-06-12 16:35:38
Context
2008-06-12
18:05
Fix a typo in the date/time function tests. Add additional cases to the zeroblob tests to make sure sqlite3_bind_zeroblob() does not use excess memory. (CVS 5216) check-in: c1006fb1 user: drh tags: trunk
16:35
Convert the date/time functions to work with milliseconds since the julian epoch internally (instead of days since the epoch) in order to avoid problems with floating-point roundoff error. The interface is unchanged. (CVS 5215) check-in: ed35f8a9 user: drh tags: trunk
14:42
Add another test to incrblob2.test. This test failed to reveal any new bugs. (CVS 5214) check-in: 20d8ea45 user: danielk1977 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
..
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
...
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
...
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
...
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356

357

358
359
360
361
362
363
364
...
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
...
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
...
421
422
423
424
425
426
427

428
429
430
431
432
433
434
435
436
437
438
...
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
...
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
...
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
591
...
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
...
711
712
713
714
715
716
717

718
719
720
721
722
723
724
725
726
727
728
729
730
...
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
...
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
** 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
** sqlite3RegisterDateTimeFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: date.c,v 1.82 2008/06/12 13:50:00 drh Exp $
**
** 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. 
**
** 1970-01-01 00:00:00 is JD 2440587.5
................................................................................
#endif

/*
** A structure for holding a single date and time.
*/
typedef struct DateTime DateTime;
struct DateTime {
  double rJD;      /* The julian day number */
  int Y, M, D;     /* Year, month, and day */
  int h, m;        /* Hour and minutes */
  int tz;          /* Timezone offset in minutes */
  double s;        /* Seconds */
  char validYMD;   /* True if Y,M,D are valid */
  char validHMS;   /* True if h,m,s are valid */
  char validJD;    /* True if rJD is valid */
  char validTZ;    /* True if tz is valid */
};


/*
** Convert zDate into one or more integers.  Additional arguments
** come in groups of 5 as follows:
**
................................................................................
    Y--;
    M += 12;
  }
  A = Y/100;
  B = 2 - A + (A/4);
  X1 = 365.25*(Y+4716);
  X2 = 30.6001*(M+1);
  p->rJD = X1 + X2 + D + B - 1524.5;
  p->validJD = 1;
  if( p->validHMS ){
    p->rJD += (p->h*3600.0 + p->m*60.0 + p->s)/86400.0;
    if( p->validTZ ){
      p->rJD -= p->tz*60/86400.0;
      p->validYMD = 0;
      p->validHMS = 0;
      p->validTZ = 0;
    }
  }
}

................................................................................
/*
** Set the time to the current time reported by the VFS
*/
static void setDateTimeToCurrent(sqlite3_context *context, DateTime *p){
  double r;
  sqlite3 *db = sqlite3_context_db_handle(context);
  sqlite3OsCurrentTime(db->pVfs, &r);
  p->rJD = r;
  p->validJD = 1;
}

/*
** Attempt to parse the given string into a Julian Day Number.  Return
** the number of errors.
**
................................................................................
** as there is a year and date.
*/
static int parseDateOrTime(
  sqlite3_context *context, 
  const char *zDate, 
  DateTime *p
){
  memset(p, 0, sizeof(*p));
  if( parseYyyyMmDd(zDate,p)==0 ){
    return 0;
  }else if( parseHhMmSs(zDate, p)==0 ){
    return 0;
  }else if( sqlite3StrICmp(zDate,"now")==0){
    setDateTimeToCurrent(context, p);
    return 0;
  }else if( sqlite3IsNumber(zDate, 0, SQLITE_UTF8) ){

    getValue(zDate, &p->rJD);

    p->validJD = 1;
    return 0;
  }
  return 1;
}

/*
................................................................................
  int Z, A, B, C, D, E, X1;
  if( p->validYMD ) return;
  if( !p->validJD ){
    p->Y = 2000;
    p->M = 1;
    p->D = 1;
  }else{
    Z = p->rJD + 0.5;
    A = (Z - 1867216.25)/36524.25;
    A = Z + 1 + A - (A/4);
    B = A + 1524;
    C = (B - 122.1)/365.25;
    D = 365.25*C;
    E = (B-D)/30.6001;
    X1 = 30.6001*E;
................................................................................
  p->validYMD = 1;
}

/*
** Compute the Hour, Minute, and Seconds from the julian day number.
*/
static void computeHMS(DateTime *p){
  int Z, s;
  if( p->validHMS ) return;
  computeJD(p);
  Z = p->rJD + 0.5;
  s = (p->rJD + 0.5 - Z)*86400000.0 + 0.5;
  p->s = 0.001*s;
  s = p->s;
  p->s -= s;
  p->h = s/3600;
  s -= p->h*3600;
  p->m = s/60;
  p->s += s - p->m*60;
  p->validHMS = 1;
................................................................................
  p->validYMD = 0;
  p->validHMS = 0;
  p->validTZ = 0;
}

#ifndef SQLITE_OMIT_LOCALTIME
/*

** 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;
  x = *p;
  computeYMD_HMS(&x);
  if( x.Y<1971 || x.Y>=2038 ){
    x.Y = 2000;
    x.M = 1;
................................................................................
  } 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;
#ifdef HAVE_LOCALTIME_R
  {
    struct tm sLocal;
    localtime_r(&t, &sLocal);
    y.Y = sLocal.tm_year + 1900;
    y.M = sLocal.tm_mon + 1;
    y.D = sLocal.tm_mday;
................................................................................
  }
#endif
  y.validYMD = 1;
  y.validHMS = 1;
  y.validJD = 0;
  y.validTZ = 0;
  computeJD(&y);
  return y.rJD - x.rJD;
}
#endif /* SQLITE_OMIT_LOCALTIME */

/*
** Process a modifier to a date-time stamp.  The modifiers are
** as follows:
**
................................................................................
      /*    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);
        clearYMD_HMS_TZ(p);
        rc = 0;
      }
      break;
    }
#endif
    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;
        clearYMD_HMS_TZ(p);
        rc = 0;
      }else if( strcmp(z, "utc")==0 ){
        double c1;
        computeJD(p);
        c1 = localtimeOffset(p);
        p->rJD -= c1;
        clearYMD_HMS_TZ(p);
        p->rJD += c1 - localtimeOffset(p);
        rc = 0;
      }
      break;
    }
    case 'w': {
      /*
      **    weekday N
................................................................................
      **
      ** Move the date to the same time on the next occurrence of
      ** weekday N where 0==Sunday, 1==Monday, and so forth.  If the
      ** date is already on the appropriate weekday, this is a no-op.
      */
      if( strncmp(z, "weekday ", 8)==0 && getValue(&z[8],&r)>0
                 && (n=r)==r && n>=0 && r<7 ){
        int Z;
        computeYMD_HMS(p);
        p->validTZ = 0;
        p->validJD = 0;
        computeJD(p);
        Z = p->rJD + 1.5;
        Z %= 7;
        if( Z>n ) Z -= 7;
        p->rJD += n - Z;

        clearYMD_HMS_TZ(p);
        rc = 0;
      }
      break;
    }
    case 's': {
      /*
................................................................................
        /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
        ** specified number of hours, minutes, seconds, and fractional seconds
        ** to the time.  The ".FFF" may be omitted.  The ":SS.FFF" may be
        ** omitted.
        */
        const char *z2 = z;
        DateTime tx;
        int day;
        if( !isdigit(*(u8*)z2) ) z2++;
        memset(&tx, 0, sizeof(tx));
        if( parseHhMmSs(z2, &tx) ) break;
        computeJD(&tx);
        tx.rJD -= 0.5;
        day = (int)tx.rJD;
        tx.rJD -= day;
        if( z[0]=='-' ) tx.rJD = -tx.rJD;
        computeJD(p);
        clearYMD_HMS_TZ(p);
        p->rJD += tx.rJD;
        rc = 0;
        break;
      }
      z += n;
      while( isspace(*(u8*)z) ) z++;
      n = strlen(z);
      if( n>10 || n<3 ) break;
      if( z[n-1]=='s' ){ z[n-1] = 0; n--; }
      computeJD(p);
      rc = 0;
      if( n==3 && strcmp(z,"day")==0 ){
        p->rJD += r;
      }else if( n==4 && strcmp(z,"hour")==0 ){
        p->rJD += r/24.0;
      }else if( n==6 && strcmp(z,"minute")==0 ){
        p->rJD += r/(24.0*60.0);
      }else if( n==6 && strcmp(z,"second")==0 ){
        p->rJD += r/(24.0*60.0*60.0);
      }else if( n==5 && strcmp(z,"month")==0 ){
        int x, y;
        computeYMD_HMS(p);
        p->M += r;
        x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
        p->Y += x;
        p->M -= x*12;
        p->validJD = 0;
        computeJD(p);
        y = r;
        if( y!=r ){
          p->rJD += (r - y)*30.0;
        }
      }else if( n==4 && strcmp(z,"year")==0 ){
        computeYMD_HMS(p);
        p->Y += r;
        p->validJD = 0;
        computeJD(p);
      }else{
................................................................................
  sqlite3_context *context, 
  int argc, 
  sqlite3_value **argv, 
  DateTime *p
){
  int i;
  const unsigned char *z;

  memset(p, 0, sizeof(*p));
  if( argc==0 ){
    setDateTimeToCurrent(context, p);
  }else if( sqlite3_value_type(argv[0])==SQLITE_FLOAT ){
    memset(p, 0, sizeof(*p));
    p->rJD = sqlite3_value_double(argv[0]);
    p->validJD = 1;
  }else{
    z = sqlite3_value_text(argv[0]);
    if( !z || parseDateOrTime(context, (char*)z, p) ){
      return 1;
    }
  }
................................................................................
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  DateTime x;
  if( isDate(context, argc, argv, &x)==0 ){
    computeJD(&x);
    sqlite3_result_double(context, x.rJD);
  }
}

/*
**    datetime( TIMESTRING, MOD, MOD, ...)
**
** Return YYYY-MM-DD HH:MM:SS
................................................................................
        case 'j': {
          int nDay;             /* Number of days since 1st day of year */
          DateTime y = x;
          y.validJD = 0;
          y.M = 1;
          y.D = 1;
          computeJD(&y);
          nDay = x.rJD - y.rJD + 0.5;
          if( zFmt[i]=='W' ){
            int wd;   /* 0=Monday, 1=Tuesday, ... 6=Sunday */
            wd = ((int)(x.rJD+0.5)) % 7;
            sqlite3_snprintf(3, &z[j],"%02d",(nDay+7-wd)/7);
            j += 2;
          }else{
            sqlite3_snprintf(4, &z[j],"%03d",nDay+1);
            j += 3;
          }
          break;
        }
        case 'J': {
          sqlite3_snprintf(20, &z[j],"%.16g",x.rJD);
          j+=strlen(&z[j]);
          break;
        }
        case 'm':  sqlite3_snprintf(3, &z[j],"%02d",x.M); j+=2; break;
        case 'M':  sqlite3_snprintf(3, &z[j],"%02d",x.m); j+=2; break;
        case 's': {
          sqlite3_snprintf(30,&z[j],"%d",
                           (int)((x.rJD-2440587.5)*86400.0 + 0.5));
          j += strlen(&z[j]);
          break;
        }
        case 'S':  sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break;
        case 'w':  z[j++] = (((int)(x.rJD+1.5)) % 7) + '0'; break;
        case 'Y':  sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=strlen(&z[j]);break;
        default:   z[j++] = '%'; break;
      }
    }
  }
  z[j] = 0;
  sqlite3_result_text(context, z, -1,







|







 







|
|
|
|
|
|
|
|
|







 







|


|

|







 







|







 







<








>
|
>







 







|







 







|


|
<
|







 







>
|


|







 







|







 







|







 







|










|



|






|

|







 







|




|
<

<
>







 







|




|
|
|
|


|











|

|

|

|











|







 







>



|
|
|







 







|







 







|


|









|







|




|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
..
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
...
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
...
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
...
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
...
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
...
388
389
390
391
392
393
394
395
396
397
398

399
400
401
402
403
404
405
406
...
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
...
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
...
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
...
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
591
...
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
...
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
...
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
...
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
** 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
** sqlite3RegisterDateTimeFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: date.c,v 1.83 2008/06/12 16:35:38 drh Exp $
**
** 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. 
**
** 1970-01-01 00:00:00 is JD 2440587.5
................................................................................
#endif

/*
** A structure for holding a single date and time.
*/
typedef struct DateTime DateTime;
struct DateTime {
  sqlite3_int64 iJD; /* The julian day number times 86400000 */
  int Y, M, D;       /* Year, month, and day */
  int h, m;          /* Hour and minutes */
  int tz;            /* Timezone offset in minutes */
  double s;          /* Seconds */
  char validYMD;     /* True if Y,M,D are valid */
  char validHMS;     /* True if h,m,s are valid */
  char validJD;      /* True if iJD is valid */
  char validTZ;      /* True if tz is valid */
};


/*
** Convert zDate into one or more integers.  Additional arguments
** come in groups of 5 as follows:
**
................................................................................
    Y--;
    M += 12;
  }
  A = Y/100;
  B = 2 - A + (A/4);
  X1 = 365.25*(Y+4716);
  X2 = 30.6001*(M+1);
  p->iJD = (X1 + X2 + D + B - 1524.5)*86400000;
  p->validJD = 1;
  if( p->validHMS ){
    p->iJD += p->h*3600000 + p->m*60000 + p->s*1000;
    if( p->validTZ ){
      p->iJD -= p->tz*60000;
      p->validYMD = 0;
      p->validHMS = 0;
      p->validTZ = 0;
    }
  }
}

................................................................................
/*
** Set the time to the current time reported by the VFS
*/
static void setDateTimeToCurrent(sqlite3_context *context, DateTime *p){
  double r;
  sqlite3 *db = sqlite3_context_db_handle(context);
  sqlite3OsCurrentTime(db->pVfs, &r);
  p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);
  p->validJD = 1;
}

/*
** Attempt to parse the given string into a Julian Day Number.  Return
** the number of errors.
**
................................................................................
** as there is a year and date.
*/
static int parseDateOrTime(
  sqlite3_context *context, 
  const char *zDate, 
  DateTime *p
){

  if( parseYyyyMmDd(zDate,p)==0 ){
    return 0;
  }else if( parseHhMmSs(zDate, p)==0 ){
    return 0;
  }else if( sqlite3StrICmp(zDate,"now")==0){
    setDateTimeToCurrent(context, p);
    return 0;
  }else if( sqlite3IsNumber(zDate, 0, SQLITE_UTF8) ){
    double r;
    getValue(zDate, &r);
    p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);
    p->validJD = 1;
    return 0;
  }
  return 1;
}

/*
................................................................................
  int Z, A, B, C, D, E, X1;
  if( p->validYMD ) return;
  if( !p->validJD ){
    p->Y = 2000;
    p->M = 1;
    p->D = 1;
  }else{
    Z = (p->iJD + 43200000)/86400000;
    A = (Z - 1867216.25)/36524.25;
    A = Z + 1 + A - (A/4);
    B = A + 1524;
    C = (B - 122.1)/365.25;
    D = 365.25*C;
    E = (B-D)/30.6001;
    X1 = 30.6001*E;
................................................................................
  p->validYMD = 1;
}

/*
** Compute the Hour, Minute, and Seconds from the julian day number.
*/
static void computeHMS(DateTime *p){
  int s;
  if( p->validHMS ) return;
  computeJD(p);
  s = (p->iJD + 43200000) % 86400000;

  p->s = s/1000.0;
  s = p->s;
  p->s -= s;
  p->h = s/3600;
  s -= p->h*3600;
  p->m = s/60;
  p->s += s - p->m*60;
  p->validHMS = 1;
................................................................................
  p->validYMD = 0;
  p->validHMS = 0;
  p->validTZ = 0;
}

#ifndef SQLITE_OMIT_LOCALTIME
/*
** Compute the difference (in milliseconds)
** between localtime and UTC (a.k.a. GMT)
** for the time value p where p is in UTC.
*/
static int localtimeOffset(DateTime *p){
  DateTime x, y;
  time_t t;
  x = *p;
  computeYMD_HMS(&x);
  if( x.Y<1971 || x.Y>=2038 ){
    x.Y = 2000;
    x.M = 1;
................................................................................
  } else {
    int s = x.s + 0.5;
    x.s = s;
  }
  x.tz = 0;
  x.validJD = 0;
  computeJD(&x);
  t = x.iJD/1000 - 2440587.5*86400.0;
#ifdef HAVE_LOCALTIME_R
  {
    struct tm sLocal;
    localtime_r(&t, &sLocal);
    y.Y = sLocal.tm_year + 1900;
    y.M = sLocal.tm_mon + 1;
    y.D = sLocal.tm_mday;
................................................................................
  }
#endif
  y.validYMD = 1;
  y.validHMS = 1;
  y.validJD = 0;
  y.validTZ = 0;
  computeJD(&y);
  return y.iJD - x.iJD;
}
#endif /* SQLITE_OMIT_LOCALTIME */

/*
** Process a modifier to a date-time stamp.  The modifiers are
** as follows:
**
................................................................................
      /*    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->iJD += localtimeOffset(p);
        clearYMD_HMS_TZ(p);
        rc = 0;
      }
      break;
    }
#endif
    case 'u': {
      /*
      **    unixepoch
      **
      ** Treat the current value of p->iJD as the number of
      ** seconds since 1970.  Convert to a real julian day number.
      */
      if( strcmp(z, "unixepoch")==0 && p->validJD ){
        p->iJD = p->iJD/86400.0 + 2440587.5*86400000.0;
        clearYMD_HMS_TZ(p);
        rc = 0;
      }else if( strcmp(z, "utc")==0 ){
        double c1;
        computeJD(p);
        c1 = localtimeOffset(p);
        p->iJD -= c1;
        clearYMD_HMS_TZ(p);
        p->iJD += c1 - localtimeOffset(p);
        rc = 0;
      }
      break;
    }
    case 'w': {
      /*
      **    weekday N
................................................................................
      **
      ** Move the date to the same time on the next occurrence of
      ** weekday N where 0==Sunday, 1==Monday, and so forth.  If the
      ** date is already on the appropriate weekday, this is a no-op.
      */
      if( strncmp(z, "weekday ", 8)==0 && getValue(&z[8],&r)>0
                 && (n=r)==r && n>=0 && r<7 ){
        sqlite3_int64 Z;
        computeYMD_HMS(p);
        p->validTZ = 0;
        p->validJD = 0;
        computeJD(p);
        Z = ((p->iJD + 129600000)/86400000) % 7;

        if( Z>n ) Z -= 7;

        p->iJD += (n - Z)*86400000;
        clearYMD_HMS_TZ(p);
        rc = 0;
      }
      break;
    }
    case 's': {
      /*
................................................................................
        /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
        ** specified number of hours, minutes, seconds, and fractional seconds
        ** to the time.  The ".FFF" may be omitted.  The ":SS.FFF" may be
        ** omitted.
        */
        const char *z2 = z;
        DateTime tx;
        sqlite3_int64 day;
        if( !isdigit(*(u8*)z2) ) z2++;
        memset(&tx, 0, sizeof(tx));
        if( parseHhMmSs(z2, &tx) ) break;
        computeJD(&tx);
        tx.iJD -= 43200000;
        day = tx.iJD/86400000;
        tx.iJD -= day*86400000;
        if( z[0]=='-' ) tx.iJD = -tx.iJD;
        computeJD(p);
        clearYMD_HMS_TZ(p);
        p->iJD += tx.iJD;
        rc = 0;
        break;
      }
      z += n;
      while( isspace(*(u8*)z) ) z++;
      n = strlen(z);
      if( n>10 || n<3 ) break;
      if( z[n-1]=='s' ){ z[n-1] = 0; n--; }
      computeJD(p);
      rc = 0;
      if( n==3 && strcmp(z,"day")==0 ){
        p->iJD += r*86400000.0 + 0.5;
      }else if( n==4 && strcmp(z,"hour")==0 ){
        p->iJD += r*(86400000.0/24.0) + 0.5;
      }else if( n==6 && strcmp(z,"minute")==0 ){
        p->iJD += r*(86400000.0/(24.0*60.0)) + 0.5;
      }else if( n==6 && strcmp(z,"second")==0 ){
        p->iJD += r*(86400000.0/(24.0*60.0*60.0)) + 0.5;
      }else if( n==5 && strcmp(z,"month")==0 ){
        int x, y;
        computeYMD_HMS(p);
        p->M += r;
        x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
        p->Y += x;
        p->M -= x*12;
        p->validJD = 0;
        computeJD(p);
        y = r;
        if( y!=r ){
          p->iJD += (r - y)*30.0*86400000.0 + 0.5;
        }
      }else if( n==4 && strcmp(z,"year")==0 ){
        computeYMD_HMS(p);
        p->Y += r;
        p->validJD = 0;
        computeJD(p);
      }else{
................................................................................
  sqlite3_context *context, 
  int argc, 
  sqlite3_value **argv, 
  DateTime *p
){
  int i;
  const unsigned char *z;
  int eType;
  memset(p, 0, sizeof(*p));
  if( argc==0 ){
    setDateTimeToCurrent(context, p);
  }else if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT
                   || eType==SQLITE_INTEGER ){
    p->iJD = sqlite3_value_double(argv[0])*86400000.0 + 0.5;
    p->validJD = 1;
  }else{
    z = sqlite3_value_text(argv[0]);
    if( !z || parseDateOrTime(context, (char*)z, p) ){
      return 1;
    }
  }
................................................................................
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  DateTime x;
  if( isDate(context, argc, argv, &x)==0 ){
    computeJD(&x);
    sqlite3_result_double(context, x.iJD/86400000.0);
  }
}

/*
**    datetime( TIMESTRING, MOD, MOD, ...)
**
** Return YYYY-MM-DD HH:MM:SS
................................................................................
        case 'j': {
          int nDay;             /* Number of days since 1st day of year */
          DateTime y = x;
          y.validJD = 0;
          y.M = 1;
          y.D = 1;
          computeJD(&y);
          nDay = (x.iJD - y.iJD)/86400000.0 + 0.5;
          if( zFmt[i]=='W' ){
            int wd;   /* 0=Monday, 1=Tuesday, ... 6=Sunday */
            wd = ((x.iJD+43200000)/86400000) % 7;
            sqlite3_snprintf(3, &z[j],"%02d",(nDay+7-wd)/7);
            j += 2;
          }else{
            sqlite3_snprintf(4, &z[j],"%03d",nDay+1);
            j += 3;
          }
          break;
        }
        case 'J': {
          sqlite3_snprintf(20, &z[j],"%.16g",x.iJD/86400000.0);
          j+=strlen(&z[j]);
          break;
        }
        case 'm':  sqlite3_snprintf(3, &z[j],"%02d",x.M); j+=2; break;
        case 'M':  sqlite3_snprintf(3, &z[j],"%02d",x.m); j+=2; break;
        case 's': {
          sqlite3_snprintf(30,&z[j],"%d",
                           (int)(x.iJD/1000.0 - 210866760000.0));
          j += strlen(&z[j]);
          break;
        }
        case 'S':  sqlite3_snprintf(3,&z[j],"%02d",(int)x.s); j+=2; break;
        case 'w':  z[j++] = (((x.iJD+129600000)/86400000) % 7) + '0'; break;
        case 'Y':  sqlite3_snprintf(5,&z[j],"%04d",x.Y); j+=strlen(&z[j]);break;
        default:   z[j++] = '%'; break;
      }
    }
  }
  z[j] = 0;
  sqlite3_result_text(context, z, -1,

Changes to test/date.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
447
448
449
450
451
452
453
454
455




456
457
458




459
460
461
462
463
464





465
466



467









468
#    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.28 2008/06/12 05:16:15 shane Exp $

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

# Skip this whole file if date and time functions are omitted
# at compile-time
#
................................................................................
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}

# Test for issues reported by BareFeet <list.sql@tandb.com.au> on mailing list
# SELECT datetime(julianday('2008-06-12','utc'), 'localtime') should give 2008-06-12 00:00:00




do_test date-13.8 {
  execsql {
    SELECT datetime(julianday('2008-06-12','utc'), 'localtime')




  }
} {{2008-06-12 00:00:00}}
# SELECT date(julianday('2008-06-12', 'utc'), 'localtime') should give 2008-06-12
do_test date-13.9 {
  execsql {
    SELECT date(julianday('2008-06-12','utc'), 'localtime')





  }
} {2008-06-12}













finish_test







|







 







|
|
>
>
>
>
|

<
>
>
>
>

<
<
<
<
<
>
>
>
>
>
|
<
>
>
>
|
>
>
>
>
>
>
>
>
>

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
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
#    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.29 2008/06/12 16:35:39 drh Exp $

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

# Skip this whole file if date and time functions are omitted
# at compile-time
#
................................................................................
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}

# 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.
#
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 %2x $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}
}
finish_test