SQLite Forum

Proposed slightly-incompatible change to date-time functions
As you know, SQLite currently supports three different 
representations for date/time values:

  1.  **TEXT** →  *YYYY-MM-DD HH:MM:SS.SSS*

  2.  **REAL** →  The [Julian day number][1], the number of days since
      noon (in Greenwich) on -4714-11-24, according to the proleptic
      Gregorian calendar.

  3.  **INT** →  Unix-time or the number of seconds since 1970-01-01 00:00:00.

[1]: https://en.wikipedia.org/wiki/Julian_day

In all cases, SQLite supports date ranges from 0000-01-01 00:00:00 through
9999-12-31 23:59:59.  For the various date/time functions
(`date()`, `datetime()`, `time()`, `julianday()`, `strftime()`) the date/time
argument can be either TEXT or REAL and everything just works.  The date/time
argument can also be INT, but such INT values will still be interpreted as a Julian
day number unless followed by the `'unixepoch'` modifier.

## Proposed change

If the date/time argument is an INT value that is not within the
range of 1721060..5373483, then it is automatically interpreted as
a Unix timestamp, without needing the `'unixepoch'` modifier.  The
`'unixepoch'` modifier can still be used, but it would be redundant in
this case.

## Benefits

With this enhancement, queries could invoke SQL functions like `datetime()` on
a column of type DATETIME without having to worry about the underlying storage
format.  The conversion would be completely automatic.  Indeed, columns that
are intended to store date/time values could hold a mixture of TEXT, REAL, and
INT values, and queries using the date/time functions would always be able
to interpret the values correctly.

## Possible Breakage

SQLite only defines date/time values for 0000-01-01 through 9999-12-31.  So
for that date range, I don't see any way to mess this up.  There is an
ambiguity for numeric inputs - do we interpret them as Julian day or
Unix timestamps - but this ambiguity only occurs for a limited range of
values between 1970-01-20 22:04:19 and 1970-03-04 04:38:03.  If you have
unix timestamp value in that range, and you pass them into a datetime
function as REAL numbers instead of as integers, they will get reinterpreted
as Julian day numbers unless you use the `'unixepoch'` modifier.

Perhaps a bigger problem would be Julian day numbers that are passed into
into date/time functions as integers.  That is only possible for dates
that correspond to noon UTC.  Such integer Julian day numbers would be
interpreted as unix timestamps and
transformed into date/times in vicinity of 1970-01-29.  We could mitigate
this by adding a new `'julianday'` modifier that forces the date/time
argument to be interpreted as a Julian day number even if it is an

## What do you think?

Is the benefit of this change worth the small chance of breaking
legacy applications?