3 ms·

Poverty in terms of datatypes available to use.

mjevans 3y ago
Offhand, Date types are really strings in SQLite. 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.
- chrismorgan 3y ago
  > 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. Documentation: https://www.sqlite.org/lang_datefunc.html https://www.sqlite.org/lang_datefunc.html.
  - chasil 3y ago
    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)." http://www.ch-werner.de/sqliteodbc/html/index.html http://www.ch-werner.de/sqliteodbc/html/index.html
- chrisweekly 3y ago
  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?

Offhand, Date types are really strings in SQLite. 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.

chrismorgan 3y ago
> 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. Documentation: https://www.sqlite.org/lang_datefunc.html https://www.sqlite.org/lang_datefunc.html.
- chasil 3y ago
  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)." http://www.ch-werner.de/sqliteodbc/html/index.html http://www.ch-werner.de/sqliteodbc/html/index.html
chrisweekly 3y ago
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?