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
integer.
## What do you think?
Is the benefit of this change worth the small chance of breaking
legacy applications?