SQLite Forum

Proposed slightly-incompatible change to date-time functions
> No. REAL values are interpreted as Julian Dates and INT values are interpreted as unix timestamps. Do you know of an application that stores an unix timestamp as a REAL or a Julian date as an INT? I do not. So, I'm wondering if the change will have any impact at all.

I do.  Why do you assume that unix timestamps are integers?  I also sometimes store (when used for other purposes) Julian Day (integer) counts.

I have also used Rata Die daystamps (aka the unit is day but the epoch is 0001-01-01 0:00:00.000).  Once, oddly enough, I had to use ANSI month stamps (that is, the epoch was the ANSI epoch, 1601, and the unit was month and the fractional part was the fraction of the month elapsed -- that makes for an interesting conversion algorithm...)

sqlite> INSERT INTO x VALUES('-4713-11-24 12:00:00.000');
sqlite> INSERT INTO x VALUES('1970-01-01 00:00:00.000');
sqlite> INSERT INTO x VALUES('2021-11-21 17:37:38.000');
sqlite> INSERT INTO x VALUES('9999-12-31 23:59:59.000');
sqlite> select x as timestring, unixtime(x) as UnixEpochSeconds, ulp(unixtime(x)) as ResolutionSecU, julianday(x) as JulianEpochDays, ulp(julianday(x))*86400 ResolutionSecJ from x;
│        timestring        │ UnixEpochSeconds │    ResolutionSecU    │ JulianEpochDays  │    ResolutionSecJ    │
│ -4713-11-24 12:00:00.000 │ -210866760000.0  │ 3.0517578125e-05     │ 0.0              │ 9.59232693276135e-12 │
│ 1970-01-01 00:00:00.000  │ 0.0              │ 1.11022302462516e-16 │ 2440587.5        │ 4.02331352233887e-05 │
│ 2021-11-21 17:37:38.000  │ 1637516258.0     │ 2.38418579101563e-07 │ 2459540.23446759 │ 4.02331352233887e-05 │
│ 9999-12-31 23:59:59.000  │ 253402300799.0   │ 3.0517578125e-05     │ 5373484.49998843 │ 8.04662704467773e-05 │

The resolution of each format (as a double precision floating point number) is at least capable of containing the entire iJD (milliseconds since the julian epoch) to the internal resolution of a millisecond.

Assuming that an INTEGER meaning "seconds since the Unix epoch" and REAL meaning "days since the Julian epoch" is a breaking change in that anything that required "seconds since the Unix epoch" already has the appropriate unit (modifier) specified, whereas, there is presently no unit (modifier) for "days since the Julian epoch" as that is the default.

This means that all code that requires "days since the julian epoch" will now either (a) require a change to add the appropriate modifier or (b) risk misinterpretation of the value.

** NB:  unixtime is a UDF that takes the iJD value from a datetime struct and converts it from days (milliseconds, actually) since the julian epoch to seconds since the unix epoch and returns the full value (a real).