Does SQLite datetime() accept fractional modifier values?

user_262748 0
Viewed 52

As the title states, does the SQLite datetime() function accept fractional (REAL/float/etc) values for its modifier clauses? The official documentation doesn't seem to state explicitly one way or another on integer vs real. The fact that it lists NNN for minutes, hours, days, months, and years, but NNN.NNNN for seconds suggests that only seconds would take a fractional value. However, simple testing seems to show that fractional values are acceptable to any of these modifiers.

CREATE TABLE a (rel TEXT);
INSERT INTO a VALUES('0.25 days');
INSERT INTO a VALUES('0.5 days');
INSERT INTO a VALUES('1 days');
INSERT INTO a VALUES('1.5 days');
INSERT INTO a VALUES('2 days');
INSERT INTO a VALUES('7 days');
INSERT INTO a VALUES('14 days');
INSERT INTO a VALUES('0.25 months');
INSERT INTO a VALUES('0.5 months');
INSERT INTO a VALUES('1 months');
INSERT INTO a VALUES('1.5 months');
INSERT INTO a VALUES('6 months');
INSERT INTO a VALUES('0.5 years');
INSERT INTO a VALUES('1 years');
INSERT INTO a VALUES('1.5 years');

SELECT rel, datetime('now', 'localtime', rel) FROM a;

rel         datetime('now', 'localtime', rel)
----------  ---------------------------------
0.25 days   2020-03-10 05:21:35              
0.5 days    2020-03-10 11:21:35              
1 days      2020-03-10 23:21:35              
1.5 days    2020-03-11 11:21:35              
2 days      2020-03-11 23:21:35              
7 days      2020-03-16 23:21:35              
14 days     2020-03-23 23:21:35              
0.25 month  2020-03-17 11:21:35              
0.5 months  2020-03-24 23:21:35              
1 months    2020-04-09 23:21:35              
1.5 months  2020-04-24 23:21:35              
6 months    2020-09-09 23:21:35              
0.5 years   2020-09-08 11:21:35              
1 years     2021-03-09 23:21:35              
1.5 years   2021-09-08 11:21:35

I'm mostly just wondering if anyone has a conclusive answer some other proof or experience if this behavior--correct datetime calculation from fractional modifiers--can be relied upon, or if it's simply a quirk.

sqlite
1 Answers

It's not a quirk. As uncomfortable as one should be when relying on behavior that appears to be outside a limited inference from the docs, it may be time to update the docs.

SQLite's datetime implementation in src/date.c understands NNN to mean fractional numbers:

/*
** The following table defines various date transformations of the form
**
**            'NNN days'
**
** Where NNN is an arbitrary floating-point number and "days" can be one
** of several units of time.
*/

Moreover, the test cases in test/date.test do cover some fractional datetime arithmetic in units larger than seconds:

$ perl -lnE 'say if /\d\.\d+\s+(days|hours|minutes|months|years)/' date.test 
datetest 2.24 {datetime('2003-10-22 12:34','1.5 months')} {2003-12-07 12:34:00}
datetest 2.26 {datetime('2003-10-22 12:34','+10.5 minutes')} \
datetest 2.27 {datetime('2003-10-22 12:34','-1.25 hours')} \
datetest 8.15 {datetime('now','1.5 months')} {2003-12-07 12:34:00}
datetest 8.17 {datetime('now','+10.5 minutes')} {2003-10-22 12:44:30}
datetest 8.18 {datetime('now','-1.25 hours')} {2003-10-22 11:19:00}
datetest 13.21 {julianday(2454832.5,'-1.5 months')} {2454786.5}
datetest 13.22 {julianday(2454832.5,'+1.5 months')} {2454878.5}
datetest 13.23 {julianday(2454832.5,'-1.5 years')} {2454284.0}
datetest 13.24 {julianday(2454832.5,'+1.5 years')} {2455380.0}
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}

As a caveat, note that for this kind of datetime arithmetic, SQLite uses normalized time intervals that don't always match our civil reckoning: 60 second minutes (but leap seconds!), 24 hour days (but daylight savings adjustments!), 30 day months (hello, Sept, Apr, Jun, and Nov — where are your friends?), and 365 day years (but leap years!).

user_1323820
Related