lundi 27 juillet 2015

Clarity needed on SQLite Column "type" vs the syntax for UPDATE and INSERT

There are answers all over StackOverflow that quote the same section of the documentation (as follows):

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

  • TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
  • REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
  • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

However I think that some people (question askers and answer-ers) are confused about or omit the following detail regarding table creation, where DateTime is actually a NUMERIC type internally, and should be created as a DATETIME or NUMERIC to avoid performance issues relating to casting.

I may be misunderstanding something, but I do see answers out there telling people to create a TEXT column for datetime, which doesn't seem efficient.

Question

Given that I can query a datetime column as TEXT, REAL, or INTEGER, what is the most efficient way to handle datetimes, especially since a column of DATETIME results in a NUMERIC.

  • Does a columnType of NUMERIC/DateTime result in casting delays due to comparisons of TEXT, REAL, or Integer?

  • Could a TEXT column type and a TEXT query type for SQLite be faster?

  • Could a REAL column type and a REAL query type for SQLite be faster?

  • Could a INTEGER column type and a INTEGER query type for SQLite be faster?

  • How would I convert a DATETIME into a NUMERIC for faster queries?

Aucun commentaire:

Enregistrer un commentaire