/ Check-in [768c9859]
Login

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

Overview
Comment:Extend the valid range of 'unixepoch' dates from JulianDay 0 through 9999-12-31.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 768c9859b21a3c88be084d6dd87acab4211b3a87
User & Date: drh 2016-12-02 17:08:27
References
2017-03-02
23:20 New ticket [6097cb92] Broken 'start of day' modifier on date/time functions. artifact: dc7e78fb user: drh
Context
2016-12-02
19:07
Simplify the date/time function logic for improved rebustness and also to decrease the size of the binary. check-in: 6e144735 user: drh tags: trunk
17:08
Extend the valid range of 'unixepoch' dates from JulianDay 0 through 9999-12-31. check-in: 768c9859 user: drh tags: trunk
02:19
Test cases for the improved LIKE/GLOB matcher. check-in: 0bdb7ae1 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/date.c.

66
67
68
69
70
71
72


73
74
75
76
77
78
79
80
81
82
...
219
220
221
222
223
224
225

226
227
228
229
230
231
232
...
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
...
338
339
340
341
342
343
344















345
346
347
348
349
350
351
...
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
...
428
429
430
431
432
433
434

435
436
437
438
439
440
441
...
567
568
569
570
571
572
573

574

575
576
577
578
579
580
581
...
628
629
630
631
632
633
634
635
636
637
638
639

640



641

642
643
644
645
646
647
648
...
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
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 (1) if Y,M,D are valid */
  char validHMS;      /* True (1) if h,m,s are valid */
  char validJD;       /* True (1) if iJD is valid */
  char validTZ;       /* True (1) if tz is valid */
  char tzSet;         /* Timezone was set explicitly */
  char isError;       /* An overflow has occurred */
};


/*
................................................................................
      }
      ms /= rScale;
    }
  }else{
    s = 0;
  }
  p->validJD = 0;

  p->validHMS = 1;
  p->h = h;
  p->m = m;
  p->s = s + ms;
  if( parseTimezone(zDate, p) ) return 1;
  p->validTZ = (p->tz!=0)?1:0;
  return 0;
................................................................................
    M = p->M;
    D = p->D;
  }else{
    Y = 2000;  /* If no YMD specified, assume 2000-Jan-01 */
    M = 1;
    D = 1;
  }
  if( Y<-4713 || Y>9999 ){
    datetimeError(p);
    return;
  }
  if( M<=2 ){
    Y--;
    M += 12;
  }
................................................................................
  if( p->iJD>0 ){
    p->validJD = 1;
    return 0;
  }else{
    return 1;
  }
}
















/*
** Attempt to parse the given string into a julian day number.  Return
** the number of errors.
**
** The following are acceptable forms for the input string:
**
................................................................................
  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) ){
    p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);
    p->validJD = 1;
    return 0;
  }
  return 1;
}

/*
** Return TRUE if the given julian day number is within range.
................................................................................
  p->s = s/1000.0;
  s = (int)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;
}

/*
** Compute both YMD and HMS
*/
static void computeYMD_HMS(DateTime *p){
................................................................................
  y.D = sLocal.tm_mday;
  y.h = sLocal.tm_hour;
  y.m = sLocal.tm_min;
  y.s = sLocal.tm_sec;
  y.validYMD = 1;
  y.validHMS = 1;
  y.validJD = 0;

  y.validTZ = 0;

  computeJD(&y);
  *pRc = SQLITE_OK;
  return y.iJD - x.iJD;
}
#endif /* SQLITE_OMIT_LOCALTIME */

/*
................................................................................
      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 + 43200)/86400 + 21086676*(i64)10000000;

        clearYMD_HMS_TZ(p);



        rc = 0;

      }
#ifndef SQLITE_OMIT_LOCALTIME
      else if( strcmp(z, "utc")==0 ){
        if( p->tzSet==0 ){
          sqlite3_int64 c1;
          computeJD(p);
          c1 = localtimeOffset(p, pCtx, &rc);
................................................................................
  int eType;
  memset(p, 0, sizeof(*p));
  if( argc==0 ){
    return setDateTimeToCurrent(context, p);
  }
  if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT
                   || eType==SQLITE_INTEGER ){
    double r = sqlite3_value_double(argv[0]);
    if( r>106751991167.0 || r<-106751991167.0 ) return 1;
    p->iJD = (sqlite3_int64)(r*86400000.0 + 0.5);
    p->validJD = 1;
  }else{
    z = sqlite3_value_text(argv[0]);
    if( !z || parseDateOrTime(context, (char*)z, p) ){
      return 1;
    }
  }
  for(i=1; i<argc; i++){







>
>


<







 







>







 







|







 







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







 







|
<







 







>







 







>

>







 







|


|
|
>
|
>
>
>
|
>







 







|
<
<
<







66
67
68
69
70
71
72
73
74
75
76

77
78
79
80
81
82
83
...
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
...
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
...
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
...
385
386
387
388
389
390
391
392

393
394
395
396
397
398
399
...
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
...
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
...
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
...
849
850
851
852
853
854
855
856



857
858
859
860
861
862
863
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 validJD;       /* True (1) if iJD is valid */
  char rawS;          /* Raw numeric value stored in s */
  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 */
};


/*
................................................................................
      }
      ms /= rScale;
    }
  }else{
    s = 0;
  }
  p->validJD = 0;
  p->rawS = 0;
  p->validHMS = 1;
  p->h = h;
  p->m = m;
  p->s = s + ms;
  if( parseTimezone(zDate, p) ) return 1;
  p->validTZ = (p->tz!=0)?1:0;
  return 0;
................................................................................
    M = p->M;
    D = p->D;
  }else{
    Y = 2000;  /* If no YMD specified, assume 2000-Jan-01 */
    M = 1;
    D = 1;
  }
  if( Y<-4713 || Y>9999 || p->rawS ){
    datetimeError(p);
    return;
  }
  if( M<=2 ){
    Y--;
    M += 12;
  }
................................................................................
  if( p->iJD>0 ){
    p->validJD = 1;
    return 0;
  }else{
    return 1;
  }
}

/*
** Input "r" is a numeric quantity which might be a julian day number,
** or the number of seconds since 1970.  If the value if r is within
** range of a julian day number, install it as such and set validJD.
** If the value is a valid unix timestamp, put it in p->s and set p->rawS.
*/
static void setRawDateNumber(DateTime *p, double r){
  p->s = r;
  p->rawS = 1;
  if( r>=0.0 && r<5373484.5 ){
    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.
**
** The following are acceptable forms for the input string:
**
................................................................................
  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;
}

/*
** Return TRUE if the given julian day number is within range.
................................................................................
  p->s = s/1000.0;
  s = (int)p->s;
  p->s -= s;
  p->h = s/3600;
  s -= p->h*3600;
  p->m = s/60;
  p->s += s - p->m*60;
  p->rawS = 0;
  p->validHMS = 1;
}

/*
** Compute both YMD and HMS
*/
static void computeYMD_HMS(DateTime *p){
................................................................................
  y.D = sLocal.tm_mday;
  y.h = sLocal.tm_hour;
  y.m = sLocal.tm_min;
  y.s = sLocal.tm_sec;
  y.validYMD = 1;
  y.validHMS = 1;
  y.validJD = 0;
  y.rawS = 0;
  y.validTZ = 0;
  y.isError = 0;
  computeJD(&y);
  *pRc = SQLITE_OK;
  return y.iJD - x.iJD;
}
#endif /* SQLITE_OMIT_LOCALTIME */

/*
................................................................................
      break;
    }
#endif
    case 'u': {
      /*
      **    unixepoch
      **
      ** Treat the current value of p->s as the number of
      ** seconds since 1970.  Convert to a real julian day number.
      */
      if( strcmp(z, "unixepoch")==0 && p->rawS ){
        double r = p->s*1000.0 + 210866760000000.0;
        if( r>=0.0 && r<464269060800000.0 ){
          clearYMD_HMS_TZ(p);
          p->iJD = (sqlite3_int64)r;
          p->validJD = 1;
          p->rawS = 0;
          rc = 0;
        }
      }
#ifndef SQLITE_OMIT_LOCALTIME
      else if( strcmp(z, "utc")==0 ){
        if( p->tzSet==0 ){
          sqlite3_int64 c1;
          computeJD(p);
          c1 = localtimeOffset(p, pCtx, &rc);
................................................................................
  int eType;
  memset(p, 0, sizeof(*p));
  if( argc==0 ){
    return setDateTimeToCurrent(context, p);
  }
  if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT
                   || eType==SQLITE_INTEGER ){
    setRawDateNumber(p, sqlite3_value_double(argv[0]));



  }else{
    z = sqlite3_value_text(argv[0]);
    if( !z || parseDateOrTime(context, (char*)z, p) ){
      return 1;
    }
  }
  for(i=1; i<argc; i++){