SQLite

Check-in [296b46c5]
Login

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

Overview
Comment:Change the magic time-interval names that do truncate-to-same-month to be "mnth" and "yr" - "month" and "year" without the vowels.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | month-truncate
Files: files | file ages | folders
SHA3-256: 296b46c529d79385d7f4db2c789254a992c01ff66fe8ba6d3a3ce4aaac0203fb
User & Date: drh 2024-03-02 20:50:56
Context
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: 5d392c16 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: 296b46c5 user: drh tags: month-truncate)
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: b606c096 user: drh tags: month-truncate)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/date.c.

︙︙
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637

638
639
640
641
642
643
644
645
646
647

**            'NNN days'
**
** Where NNN is an arbitrary floating-point number and "days" can be one
** of several units of time.
*/
static const struct {
  u8 nName;           /* Length of the name */
  char zName[9];      /* 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  },
  { 8, "pg-month", 176546.0,   2592000.0  },
  { 7, "pg-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.
*/








|



|
|
|
|
|
>
|
<
|








622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639

640
641
642
643
644
645
646
647

**            'NNN days'
**
** Where NNN is an arbitrary floating-point number and "days" can be one
** of several units of time.
*/
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.
*/
︙︙
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

      }

      /* 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
        ){
          int targetMonth = 0;
          switch( i ){
            case 6:
            case 4: { /* Special processing to add months */
              assert( strcmp(aXformType[i].zName,"month")==0
                   || strcmp(aXformType[i].zName,"pg-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;
              assert( p->M>=1 && p->M<=12 );
              if( i==6 ) targetMonth = p->M;
              p->validJD = 0;
              r -= (int)r;
              break;
            }
            case 7:
            case 5: { /* Special processing to add years */
              int y = (int)r;
              assert( strcmp(aXformType[i].zName,"year")==0
                   || strcmp(aXformType[i].zName,"pg-year")==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;








|











|
|
|
|






|




|
|

|
|








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

      }

      /* 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;
︙︙

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
︙︙