SQLite

Changes On Branch month-truncate
Login
Patch Unified Diff

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

Changes In Branch month-truncate Excluding Merge-Ins

This is equivalent to a diff from 803481f250 to 296b46c529

2024-03-06
20:59
Fix two -Werror=lto-type-mismatch warnings reported in forum post ef62b57bd5. (check-in: d4e423f3a4 user: mistachkin tags: branch-3.45)
2024-03-02
21:02
New date/time modifiers "mnth" and "yr" work like "month" and "year" but resolve day-of-month overflow by truncating rather than rolling over into the next month. Forum thread 232d1abb5d (check-in: 5d392c16bb user: drh tags: trunk)
20:50
Change the magic time-interval names that do truncate-to-same-month to be "mnth" and "yr" - "month" and "year" without the vowels. (Closed-Leaf check-in: 296b46c529 user: drh tags: month-truncate)
20:39
Attempt to use less memory when handling a large VALUES clause attached to an INSERT statement. This branch is buggy. (check-in: 6d4f1ae2fc user: dan tags: exp-values-clause)
13:38
Proof-of-concept for new time-interval operator "pg-month" and "pg-year" that use the truncate-to-month algorithm for month overflow instead of the wrap-to-next-month algorithm that is used by SQLite by default. (check-in: b606c09657 user: drh tags: month-truncate)
2024-02-29
13:44
Add the ability to DROP one or more objects of the same class in a single statement by listing the objects as multiple arguments to the DROP command. (check-in: 2266086cf0 user: drh tags: multi-drop)
10:55
Fix two -Werror=lto-type-mismatch warnings reported in forum post ef62b57bd5. (check-in: 803481f250 user: stephan tags: trunk)
03:45
Fix two -Werror=lto-type-mismatch warnings reported in forum post ef62b57bd5. (Closed-Leaf check-in: 29f94610dc user: stephan tags: lto-type-mismatch)
2024-02-28
15:32
Add in a VdbeCoverage() macro needed by STAT4 that should have been part of check-in [63ef234e88857a65]. (check-in: d51c699ae4 user: drh tags: trunk)

Changes to src/date.c.
︙︙
626
627
628
629
630
631
632
633
634
635
636
637

638

639
640
641
642
643
644
645

*/
static const struct {
  u8 nName;           /* Length of the name */
  char zName[7];      /* Name of the transformation */
  float rLimit;       /* Maximum NNN value for this transform */
  float rXform;       /* Constant used for this transform */
} aXformType[] = {
  { 6, "second", 4.6427e+14,       1.0  },
  { 6, "minute", 7.7379e+12,      60.0  },
  { 4, "hour",   1.2897e+11,    3600.0  },
  { 3, "day",    5373485.0,    86400.0  },
  { 5, "month",  176546.0,   2592000.0  },

  { 4, "year",   14713.0,   31536000.0  },

};

/*
** If the DateTime p is raw number, try to figure out if it is
** a julian day number of a unix timestamp.  Set the p value
** appropriately.
*/








|
|
|
|
|
>
|
>








626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647

*/
static const struct {
  u8 nName;           /* Length of the name */
  char zName[7];      /* Name of the transformation */
  float rLimit;       /* Maximum NNN value for this transform */
  float rXform;       /* Constant used for this transform */
} aXformType[] = {
  /* 0 */ { 6, "second",   4.6427e+14,         1.0  },
  /* 1 */ { 6, "minute",   7.7379e+12,        60.0  },
  /* 2 */ { 4, "hour",     1.2897e+11,      3600.0  },
  /* 3 */ { 3, "day",      5373485.0,      86400.0  },
  /* 4 */ { 5, "month",    176546.0,  30.0*86400.0  },
  /* 5 */ { 4, "mnth",     176546.0,  30.0*86400.0  },
  /* 6 */ { 4, "year",     14713.0,  365.0*86400.0  },
  /* 7 */ { 2, "yr",       14713.0,  365.0*86400.0  },
};

/*
** If the DateTime p is raw number, try to figure out if it is
** a julian day number of a unix timestamp.  Set the p value
** appropriately.
*/
︙︙
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968

969

970

971
972
973
974
975
976


977
978
979
980

981
982

983
984


985
986
987
988
989
990
991






992
993
994
995
996
997
998

      }

      /* If control reaches this point, it means the transformation is
      ** one of the forms like "+NNN days".  */
      z += n;
      while( sqlite3Isspace(*z) ) z++;
      n = sqlite3Strlen30(z);
      if( n>10 || n<3 ) break;
      if( sqlite3UpperToLower[(u8)z[n-1]]=='s' ) n--;
      computeJD(p);
      assert( rc==1 );
      rRounder = r<0 ? -0.5 : +0.5;
      for(i=0; i<ArraySize(aXformType); i++){
        if( aXformType[i].nName==n
         && sqlite3_strnicmp(aXformType[i].zName, z, n)==0
         && r>-aXformType[i].rLimit && r<aXformType[i].rLimit
        ){

          switch( i ){

            case 4: { /* Special processing to add months */

              assert( strcmp(aXformType[i].zName,"month")==0 );
              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;
              r -= (int)r;
              break;
            }

            case 5: { /* Special processing to add years */
              int y = (int)r;

              assert( strcmp(aXformType[i].zName,"year")==0 );
              computeYMD_HMS(p);


              p->Y += y;
              p->validJD = 0;
              r -= (int)r;
              break;
            }
          }
          computeJD(p);






          p->iJD += (sqlite3_int64)(r*1000.0*aXformType[i].rXform + rRounder);
          rc = 0;
          break;
        }
      }
      clearYMD_HMS_TZ(p);
      break;








|









>

>
|
>
|





>
>




>
|

>
|

>
>







>
>
>
>
>
>








954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015

      }

      /* If control reaches this point, it means the transformation is
      ** one of the forms like "+NNN days".  */
      z += n;
      while( sqlite3Isspace(*z) ) z++;
      n = sqlite3Strlen30(z);
      if( n>10 || n<2 ) break;
      if( sqlite3UpperToLower[(u8)z[n-1]]=='s' ) n--;
      computeJD(p);
      assert( rc==1 );
      rRounder = r<0 ? -0.5 : +0.5;
      for(i=0; i<ArraySize(aXformType); i++){
        if( aXformType[i].nName==n
         && sqlite3_strnicmp(aXformType[i].zName, z, n)==0
         && r>-aXformType[i].rLimit && r<aXformType[i].rLimit
        ){
          int targetMonth = 0;
          switch( i ){
            case 4:
            case 5: { /* Special processing to add months */
              assert( strcmp(aXformType[4].zName,"month")==0 );
              assert( strcmp(aXformType[5].zName,"mnth")==0 );
              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;
              assert( p->M>=1 && p->M<=12 );
              if( i==5 ) targetMonth = p->M;
              p->validJD = 0;
              r -= (int)r;
              break;
            }
            case 6:
            case 7: { /* Special processing to add years */
              int y = (int)r;
              assert( strcmp(aXformType[6].zName,"year")==0 );
              assert( strcmp(aXformType[7].zName,"yr")==0 );
              computeYMD_HMS(p);
              assert( p->M>=1 && p->M<=12 );
              if( i==7 ) targetMonth = p->M;
              p->Y += y;
              p->validJD = 0;
              r -= (int)r;
              break;
            }
          }
          computeJD(p);
          if( targetMonth>0 ){
            p->validYMD = 0;
            computeYMD(p);
            if( p->M==targetMonth+1 ) p->iJD -= p->D*86400000;
            p->validYMD = 0;
          }
          p->iJD += (sqlite3_int64)(r*1000.0*aXformType[i].rXform + rRounder);
          rc = 0;
          break;
        }
      }
      clearYMD_HMS_TZ(p);
      break;
︙︙
Changes to test/date.test.
︙︙
143
144
145
146
147
148
149









150
151
152
153
154
155
156

datetest 2.47 {datetime('2003-10-22 12:24','8.6 seconds')} {2003-10-22 12:24:08}
datetest 2.48 {datetime('2003-10-22 12:24','9.4 second')} {2003-10-22 12:24:09}
datetest 2.49 {datetime('2003-10-22 12:24','0000 second')} {2003-10-22 12:24:00}
datetest 2.50 {datetime('2003-10-22 12:24','0001 second')} {2003-10-22 12:24:01}
datetest 2.51 {datetime('2003-10-22 12:24','nonsense')} NULL

datetest 2.60 {datetime('2023-02-31')} {2023-03-03 00:00:00}










datetest 3.1 {strftime('%d','2003-10-31 12:34:56.432')} 31
datetest 3.2.1 {strftime('pre%fpost','2003-10-31 12:34:56.432')} pre56.432post
datetest 3.2.2 {strftime('%f','2003-10-31 12:34:59.9999999')} 59.999
datetest 3.3 {strftime('%H','2003-10-31 12:34:56.432')} 12
datetest 3.4 {strftime('%j','2003-10-31 12:34:56.432')} 304
datetest 3.5 {strftime('%J','2003-10-31 12:34:56.432')} 2452944.024264259








>
>
>
>
>
>
>
>
>








143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165

datetest 2.47 {datetime('2003-10-22 12:24','8.6 seconds')} {2003-10-22 12:24:08}
datetest 2.48 {datetime('2003-10-22 12:24','9.4 second')} {2003-10-22 12:24:09}
datetest 2.49 {datetime('2003-10-22 12:24','0000 second')} {2003-10-22 12:24:00}
datetest 2.50 {datetime('2003-10-22 12:24','0001 second')} {2003-10-22 12:24:01}
datetest 2.51 {datetime('2003-10-22 12:24','nonsense')} NULL

datetest 2.60 {datetime('2023-02-31')} {2023-03-03 00:00:00}

datetest 2.70 {date('2024-01-31','+1 month')} {2024-03-02}
datetest 2.71 {date('2024-01-31','+1 mnth')} {2024-02-29}
datetest 2.72 {date('2023-01-31','+1 month')} {2023-03-03}
datetest 2.73 {date('2023-01-31','+1 mnth')} {2023-02-28}
datetest 2.74 {date('2024-02-29','+1 year')} {2025-03-01}
datetest 2.75 {date('2024-02-29','+1 yr')} {2025-02-28}
datetest 2.76 {date('2024-02-29','-110 years')} {1914-03-01}
datetest 2.77 {date('2024-02-29','-110 yrs')} {1914-02-28}

datetest 3.1 {strftime('%d','2003-10-31 12:34:56.432')} 31
datetest 3.2.1 {strftime('pre%fpost','2003-10-31 12:34:56.432')} pre56.432post
datetest 3.2.2 {strftime('%f','2003-10-31 12:34:59.9999999')} 59.999
datetest 3.3 {strftime('%H','2003-10-31 12:34:56.432')} 12
datetest 3.4 {strftime('%j','2003-10-31 12:34:56.432')} 304
datetest 3.5 {strftime('%J','2003-10-31 12:34:56.432')} 2452944.024264259
︙︙