SQLite Forum

Proposed slightly-incompatible change to date-time functions
So as I see it **CURRENT SITUATION**:

A 'text' argument is interpreted as an ISO-8601 subset datetime string.  
A 'numeric' argument is interpreted as a Julian Date number.  
The 'unixepoch' modifier forces the 'numeric' to be interpreted as a unixepoch timestamp rather than a Julian Date number.

\*\* numeric means a numeric type, either integer or real

Interpretation:  Numeric values are always interpreted as Julian Date numbers UNLESS the 'unixepoch' modifier is specified.


Introduce "magical behaviour" which alters how a 'numeric' argument is interpreted depeding on its presentement type *and* its value range.


Numeric arguments are no longer interpreted as Julian Date numbers.  
A new modifier must be introduced to restore the previous behaviour.  
All existing code that relies on the old behaviour must be modified so as to pass the new modifier string that the argument is to be interpreted as a Julian Date number.  
All existing code that relies on the numeric argument representing a unixepoch value rather than a julian date value *already* have the 'unixepoch' modifier.  


The addition of the automagical interpretation adds no benefit and will require all pre-existing code that depends on the current behaviour to be modified in order to continue to work as it does currently.

If one wishes to break compatibility in this manner then I would recommend that a new modifier be added to indicate that the numeric argument is to be interpreted as 'julianepoch' or some such, and that the function **throws an error** if a numeric is presented and no modifier is specified.

This will ensure that the change is **crumbling** and detected and analysed for its effect where it occurs.


A pragma setting that has three values for how to interpret numeric arguments with no modifier specified:

pragma epoch = JULIAN;     -- current state of affairs, default
pragma epoch = UNIX;       -- force 'unixepoch' unless julian is specified
pragma epoch = MAGICAL;    -- use "magical interpretation" unless a specific modifier is specified

This could, conceivably be extended to allow other epoch boundaries.  For example:

pragma epoch = JULIAN;  -- -4714-11-25 12:00:00
pragma epoch = DOTNET;  --  0001-01-01 00:00:00
pragma epoch = ANSI;    --  1601-01-01 00:00:00
pragma epoch = MJD;     --  1858-11-17 00:00:00
pragma epoch = EXCEL;   --  1900-01-00 00:00:00
pragma epoch = NTP;     --  1900-01-01 00:00:00
pragma epoch = UNIX;    --  1970-01-01 00:00:00
pragma epoch = IBMPC;   --  1980-01-01 00:00:00
pragma epoch = COCOA;   --  2001-01-01 00:00:00

You could even specify the granularity:

pragma epochunit = SECOND;
pragma epochunit = DAY;

The default would be, of course, `epoch = JULIAN` and `epochunit = DAY`, to match the presently existant code.