SQLite4
Check-in [fa869b0982]
Not logged in

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

Overview
Comment:Remove the use of type 'double' from date.c.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: fa869b0982959bfdcf71b2c0c2b5be37de4bc9f2
User & Date: dan 2013-06-03 19:25:21
Context
2013-06-04
15:16
Fix a few warnings relating to unused variables and functions. check-in: 424d39678b user: dan tags: trunk
2013-06-03
19:25
Remove the use of type 'double' from date.c. check-in: fa869b0982 user: dan tags: trunk
15:23
Merge overflow and rounding fixes for sqlite4_num_to_int64(). check-in: e8db1e81a4 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to src/date.c.

56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
...
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
...
230
231
232
233
234
235
236
237



238
239
240
241
242
243
244
...
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
...
368
369
370
371
372
373
374
375
376
377



378
379
380
381
382
383
384
385
386
387
388
...
511
512
513
514
515
516
517










518
519
520
521
522
523
524
...
536
537
538
539
540
541
542
543




544
545
546
547
548
549
550
551
552
553
...
596
597
598
599
600
601
602
603
604
605
606










607
608
609
610
611
612
613
614
615
616
617
618
619
620
...
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
...
652
653
654
655
656
657
658
659

660
661


662
663
664

665
666
667
668
669
670
671
...
681
682
683
684
685
686
687

688
689
690
691
692
693
694
695

696
697
698
699
700
701
702
703
704


705

706
707
708
709
710
711
712
713
714
715
716
717
718


719
720
721
722
723
724



725
726
727
728
729
730
731
...
747
748
749
750
751
752
753


754
755
756
757
758
759
760
761
762
763

764
765
766
767
768
769
770
...
787
788
789
790
791
792
793

794

795
796
797


798
799
800
801
802
803
804
...
809
810
811
812
813
814
815
816

817
818
819
820
821
822
823
...
828
829
830
831
832
833
834
835


836
837
838
839
840
841
842
...
944
945
946
947
948
949
950
951
952


953
954
955
956
957
958
959
960
...
981
982
983
984
985
986
987
988



989
990
991
992
993
994
995
*/
typedef struct DateTime DateTime;
struct DateTime {
  sqlite4_uint64 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 */
};


................................................................................
** The HH, MM, and SS must each be exactly 2 digits.  The
** fractional seconds FFFF can be one or more digits.
**
** Return 1 if there is a parsing error and 0 on success.
*/
static int parseHhMmSs(const char *zDate, DateTime *p){
  int h, m, s;
  double ms = 0.0;
  if( getDigits(zDate, 2, 0, 24, ':', &h, 2, 0, 59, 0, &m)!=2 ){
    return 1;
  }
  zDate += 5;
  if( *zDate==':' ){
    zDate++;
    if( getDigits(zDate, 2, 0, 59, 0, &s)!=1 ){
      return 1;
    }
    zDate += 2;

    if( *zDate=='.' && sqlite4Isdigit(zDate[1]) ){
      double rScale = 1.0;
      zDate++;

      while( sqlite4Isdigit(*zDate) ){
        ms = ms*10.0 + *zDate - '0';
        rScale *= 10.0;
        zDate++;
      }
      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;
}

/*
** Convert from YYYY-MM-DD HH:MM:SS to julian day.  We always assume
................................................................................
  A = Y/100;
  B = 2 - A + (A/4);
  X1 = 36525*(Y+4716)/100;
  X2 = 306001*(M+1)/10000;
  p->iJD = (sqlite4_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000);
  p->validJD = 1;
  if( p->validHMS ){
    p->iJD += p->h*3600000 + p->m*60000 + (sqlite4_int64)(p->s*1000);



    if( p->validTZ ){
      p->iJD -= p->tz*60000;
      p->validYMD = 0;
      p->validHMS = 0;
      p->validTZ = 0;
    }
  }
................................................................................
** as there is a year and date.
*/
static int parseDateOrTime(
  sqlite4_context *context, 
  const char *zDate, 
  DateTime *p
){
  double r;
  if( parseYyyyMmDd(zDate,p)==0 ){
    return 0;
  }else if( parseHhMmSs(zDate, p)==0 ){
    return 0;
  }else if( sqlite4_stricmp(zDate,"now")==0){
    return setDateTimeToCurrent(context, p);
  }else if( sqlite4AtoF(zDate, &r, sqlite4Strlen30(zDate), SQLITE4_UTF8) ){
    p->iJD = (sqlite4_int64)(r*86400000.0 + 0.5);







    p->validJD = 1;
    return 0;
  }

  return 1;
}

/*
** Compute the Year, Month, and Day from the julian day number.
*/
static void computeYMD(DateTime *p){
................................................................................
** 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 = (int)((p->iJD + 43200000) % 86400000);
  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.validJD = 0;
  y.validTZ = 0;
  computeJD(&y);
  *pRc = SQLITE4_OK;
  return y.iJD - x.iJD;
}
#endif /* SQLITE4_OMIT_LOCALTIME */











/*
** Process a modifier to a date-time stamp.  The modifiers are
** as follows:
**
**     NNN days
**     NNN hours
................................................................................
**     utc
**
** Return 0 on success and 1 if there is any kind of error. If the error
** is in a system call (i.e. localtime()), then an error message is written
** to context pCtx. If the error is an unrecognized modifier, no error is
** written to pCtx.
*/
static int parseModifier(sqlite4_context *pCtx, const char *zMod, DateTime *p){




  int rc = 1;
  int n;
  double r;
  char *z, zBuf[30];
  z = zBuf;
  for(n=0; n<ArraySize(zBuf)-1 && zMod[n]; n++){
    z[n] = (char)sqlite4UpperToLower[(u8)zMod[n]];
  }
  z[n] = 0;
  switch( z[0] ){
................................................................................
      /*
      **    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
               && sqlite4AtoF(&z[8], &r, sqlite4Strlen30(&z[8]), SQLITE4_UTF8)
               && (n=(int)r)==r && n>=0 && r<7 ){
        sqlite4_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': {
      /*
................................................................................
      ** or month or year.
      */
      if( strncmp(z, "start of ", 9)!=0 ) break;
      z += 9;
      computeYMD(p);
      p->validHMS = 1;
      p->h = p->m = 0;
      p->s = 0.0;
      p->validTZ = 0;
      p->validJD = 0;
      if( strcmp(z,"month")==0 ){
        p->D = 1;
        rc = 0;
      }else if( strcmp(z,"year")==0 ){
        computeYMD(p);
................................................................................
    case '3':
    case '4':
    case '5':
    case '6':
    case '7':
    case '8':
    case '9': {
      double rRounder;

      for(n=1; z[n] && z[n]!=':' && !sqlite4Isspace(z[n]); n++){}
      if( !sqlite4AtoF(z, &r, n, SQLITE4_UTF8) ){


        rc = 1;
        break;
      }

      if( z[n]==':' ){
        /* 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;
................................................................................
        if( z[0]=='-' ) tx.iJD = -tx.iJD;
        computeJD(p);
        clearYMD_HMS_TZ(p);
        p->iJD += tx.iJD;
        rc = 0;
        break;
      }

      z += n;
      while( sqlite4Isspace(*z) ) z++;
      n = sqlite4Strlen30(z);
      if( n>10 || n<3 ) break;
      if( z[n-1]=='s' ){ z[n-1] = 0; n--; }
      computeJD(p);
      rc = 0;
      rRounder = r<0 ? -0.5 : +0.5;

      if( n==3 && strcmp(z,"day")==0 ){
        p->iJD += (sqlite4_int64)(r*86400000.0 + rRounder);
      }else if( n==4 && strcmp(z,"hour")==0 ){
        p->iJD += (sqlite4_int64)(r*(86400000.0/24.0) + rRounder);
      }else if( n==6 && strcmp(z,"minute")==0 ){
        p->iJD += (sqlite4_int64)(r*(86400000.0/(24.0*60.0)) + rRounder);
      }else if( n==6 && strcmp(z,"second")==0 ){
        p->iJD += (sqlite4_int64)(r*(86400000.0/(24.0*60.0*60.0)) + rRounder);
      }else if( n==5 && strcmp(z,"month")==0 ){


        int x, y;

        computeYMD_HMS(p);
        p->M += (int)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 = (int)r;
        if( y!=r ){
          p->iJD += (sqlite4_int64)((r - y)*30.0*86400000.0 + rRounder);
        }
      }else if( n==4 && strcmp(z,"year")==0 ){
        int y = (int)r;


        computeYMD_HMS(p);
        p->Y += y;
        p->validJD = 0;
        computeJD(p);
        if( y!=r ){
          p->iJD += (sqlite4_int64)((r - y)*365.0*86400000.0 + rRounder);



        }
      }else{
        rc = 1;
      }
      clearYMD_HMS_TZ(p);
      break;
    }
................................................................................
*/
static int isDate(
  sqlite4_context *context, 
  int argc, 
  sqlite4_value **argv, 
  DateTime *p
){


  int i;
  const char *z;
  int eType;
  memset(p, 0, sizeof(*p));
  if( argc==0 ){
    return setDateTimeToCurrent(context, p);
  }
  if( (eType = sqlite4_value_type(argv[0]))==SQLITE4_FLOAT
                   || eType==SQLITE4_INTEGER ){
    p->iJD = (sqlite4_int64)(sqlite4_value_double(argv[0])*86400000.0 + 0.5);

    p->validJD = 1;
  }else{
    z = sqlite4_value_text(argv[0], 0);
    if( !z || parseDateOrTime(context, z, p) ){
      return 1;
    }
  }
................................................................................
** Return the julian day number of the date specified in the arguments
*/
static void juliandayFunc(
  sqlite4_context *context,
  int argc,
  sqlite4_value **argv
){

  DateTime x;

  if( isDate(context, argc, argv, &x)==0 ){
    computeJD(&x);
    sqlite4_result_double(context, x.iJD/86400000.0);


  }
}

/*
**    datetime( TIMESTRING, MOD, MOD, ...)
**
** Return YYYY-MM-DD HH:MM:SS
................................................................................
  sqlite4_value **argv
){
  DateTime x;
  if( isDate(context, argc, argv, &x)==0 ){
    char zBuf[100];
    computeYMD_HMS(&x);
    sqlite4_snprintf(zBuf,sizeof(zBuf), "%04d-%02d-%02d %02d:%02d:%02d",
                     x.Y, x.M, x.D, x.h, x.m, (int)(x.s));

    sqlite4_result_text(context, zBuf, -1, SQLITE4_TRANSIENT, 0);
  }
}

/*
**    time( TIMESTRING, MOD, MOD, ...)
**
................................................................................
  int argc,
  sqlite4_value **argv
){
  DateTime x;
  if( isDate(context, argc, argv, &x)==0 ){
    char zBuf[100];
    computeHMS(&x);
    sqlite4_snprintf(zBuf,sizeof(zBuf), "%02d:%02d:%02d", x.h, x.m, (int)x.s);


    sqlite4_result_text(context, zBuf, -1, SQLITE4_TRANSIENT, 0);
  }
}

/*
**    date( TIMESTRING, MOD, MOD, ...)
**
................................................................................
    if( zFmt[i]!='%' ){
      z[j++] = zFmt[i];
    }else{
      i++;
      switch( zFmt[i] ){
        case 'd':  sqlite4_snprintf(&z[j],3,"%02d",x.D); j+=2; break;
        case 'f': {
          double s = x.s;
          if( s>59.999 ) s = 59.999;


          j += sqlite4_snprintf(&z[j],7,"%06.3f", s);
          break;
        }
        case 'H':  sqlite4_snprintf(&z[j],3,"%02d",x.h); j+=2; break;
        case 'W': /* Fall thru */
        case 'j': {
          int nDay;             /* Number of days since 1st day of year */
          DateTime y = x;
................................................................................
        case 'm':  sqlite4_snprintf(&z[j],3,"%02d",x.M); j+=2; break;
        case 'M':  sqlite4_snprintf(&z[j],3,"%02d",x.m); j+=2; break;
        case 's': {
          j += sqlite4_snprintf(&z[j],30,"%lld",
                                (i64)(x.iJD/1000 - 21086676*(i64)10000));
          break;
        }
        case 'S':  sqlite4_snprintf(&z[j],3,"%02d",(int)x.s); j+=2; break;



        case 'w': {
          z[j++] = (char)(((x.iJD+129600000)/86400000) % 7) + '0';
          break;
        }
        case 'Y': {
          sqlite4_snprintf(&z[j],5,"%04d",x.Y); j+=sqlite4Strlen30(&z[j]);
          break;







|







 







|










>
|
<
|
>
|
<
|
|

|
>
>
>
>
>








|







 







|
>
>
>







 







<






|
|
>
>
>
>
>
>
>



>







 







|
|
<
>
>
>



|







 







>
>
>
>
>
>
>
>
>
>







 







|
>
>
>
>


<







 







|
|
|

>
>
>
>
>
>
>
>
>
>





|
|







 







|







 







|
>

<
>
>



>







 







>







<
>

|

|

|

|

>
>
|
>

|





|
|
|


|
>
>

|


<
<
>
>
>







 







>
>







|
|
|
>







 







>

>


|
>
>







 







|
>







 







|
>
>







 







|
|
>
>
|







 







|
>
>
>







56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
...
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
...
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
...
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
...
383
384
385
386
387
388
389
390
391

392
393
394
395
396
397
398
399
400
401
402
403
404
405
...
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
...
563
564
565
566
567
568
569
570
571
572
573
574
575
576

577
578
579
580
581
582
583
...
626
627
628
629
630
631
632
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
...
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
...
692
693
694
695
696
697
698
699
700
701

702
703
704
705
706
707
708
709
710
711
712
713
714
...
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738

739
740
741
742
743
744
745
746
747
748
749
750
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
...
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
...
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
...
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
...
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
....
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
....
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
*/
typedef struct DateTime DateTime;
struct DateTime {
  sqlite4_uint64 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 */
  sqlite4_num 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 */
};


................................................................................
** The HH, MM, and SS must each be exactly 2 digits.  The
** fractional seconds FFFF can be one or more digits.
**
** Return 1 if there is a parsing error and 0 on success.
*/
static int parseHhMmSs(const char *zDate, DateTime *p){
  int h, m, s;
  sqlite4_num ms = {0,0,0,0};
  if( getDigits(zDate, 2, 0, 24, ':', &h, 2, 0, 59, 0, &m)!=2 ){
    return 1;
  }
  zDate += 5;
  if( *zDate==':' ){
    zDate++;
    if( getDigits(zDate, 2, 0, 59, 0, &s)!=1 ){
      return 1;
    }
    zDate += 2;

    if( *zDate=='.' ){

      int iMs = 0;
      int nDigit = 1;
      while( sqlite4Isdigit(zDate[nDigit]) ){

        iMs = iMs * 10 + (zDate[nDigit] - '0');
        nDigit++;
      }
      if( nDigit>1 ){
        ms = sqlite4_num_from_int64(iMs);
        assert( ms.e==0 );
        ms.e += (1-nDigit);
        zDate += nDigit;
      }
    }
  }else{
    s = 0;
  }
  p->validJD = 0;
  p->validHMS = 1;
  p->h = h;
  p->m = m;
  p->s = sqlite4_num_add(sqlite4_num_from_int64(s), ms);
  if( parseTimezone(zDate, p) ) return 1;
  p->validTZ = (p->tz!=0)?1:0;
  return 0;
}

/*
** Convert from YYYY-MM-DD HH:MM:SS to julian day.  We always assume
................................................................................
  A = Y/100;
  B = 2 - A + (A/4);
  X1 = 36525*(Y+4716)/100;
  X2 = 306001*(M+1)/10000;
  p->iJD = (sqlite4_int64)((X1 + X2 + D + B - 1524.5 ) * 86400000);
  p->validJD = 1;
  if( p->validHMS ){
    p->iJD += p->h*3600000 + p->m*60000;
    p->iJD += sqlite4_num_to_int64(
        sqlite4_num_mul(p->s, sqlite4_num_from_int64(1000)), 0
    );
    if( p->validTZ ){
      p->iJD -= p->tz*60000;
      p->validYMD = 0;
      p->validHMS = 0;
      p->validTZ = 0;
    }
  }
................................................................................
** as there is a year and date.
*/
static int parseDateOrTime(
  sqlite4_context *context, 
  const char *zDate, 
  DateTime *p
){

  if( parseYyyyMmDd(zDate,p)==0 ){
    return 0;
  }else if( parseHhMmSs(zDate, p)==0 ){
    return 0;
  }else if( sqlite4_stricmp(zDate,"now")==0){
    return setDateTimeToCurrent(context, p);
  }else{
    sqlite4_num num;
    num = sqlite4_num_from_text(zDate, -1, SQLITE4_IGNORE_WHITESPACE, 0);
    if( sqlite4_num_isnan(num)==0 ){
      static const sqlite4_num one_half = {0, 0, -1, 5};

      num = sqlite4_num_mul(num, sqlite4_num_from_int64(86400000));
      num = sqlite4_num_add(num, one_half);
      p->iJD = sqlite4_num_to_int64(num, 0);
      p->validJD = 1;
      return 0;
    }
  }
  return 1;
}

/*
** Compute the Year, Month, and Day from the julian day number.
*/
static void computeYMD(DateTime *p){
................................................................................
** 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 = (int)((p->iJD + 43200000) % 86400000);
  p->s = sqlite4_num_div(
      sqlite4_num_from_int64(s), sqlite4_num_from_int64(1000)

  );
  s = (int)sqlite4_num_to_int64(p->s, 0);
  p->s = sqlite4_num_sub(p->s, sqlite4_num_from_int64(s));
  p->h = s/3600;
  s -= p->h*3600;
  p->m = s/60;
  p->s = sqlite4_num_add(p->s, sqlite4_num_from_int64(s - p->m*60));
  p->validHMS = 1;
}

/*
** Compute both YMD and HMS
*/
static void computeYMD_HMS(DateTime *p){
................................................................................
  y.validJD = 0;
  y.validTZ = 0;
  computeJD(&y);
  *pRc = SQLITE4_OK;
  return y.iJD - x.iJD;
}
#endif /* SQLITE4_OMIT_LOCALTIME */

static sqlite4_int64 multiplyAndRound(sqlite4_num a, i64 b){
  static const sqlite4_num aRnd[2] = { {0, 0, -1, 5}, {1, 0, -1, 5} };
  sqlite4_num res;

  res = sqlite4_num_mul(a, sqlite4_num_from_int64(b));
  assert( res.sign==0 || res.sign==1 );
  res = sqlite4_num_add(res, aRnd[res.sign]);
  return sqlite4_num_to_int64(res, 0);
}

/*
** Process a modifier to a date-time stamp.  The modifiers are
** as follows:
**
**     NNN days
**     NNN hours
................................................................................
**     utc
**
** Return 0 on success and 1 if there is any kind of error. If the error
** is in a system call (i.e. localtime()), then an error message is written
** to context pCtx. If the error is an unrecognized modifier, no error is
** written to pCtx.
*/
static int parseModifier(
  sqlite4_context *pCtx,          /* Leave error message here */
  const char *zMod,               /* date-time modifier */
  DateTime *p                     /* Update the value this points to */
){
  int rc = 1;
  int n;

  char *z, zBuf[30];
  z = zBuf;
  for(n=0; n<ArraySize(zBuf)-1 && zMod[n]; n++){
    z[n] = (char)sqlite4UpperToLower[(u8)zMod[n]];
  }
  z[n] = 0;
  switch( z[0] ){
................................................................................
      /*
      **    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 ){
        int bLossy;
        int iWeekday;
        sqlite4_int64 Z;
        sqlite4_num w;
        int n;

        for(n=8; sqlite4Isspace(z[n]); n++);
        if( z[n]==0 ) break;
        w = sqlite4_num_from_text(&z[8], -1, SQLITE4_IGNORE_WHITESPACE, 0);
        if( sqlite4_num_isnan(w) ) break;
        iWeekday = (int)sqlite4_num_to_int64(w, &bLossy);
        if( bLossy || iWeekday<0 || iWeekday>6 ) break;

        computeYMD_HMS(p);
        p->validTZ = 0;
        p->validJD = 0;
        computeJD(p);
        Z = ((p->iJD + 129600000)/86400000) % 7;
        if( Z>iWeekday ) Z -= 7;
        p->iJD += (iWeekday - Z)*86400000;
        clearYMD_HMS_TZ(p);
        rc = 0;
      }
      break;
    }
    case 's': {
      /*
................................................................................
      ** or month or year.
      */
      if( strncmp(z, "start of ", 9)!=0 ) break;
      z += 9;
      computeYMD(p);
      p->validHMS = 1;
      p->h = p->m = 0;
      memset(&p->s, 0, sizeof(sqlite4_num));
      p->validTZ = 0;
      p->validJD = 0;
      if( strcmp(z,"month")==0 ){
        p->D = 1;
        rc = 0;
      }else if( strcmp(z,"year")==0 ){
        computeYMD(p);
................................................................................
    case '3':
    case '4':
    case '5':
    case '6':
    case '7':
    case '8':
    case '9': {
      sqlite4_num num;

      for(n=1; z[n] && z[n]!=':' && !sqlite4Isspace(z[n]); n++){}

      num = sqlite4_num_from_text(z, n, SQLITE4_IGNORE_WHITESPACE, 0);
      if( sqlite4_num_isnan(num) ){
        rc = 1;
        break;
      }

      if( z[n]==':' ){
        /* 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;
................................................................................
        if( z[0]=='-' ) tx.iJD = -tx.iJD;
        computeJD(p);
        clearYMD_HMS_TZ(p);
        p->iJD += tx.iJD;
        rc = 0;
        break;
      }

      z += n;
      while( sqlite4Isspace(*z) ) z++;
      n = sqlite4Strlen30(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 += multiplyAndRound(num, 86400000);
      }else if( n==4 && strcmp(z,"hour")==0 ){
        p->iJD += multiplyAndRound(num, 86400000 / 24);
      }else if( n==6 && strcmp(z,"minute")==0 ){
        p->iJD += multiplyAndRound(num, 86400000 / (24 * 60));
      }else if( n==6 && strcmp(z,"second")==0 ){
        p->iJD += multiplyAndRound(num, 86400000 / (24 * 60 * 60));
      }else if( n==5 && strcmp(z,"month")==0 ){
        int bLossy;
        int nMonth;
        int x;
        nMonth = sqlite4_num_to_int64(num, &bLossy);
        computeYMD_HMS(p);
        p->M += nMonth;
        x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
        p->Y += x;
        p->M -= x*12;
        p->validJD = 0;
        computeJD(p);
        if( bLossy ){
          num = sqlite4_num_sub(num, sqlite4_num_from_int64(nMonth));
          p->iJD += multiplyAndRound(num, (i64)30*86400000);
        }
      }else if( n==4 && strcmp(z,"year")==0 ){
        int bLossy;
        int nYear;
        nYear = sqlite4_num_to_int64(num, &bLossy);
        computeYMD_HMS(p);
        p->Y += nYear;
        p->validJD = 0;
        computeJD(p);


        if( bLossy ){
          num = sqlite4_num_sub(num, sqlite4_num_from_int64(nYear));
          p->iJD += multiplyAndRound(num, (i64)365*86400000);
        }
      }else{
        rc = 1;
      }
      clearYMD_HMS_TZ(p);
      break;
    }
................................................................................
*/
static int isDate(
  sqlite4_context *context, 
  int argc, 
  sqlite4_value **argv, 
  DateTime *p
){
  static const sqlite4_num ms_per_day = {0, 0, 0, 86400000};

  int i;
  const char *z;
  int eType;
  memset(p, 0, sizeof(*p));
  if( argc==0 ){
    return setDateTimeToCurrent(context, p);
  }
  eType = sqlite4_value_type(argv[0]);
  if( eType==SQLITE4_FLOAT || eType==SQLITE4_INTEGER ){
    sqlite4_num jd = sqlite4_num_mul(sqlite4_value_num(argv[0]), ms_per_day);
    p->iJD = sqlite4_num_to_int64(sqlite4_num_round(jd, 0), 0);
    p->validJD = 1;
  }else{
    z = sqlite4_value_text(argv[0], 0);
    if( !z || parseDateOrTime(context, z, p) ){
      return 1;
    }
  }
................................................................................
** Return the julian day number of the date specified in the arguments
*/
static void juliandayFunc(
  sqlite4_context *context,
  int argc,
  sqlite4_value **argv
){
  static const sqlite4_num ms_per_day = {0, 0, 0, 86400000};
  DateTime x;

  if( isDate(context, argc, argv, &x)==0 ){
    computeJD(&x);
    sqlite4_result_num(context, 
        sqlite4_num_div(sqlite4_num_from_int64(x.iJD), ms_per_day)
    );
  }
}

/*
**    datetime( TIMESTRING, MOD, MOD, ...)
**
** Return YYYY-MM-DD HH:MM:SS
................................................................................
  sqlite4_value **argv
){
  DateTime x;
  if( isDate(context, argc, argv, &x)==0 ){
    char zBuf[100];
    computeYMD_HMS(&x);
    sqlite4_snprintf(zBuf,sizeof(zBuf), "%04d-%02d-%02d %02d:%02d:%02d",
        x.Y, x.M, x.D, x.h, x.m, (int)(sqlite4_num_to_int64(x.s,0))
    );
    sqlite4_result_text(context, zBuf, -1, SQLITE4_TRANSIENT, 0);
  }
}

/*
**    time( TIMESTRING, MOD, MOD, ...)
**
................................................................................
  int argc,
  sqlite4_value **argv
){
  DateTime x;
  if( isDate(context, argc, argv, &x)==0 ){
    char zBuf[100];
    computeHMS(&x);
    sqlite4_snprintf(zBuf,sizeof(zBuf), "%02d:%02d:%02d", x.h, x.m, 
        (int)(sqlite4_num_to_int64(x.s,0))
    );
    sqlite4_result_text(context, zBuf, -1, SQLITE4_TRANSIENT, 0);
  }
}

/*
**    date( TIMESTRING, MOD, MOD, ...)
**
................................................................................
    if( zFmt[i]!='%' ){
      z[j++] = zFmt[i];
    }else{
      i++;
      switch( zFmt[i] ){
        case 'd':  sqlite4_snprintf(&z[j],3,"%02d",x.D); j+=2; break;
        case 'f': {
          sqlite4_num rnd = x.s;
          int i1;
          rnd.e += 3;
          i1 = sqlite4_num_to_int64(rnd, 0);
          j += sqlite4_snprintf(&z[j], 7, "%02d.%03d", i1 / 1000, i1 % 1000);
          break;
        }
        case 'H':  sqlite4_snprintf(&z[j],3,"%02d",x.h); j+=2; break;
        case 'W': /* Fall thru */
        case 'j': {
          int nDay;             /* Number of days since 1st day of year */
          DateTime y = x;
................................................................................
        case 'm':  sqlite4_snprintf(&z[j],3,"%02d",x.M); j+=2; break;
        case 'M':  sqlite4_snprintf(&z[j],3,"%02d",x.m); j+=2; break;
        case 's': {
          j += sqlite4_snprintf(&z[j],30,"%lld",
                                (i64)(x.iJD/1000 - 21086676*(i64)10000));
          break;
        }
        case 'S':  
          sqlite4_snprintf(&z[j], 3, "%02d", (int)sqlite4_num_to_int64(x.s, 0));
          j+=2; 
          break;
        case 'w': {
          z[j++] = (char)(((x.iJD+129600000)/86400000) % 7) + '0';
          break;
        }
        case 'Y': {
          sqlite4_snprintf(&z[j],5,"%04d",x.Y); j+=sqlite4Strlen30(&z[j]);
          break;

Changes to src/func.c.

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
    if( SQLITE4_NULL==sqlite4_value_type(argv[1]) ) return;
    n = sqlite4_value_int(argv[1]);
    if( n>30 ) n = 30;
    if( n<0 ) n = 0;
  }
  if( sqlite4_value_type(argv[0])==SQLITE4_NULL ) return;

  num = sqlite4_value_num(argv[0]);
  p10 = (num.e*-1) - n;
  if( p10>0 ){
    int rnd;
    int i;
    u64 div = 1;
    for(i=0; i<p10; i++) div = div * 10;
    rnd = (num.m % div) >= (div/2);
    num.m = ((num.m / div) + rnd) * div;
  }
  num.approx = 0;
  sqlite4_result_num(context, num);
}

/*
** Allocate nByte bytes of space using sqlite4_malloc(). If the
** allocation fails, call sqlite4_result_error_nomem() to notify
** the database handle that malloc() has failed and return NULL.
** If nByte is larger than the maximum string or blob length, then







<
<
<
<
<
<
<
<
<
<
<
|







266
267
268
269
270
271
272











273
274
275
276
277
278
279
280
    if( SQLITE4_NULL==sqlite4_value_type(argv[1]) ) return;
    n = sqlite4_value_int(argv[1]);
    if( n>30 ) n = 30;
    if( n<0 ) n = 0;
  }
  if( sqlite4_value_type(argv[0])==SQLITE4_NULL ) return;












  sqlite4_result_num(context, sqlite4_num_round(sqlite4_value_num(argv[0]), n));
}

/*
** Allocate nByte bytes of space using sqlite4_malloc(). If the
** allocation fails, call sqlite4_result_error_nomem() to notify
** the database handle that malloc() has failed and return NULL.
** If nByte is larger than the maximum string or blob length, then

Changes to src/os.c.

24
25
26
27
28
29
30

31
32
33
34
35
36
37
38
39
40
41
42
43
44
/*
** The following variable, if set to a non-zero value, is interpreted as
** the number of seconds since 1970 and is used to set the result of
** sqlite4OsCurrentTime() during testing.
*/
unsigned int sqlite4_current_time = 0; /* Fake system time */
int sqlite4OsCurrentTime(sqlite4_env *pEnv, sqlite4_uint64 *pTimeOut){

  int rc = SQLITE4_OK;
  if( sqlite4_current_time ){
    *pTimeOut = (sqlite4_uint64)sqlite4_current_time * 1000;
    return SQLITE4_OK;
  }
#if SQLITE4_OS_UNIX
  static const sqlite4_int64 unixEpoch = 24405875*(sqlite4_int64)8640000;
  struct timeval sNow;
  if( gettimeofday(&sNow, 0)==0 ){
    *pTimeOut = unixEpoch + 1000*(sqlite4_int64)sNow.tv_sec + sNow.tv_usec/1000;
  }else{
    rc = SQLITE4_ERROR;
  }
  UNUSED_PARAMETER(pEnv);







>


|



<







24
25
26
27
28
29
30
31
32
33
34
35
36
37

38
39
40
41
42
43
44
/*
** The following variable, if set to a non-zero value, is interpreted as
** the number of seconds since 1970 and is used to set the result of
** sqlite4OsCurrentTime() during testing.
*/
unsigned int sqlite4_current_time = 0; /* Fake system time */
int sqlite4OsCurrentTime(sqlite4_env *pEnv, sqlite4_uint64 *pTimeOut){
  static const sqlite4_uint64 unixEpoch = 24405875*(sqlite4_int64)8640000;
  int rc = SQLITE4_OK;
  if( sqlite4_current_time ){
    *pTimeOut = unixEpoch + (sqlite4_uint64)sqlite4_current_time * 1000;
    return SQLITE4_OK;
  }
#if SQLITE4_OS_UNIX

  struct timeval sNow;
  if( gettimeofday(&sNow, 0)==0 ){
    *pTimeOut = unixEpoch + 1000*(sqlite4_int64)sNow.tv_sec + sNow.tv_usec/1000;
  }else{
    rc = SQLITE4_ERROR;
  }
  UNUSED_PARAMETER(pEnv);

Changes to test/date.test.

65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
...
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
datetest 1.23b julianday('12345.6') 12345.6
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} {
................................................................................

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==[sqlite4 -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
    sqlite4 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]
    sqlite4 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







<







 







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<

65
66
67
68
69
70
71

72
73
74
75
76
77
78
...
489
490
491
492
493
494
495


































496
datetest 1.23b julianday('12345.6') 12345.6
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} {
................................................................................

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}



































finish_test

Changes to test/permutations.test.

156
157
158
159
160
161
162

163
164
165
166
167
168
169
  coalesce.test 
  collate1.test collate2.test collate3.test collate4.test collate5.test
  collate6.test collate7.test collate8.test collate9.test collateA.test
  conflict.test 
  count.test
  cse.test
  ctime.test

  delete.test delete2.test
  distinct.test distinctagg.test
  exists.test
  e_droptrigger.test e_dropview.test
  e_resolve.test e_dropview.test
  e_select2.test
  enc4.test







>







156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
  coalesce.test 
  collate1.test collate2.test collate3.test collate4.test collate5.test
  collate6.test collate7.test collate8.test collate9.test collateA.test
  conflict.test 
  count.test
  cse.test
  ctime.test
  date.test
  delete.test delete2.test
  distinct.test distinctagg.test
  exists.test
  e_droptrigger.test e_dropview.test
  e_resolve.test e_dropview.test
  e_select2.test
  enc4.test