Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Back out the previous change. Replace it with new date modifiers "ceiling" and "floor". |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
f0831cced2c919e409214d936c81473a |
| User & Date: | drh 2024-03-03 20:15:36 |
Context
|
2024-03-04
| ||
| 06:54 | After wasm bootstrapping has read the optional global-scope config objects, delete those objects because any further changes would not have any useful effect. Expand the docs regarding the sqlite3ApiBootstrap() config object and explain the necessary evil of a separate global-scope object for that configuration. (check-in: e6b14f73 user: stephan tags: trunk) | |
|
2024-03-03
| ||
| 20:15 | Back out the previous change. Replace it with new date modifiers "ceiling" and "floor". (check-in: f0831cce user: drh tags: trunk) | |
|
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) | |
Changes
Changes to src/date.c.
| ︙ | ︙ | |||
67 68 69 70 71 72 73 |
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 */
| < > > | | | | 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 |
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 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 nFloor; /* Days to implement "floor" */
unsigned rawS : 1; /* Raw numeric value stored in s */
unsigned tzSet : 1; /* Timezone was set explicitly */
unsigned isError : 1; /* An overflow has occurred */
unsigned useSubsec : 1; /* Display subsecond precision */
};
/*
** Convert zDate into one or more integers according to the conversion
** specifier zFormat.
**
|
| ︙ | ︙ | |||
282 283 284 285 286 287 288 289 290 291 292 293 294 295 |
p->iJD -= p->tz*60000;
p->validYMD = 0;
p->validHMS = 0;
p->validTZ = 0;
}
}
}
/*
** Parse dates of the form
**
** YYYY-MM-DD HH:MM:SS.FFF
** YYYY-MM-DD HH:MM:SS
** YYYY-MM-DD HH:MM
| > > > > > > > > > > > > > > > > > > > > > > > | 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 |
p->iJD -= p->tz*60000;
p->validYMD = 0;
p->validHMS = 0;
p->validTZ = 0;
}
}
}
/*
** Given the YYYY-MM-DD information current in p, determine if there
** is day-of-month overflow and set nFloor to the number of days that
** would need to be subtracted from the date in order to bring the
** date back to the end of the month.
*/
static void computeFloor(DateTime *p){
assert( p->validYMD || p->isError );
assert( (p->D>=1 && p->D<=31) || p->isError );
assert( (p->M>=1 && p->M<=12) || p->isError );
if( p->D<=28 ){
p->nFloor = 0;
}else if( (1<<p->M) & 0x15aa ){
p->nFloor = 0;
}else if( p->M!=2 ){
p->nFloor = (p->D==31);
}else if( p->Y%4!=0 || (p->Y%100==0 && p->Y%400!=0) ){
p->nFloor = p->D - 28;
}else{
p->nFloor = p->D - 29;
}
}
/*
** Parse dates of the form
**
** YYYY-MM-DD HH:MM:SS.FFF
** YYYY-MM-DD HH:MM:SS
** YYYY-MM-DD HH:MM
|
| ︙ | ︙ | |||
321 322 323 324 325 326 327 328 329 330 331 332 333 334 |
return 1;
}
p->validJD = 0;
p->validYMD = 1;
p->Y = neg ? -Y : Y;
p->M = M;
p->D = D;
if( p->validTZ ){
computeJD(p);
}
return 0;
}
/*
| > | 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 |
return 1;
}
p->validJD = 0;
p->validYMD = 1;
p->Y = neg ? -Y : Y;
p->M = M;
p->D = D;
computeFloor(p);
if( p->validTZ ){
computeJD(p);
}
return 0;
}
/*
|
| ︙ | ︙ | |||
631 632 633 634 635 636 637 |
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 },
| < | < | 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 |
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, "year", 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.
*/
|
| ︙ | ︙ | |||
665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 | ** ** NNN days ** NNN hours ** NNN minutes ** NNN.NNNN seconds ** NNN months ** NNN years ** start of month ** start of year ** start of week ** start of day ** weekday N ** unixepoch ** localtime ** 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( | > > > > > > | 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 | ** ** NNN days ** NNN hours ** NNN minutes ** NNN.NNNN seconds ** NNN months ** NNN years ** +/-YYYY-MM-DD HH:MM:SS.SSS ** ceiling ** floor ** start of month ** start of year ** start of week ** start of day ** weekday N ** unixepoch ** auto ** localtime ** utc ** subsec ** subsecond ** ** 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( |
| ︙ | ︙ | |||
702 703 704 705 706 707 708 709 710 711 712 713 714 715 |
*/
if( sqlite3_stricmp(z, "auto")==0 ){
if( idx>1 ) return 1; /* IMP: R-33611-57934 */
autoAdjustDate(p);
rc = 0;
}
break;
}
case 'j': {
/*
** julianday
**
** Always interpret the prior number as a julian-day value. If this
** is not the first modifier, or if the prior argument is not a numeric
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 |
*/
if( sqlite3_stricmp(z, "auto")==0 ){
if( idx>1 ) return 1; /* IMP: R-33611-57934 */
autoAdjustDate(p);
rc = 0;
}
break;
}
case 'c': {
/*
** ceiling
**
** Resolve day-of-month overflow by rolling forward into the next
** month. As this is the default action, this modifier is really
** a no-op that is only included for symmetry. See "floor".
*/
if( sqlite3_stricmp(z, "ceiling")==0 ){
computeJD(p);
clearYMD_HMS_TZ(p);
rc = 0;
p->nFloor = 0;
}
break;
}
case 'f': {
/*
** floor
**
** Resolve day-of-month overflow by rolling back to the end of the
** previous month.
*/
if( sqlite3_stricmp(z, "floor")==0 ){
computeJD(p);
p->iJD -= p->nFloor*86400000;
clearYMD_HMS_TZ(p);
rc = 0;
}
break;
}
case 'j': {
/*
** julianday
**
** Always interpret the prior number as a julian-day value. If this
** is not the first modifier, or if the prior argument is not a numeric
|
| ︙ | ︙ | |||
908 909 910 911 912 913 914 915 916 917 918 919 920 921 |
}else{
p->Y += Y;
p->M += M;
}
x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
p->Y += x;
p->M -= x*12;
computeJD(p);
p->validHMS = 0;
p->validYMD = 0;
p->iJD += (i64)D*86400000;
if( z[11]==0 ){
rc = 0;
break;
| > | 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 |
}else{
p->Y += Y;
p->M += M;
}
x = p->M>0 ? (p->M-1)/12 : (p->M-12)/12;
p->Y += x;
p->M -= x*12;
computeFloor(p);
computeJD(p);
p->validHMS = 0;
p->validYMD = 0;
p->iJD += (i64)D*86400000;
if( z[11]==0 ){
rc = 0;
break;
|
| ︙ | ︙ | |||
954 955 956 957 958 959 960 |
}
/* 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);
| | > < < | < | < < | < | < > < < < < < < | 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 |
}
/* 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<3 || n>10 ) break;
if( sqlite3UpperToLower[(u8)z[n-1]]=='s' ) n--;
computeJD(p);
assert( rc==1 );
rRounder = r<0 ? -0.5 : +0.5;
p->nFloor = 0;
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[4].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;
computeFloor(p);
p->validJD = 0;
r -= (int)r;
break;
}
case 5: { /* Special processing to add years */
int y = (int)r;
assert( strcmp(aXformType[5].zName,"year")==0 );
computeYMD_HMS(p);
assert( p->M>=1 && p->M<=12 );
p->Y += y;
computeFloor(p);
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;
|
| ︙ | ︙ |
Changes to test/date.test.
| ︙ | ︙ | |||
144 145 146 147 148 149 150 |
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}
| < < < < < < < < < | 144 145 146 147 148 149 150 151 152 153 154 155 156 157 |
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
datetest 3.6 {strftime('%m','2003-10-31 12:34:56.432')} 10
|
| ︙ | ︙ | |||
578 579 580 581 582 583 584 585 |
# 2023-04 The 'subsecond' (or 'subsec') modifier alters resolutions
# to at least milliseconds. Added for release 3.42.0 .
datetest 18.2 {unixepoch('1970-01-01T00:00:00.1', 'subsec')} {0.1}
datetest 18.3 {unixepoch('1970-01-01T00:00:00.2', 'subsecond')} {0.2}
datetest 18.4 {julianday('-4713-11-24 13:40:48.864', 'subsec')} {0.07001}
datetest 18.5 {typeof(unixepoch('now', 'subsecond'))} {real}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 569 570 571 572 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 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 |
# 2023-04 The 'subsecond' (or 'subsec') modifier alters resolutions
# to at least milliseconds. Added for release 3.42.0 .
datetest 18.2 {unixepoch('1970-01-01T00:00:00.1', 'subsec')} {0.1}
datetest 18.3 {unixepoch('1970-01-01T00:00:00.2', 'subsecond')} {0.2}
datetest 18.4 {julianday('-4713-11-24 13:40:48.864', 'subsec')} {0.07001}
datetest 18.5 {typeof(unixepoch('now', 'subsecond'))} {real}
# 2024-03-03 the 'ceiling' and 'floor' operators.
#
datetest 19.1 {date('2000-01-31','floor')} {2000-01-31}
datetest 19.2a {date('2000-02-31','floor')} {2000-02-29}
datetest 19.2b {date('1999-02-31','floor')} {1999-02-28}
datetest 19.2c {date('1900-02-31','floor')} {1900-02-28}
datetest 19.3 {date('2000-03-31','floor')} {2000-03-31}
datetest 19.4 {date('2000-04-31','floor')} {2000-04-30}
datetest 19.5 {date('2000-05-31','floor')} {2000-05-31}
datetest 19.6 {date('2000-06-31','floor')} {2000-06-30}
datetest 19.7 {date('2000-07-31','floor')} {2000-07-31}
datetest 19.8 {date('2000-08-31','floor')} {2000-08-31}
datetest 19.9 {date('2000-09-31','floor')} {2000-09-30}
datetest 19.10 {date('2000-10-31','floor')} {2000-10-31}
datetest 19.11 {date('2000-11-31','floor')} {2000-11-30}
datetest 19.12 {date('2000-12-31','floor')} {2000-12-31}
datetest 19.21 {date('2000-01-31','ceiling')} {2000-01-31}
datetest 19.22a {date('2000-02-31','ceiling')} {2000-03-02}
datetest 19.22b {date('1999-02-31','ceiling')} {1999-03-03}
datetest 19.22c {date('1900-02-31','ceiling')} {1900-03-03}
datetest 19.23 {date('2000-03-31','ceiling')} {2000-03-31}
datetest 19.24 {date('2000-04-31','ceiling')} {2000-05-01}
datetest 19.25 {date('2000-05-31','ceiling')} {2000-05-31}
datetest 19.26 {date('2000-06-31','ceiling')} {2000-07-01}
datetest 19.27 {date('2000-07-31','ceiling')} {2000-07-31}
datetest 19.28 {date('2000-08-31','ceiling')} {2000-08-31}
datetest 19.29 {date('2000-09-31','ceiling')} {2000-10-01}
datetest 19.30 {date('2000-10-31','ceiling')} {2000-10-31}
datetest 19.31 {date('2000-11-31','ceiling')} {2000-12-01}
datetest 19.32 {date('2000-12-31','ceiling')} {2000-12-31}
datetest 19.40 {date('2024-01-31','+1 month','ceiling')} {2024-03-02}
datetest 19.41 {date('2024-01-31','+1 month','floor')} {2024-02-29}
datetest 19.42 {date('2023-01-31','+1 month','ceiling')} {2023-03-03}
datetest 19.43 {date('2023-01-31','+1 month','floor')} {2023-02-28}
datetest 19.44 {date('2024-02-29','+1 year','ceiling')} {2025-03-01}
datetest 19.45 {date('2024-02-29','+1 year','floor')} {2025-02-28}
datetest 19.46 {date('2024-02-29','-110 years','ceiling')} {1914-03-01}
datetest 19.47 {date('2024-02-29','-110 years','floor')} {1914-02-28}
datetest 19.48 {date('2024-02-29','-0110-00-00','floor')} {1914-02-28}
datetest 19.49 {date('2024-02-29','-0110-00-00','ceiling')} {1914-03-01}
datetest 19.50 {date('2000-08-31','+0023-06-00','floor')} {2024-02-29}
datetest 19.51 {date('2000-08-31','+0022-06-00','floor')} {2023-02-28}
datetest 19.52 {date('2000-08-31','+0023-06-00','ceiling')} {2024-03-02}
datetest 19.53 {date('2000-08-31','+0022-06-00','ceiling')} {2023-03-03}
finish_test
|