/ Check-in [2029a396]
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:Enhancements to the date and time functions so that they optionally support a 5-digit year. Not sure we want to include this in trunk. Saved in a branch for future reference.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | five-digit-year
Files: files | file ages | folders
SHA1: 2029a396dcdb928f79780ec80c808fda6e3285a1
User & Date: drh 2014-04-23 12:00:20
Context
2014-04-23
12:00
Enhancements to the date and time functions so that they optionally support a 5-digit year. Not sure we want to include this in trunk. Saved in a branch for future reference. Closed-Leaf check-in: 2029a396 user: drh tags: five-digit-year
2014-04-21
13:36
Comment tweaks on the test case for the [b75a9ca6b0] bug fix. check-in: 65d2544a user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/date.c.

225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
...
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
...
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
...
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
...
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772






773

774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
...
803
804
805
806
807
808
809
















810
811
812
813
814

815
816
817
818
819
820
821
822

823
824

825
826
827
828
829
830
831
832
833
...
846
847
848
849
850
851
852
853



854
855
856
857
858
859
860
861

862
863

864
865
866
867
868
869
870
871
  }
  if( M<=2 ){
    Y--;
    M += 12;
  }
  A = Y/100;
  B = 2 - A + (A/4);
  X1 = 36525*(Y+4716)/100;
  X2 = 306001*(M+1)/10000;
  p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000);
  p->validJD = 1;
  if( p->validHMS ){
    p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000);
    if( p->validTZ ){
      p->iJD -= p->tz*60000;
................................................................................
**
** Write the result into the DateTime structure and return 0
** on success and 1 if the input string is not a well-formed
** date.
*/
static int parseYyyyMmDd(const char *zDate, DateTime *p){
  int Y, M, D, neg;


  if( zDate[0]=='-' ){
    zDate++;
    neg = 1;
  }else{
    neg = 0;

  }
  if( getDigits(zDate,4,0,9999,'-',&Y,2,1,12,'-',&M,2,1,31,0,&D)!=3 ){




    return 1;
  }
  zDate += 10;
  while( sqlite3Isspace(*zDate) || 'T'==*(u8*)zDate ){ zDate++; }

  if( parseHhMmSs(zDate, p)==0 ){
    /* We got the time */
  }else if( *zDate==0 ){
    p->validHMS = 0;
  }else{
    return 1;
  }
................................................................................
    p->D = 1;
  }else{
    Z = (int)((p->iJD + 43200000)/86400000);
    A = (int)((Z - 1867216.25)/36524.25);
    A = Z + 1 + A - (A/4);
    B = A + 1524;
    C = (int)((B - 122.1)/365.25);
    D = (36525*C)/100;
    E = (int)((B-D)/30.6001);
    X1 = (int)(30.6001*E);
    p->D = B - D - X1;
    p->M = E<14 ? E-1 : E-13;
    p->Y = p->M>2 ? C - 4716 : C - 4715;
  }
  p->validYMD = 1;
................................................................................
        p->iJD += localtimeOffset(p, pCtx, &rc);
        clearYMD_HMS_TZ(p);
      }
      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 ){
        sqlite3_int64 c1;
        computeJD(p);
        c1 = localtimeOffset(p, pCtx, &rc);
        if( rc==SQLITE_OK ){
          p->iJD -= c1;
          clearYMD_HMS_TZ(p);
          p->iJD += c1 - localtimeOffset(p, pCtx, &rc);
................................................................................
*/
static int isDate(
  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 ){
    return setDateTimeToCurrent(context, p);
  }
  if( (eType = sqlite3_value_type(argv[0]))==SQLITE_FLOAT
                   || eType==SQLITE_INTEGER ){






    p->iJD = (sqlite3_int64)(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;
    }
  }
  for(i=1; i<argc; i++){
    z = sqlite3_value_text(argv[i]);
    if( z==0 || parseModifier(context, (char*)z, p) ) return 1;
  }
  return 0;
}


/*
................................................................................
){
  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

*/
static void datetimeFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  DateTime x;
  if( isDate(context, argc, argv, &x)==0 ){

    char zBuf[100];
    computeYMD_HMS(&x);

    sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d %02d:%02d:%02d",
                     x.Y, x.M, x.D, x.h, x.m, (int)(x.s));
    sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
  }
}

/*
**    time( TIMESTRING, MOD, MOD, ...)
**
................................................................................
    sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
  }
}

/*
**    date( TIMESTRING, MOD, MOD, ...)
**
** Return YYYY-MM-DD



*/
static void dateFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  DateTime x;
  if( isDate(context, argc, argv, &x)==0 ){

    char zBuf[100];
    computeYMD(&x);

    sqlite3_snprintf(sizeof(zBuf), zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D);
    sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
  }
}

/*
**    strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
**







|







 







>






>

|
>
>
>
>


<
<
>







 







|







 







<
<
<
<
<
<
<
<
<
<
<

|







 







|








>
>
>
>
>
>
|
>







|
|







 







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




|
>








>


>
|
|







 







|
>
>
>








>


>
|







225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
...
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
...
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
...
578
579
580
581
582
583
584











585
586
587
588
589
590
591
592
593
...
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
...
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
...
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
  }
  if( M<=2 ){
    Y--;
    M += 12;
  }
  A = Y/100;
  B = 2 - A + (A/4);
  X1 = 365*(Y+4716) + 25*(Y+4716)/100;
  X2 = 306001*(M+1)/10000;
  p->iJD = (sqlite3_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000);
  p->validJD = 1;
  if( p->validHMS ){
    p->iJD += p->h*3600000 + p->m*60000 + (sqlite3_int64)(p->s*1000);
    if( p->validTZ ){
      p->iJD -= p->tz*60000;
................................................................................
**
** Write the result into the DateTime structure and return 0
** on success and 1 if the input string is not a well-formed
** date.
*/
static int parseYyyyMmDd(const char *zDate, DateTime *p){
  int Y, M, D, neg;
  char c;

  if( zDate[0]=='-' ){
    zDate++;
    neg = 1;
  }else{
    neg = 0;
    if( zDate[0]=='+' ) zDate++;
  }
  if( getDigits(zDate,4,0,9999,'-',&Y,2,1,12,'-',&M,2,1,31,0,&D)==3 ){
    zDate += 10;
  }else if( getDigits(zDate,5,0,99999,'-',&Y,2,1,12,'-',&M,2,1,31,0,&D)==3 ){
    zDate += 11;
  }else{
    return 1;
  }


  while( (c = zDate[0])=='T' || c=='t' || sqlite3Isspace(c) ){ zDate++; }
  if( parseHhMmSs(zDate, p)==0 ){
    /* We got the time */
  }else if( *zDate==0 ){
    p->validHMS = 0;
  }else{
    return 1;
  }
................................................................................
    p->D = 1;
  }else{
    Z = (int)((p->iJD + 43200000)/86400000);
    A = (int)((Z - 1867216.25)/36524.25);
    A = Z + 1 + A - (A/4);
    B = A + 1524;
    C = (int)((B - 122.1)/365.25);
    D = 365*C + (25*C)/100;
    E = (int)((B-D)/30.6001);
    X1 = (int)(30.6001*E);
    p->D = B - D - X1;
    p->M = E<14 ? E-1 : E-13;
    p->Y = p->M>2 ? C - 4716 : C - 4715;
  }
  p->validYMD = 1;
................................................................................
        p->iJD += localtimeOffset(p, pCtx, &rc);
        clearYMD_HMS_TZ(p);
      }
      break;
    }
#endif
    case 'u': {











#ifndef SQLITE_OMIT_LOCALTIME
      if( strcmp(z, "utc")==0 ){
        sqlite3_int64 c1;
        computeJD(p);
        c1 = localtimeOffset(p, pCtx, &rc);
        if( rc==SQLITE_OK ){
          p->iJD -= c1;
          clearYMD_HMS_TZ(p);
          p->iJD += c1 - localtimeOffset(p, pCtx, &rc);
................................................................................
*/
static int isDate(
  sqlite3_context *context, 
  int argc, 
  sqlite3_value **argv, 
  DateTime *p
){
  int i = 1;
  const unsigned char *z;
  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 ){
    if( argc>=2 && sqlite3_value_type(argv[1])==SQLITE_TEXT
       && strcmp((const char*)sqlite3_value_text(argv[1]),"unixepoch")==0 ){
      i = 2;
      p->iJD = (sqlite3_int64)(sqlite3_value_double(argv[0])*1000.0) +
                21086676*(i64)10000000;
    }else{
      p->iJD = (sqlite3_int64)(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;
    }
  }
  while( i<argc ){
    z = sqlite3_value_text(argv[i++]);
    if( z==0 || parseModifier(context, (char*)z, p) ) return 1;
  }
  return 0;
}


/*
................................................................................
){
  DateTime x;
  if( isDate(context, argc, argv, &x)==0 ){
    computeJD(&x);
    sqlite3_result_double(context, x.iJD/86400000.0);
  }
}

/*
** Render an ISO-8601 year value into the given buffer.  Return the
** number of characters.
**
** Years are rendered differently depending on magnitude:
**
**      0..9999        YYYY
**  10000..99999     +YYYYY
**  -9999..-1         -YYYY
** -99999..-10000    -YYYYY
*/
static int renderYear(char *zBuf, int Y){
  sqlite3_snprintf(10, zBuf, Y<0 ? "%05d" : Y>=10000 ? "%+05d" : "%04d", Y);
  return sqlite3Strlen30(zBuf);
}

/*
**    datetime( TIMESTRING, MOD, MOD, ...)
**
** Return YYYY-MM-DD HH:MM:SS.  The YYYY might be +YYYYY or -YYYY or -YYYYY
** depending on the year.
*/
static void datetimeFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  DateTime x;
  if( isDate(context, argc, argv, &x)==0 ){
    int i;
    char zBuf[100];
    computeYMD_HMS(&x);
    i = renderYear(zBuf, x.Y);
    sqlite3_snprintf(sizeof(zBuf)-i, zBuf+i, "-%02d-%02d %02d:%02d:%02d",
                     x.M, x.D, x.h, x.m, (int)(x.s));
    sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
  }
}

/*
**    time( TIMESTRING, MOD, MOD, ...)
**
................................................................................
    sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
  }
}

/*
**    date( TIMESTRING, MOD, MOD, ...)
**
** Return   YYYY-MM-DD
** or     +YYYYY-MM-DD
** or      -YYYY-MM-DD
** or     -YYYYY-MM-DD
*/
static void dateFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  DateTime x;
  if( isDate(context, argc, argv, &x)==0 ){
    int i;
    char zBuf[100];
    computeYMD(&x);
    i = renderYear(zBuf, x.Y);
    sqlite3_snprintf(sizeof(zBuf)-i, zBuf+i, "-%02d-%02d", x.M, x.D);
    sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
  }
}

/*
**    strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
**