Personally I'm not yet convinced that the normal SQL representation for what a 'date' object is matches real world use cases that well, or at least doesn't cover all of them.
As a programmer, what I find I want is a 'moment' which retains the input specification. Possibly in a sanitized binary format that's not the literal text, but also isn't a single numeric value either. E.G. Timezone Specifier / City (not just one per TZ!) + Human timespec (5 PM Tuesday the Whatever day of Month in Year) AND a pair of evaluation rules version and 'effective UTC||TAI time' for comparisons. The evaluation version thing needn't be a full version it might even just be a couple bits at the top or bottom of a long range 64 or 128 etc time number of some units. Something that can be used to determine if a value still needs to be updated with the latest rules set rather than using the cached representation.
> Offhand, Date types are really strings in SQLite.
It’s more that there’s no such thing as a date type, but that date and time functions can work with text or numbers, in a few different interpretations.
If you load the ODBC driver version of SQLite, there is an option to transparently convert the ODBC date time expressions into Oracle style dates.
It's an option for those who want easier handling.
"When the DSN Option "JDConv" (Julian Day conversion) is enabled
the SQLite 3 driver translates floating point column data
interpreted as Julian Day to/from SQL_DATE, SQL_TIME, and
SQL_TIMESTAMP data types (supported since May 2013)."
Good point / ideas about SQL date objects... but you wrote about date in response to a comment about datatypes. Is your point that datatypes more generally may be less useful than parent implied?
Personally I'm not yet convinced that the normal SQL representation for what a 'date' object is matches real world use cases that well, or at least doesn't cover all of them.
As a programmer, what I find I want is a 'moment' which retains the input specification. Possibly in a sanitized binary format that's not the literal text, but also isn't a single numeric value either. E.G. Timezone Specifier / City (not just one per TZ!) + Human timespec (5 PM Tuesday the Whatever day of Month in Year) AND a pair of evaluation rules version and 'effective UTC||TAI time' for comparisons. The evaluation version thing needn't be a full version it might even just be a couple bits at the top or bottom of a long range 64 or 128 etc time number of some units. Something that can be used to determine if a value still needs to be updated with the latest rules set rather than using the cached representation.