What's the most efficient way to handle DateTimes, especially since a column of DATETIME results in a NUMERIC?

Question

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?

Answer 1

These questions presuppose facts which are not actually true.

SQLite has no column types. There are only column affinities, which do not restrict what types can be actually stored in a column.

There is no special DATETIME type. When the documentation mentions TEXT and REAL and INTEGER, it means that you actually have to use one of those types for your values. And once you are using one such value, SQLite will not automatically convert it into another type, because it does not know that this value is a date/time value.

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

You get casting delays only when you have values of one type stored in the column, and are comparing against another type. (And a string like '1970-01-01' is never equal with a number like 0, so such comparisons would not make sense.)

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

The column type does not really matter.

If you store TEXT values in the column, you must query with TEXT values.
If you store REAL values in the column, you must query with REAL values.
If you store INTEGER values in the column, you must query with INTEGER values.

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

There is no DATETIME type.

Answer 2

For the benefit of those asking the question above, or answering it with excessive focus on theory, I offer an answer that attempts to address the intent of your question... "what is the most efficient way to handle datetimes" in the context of "especially since a column of DATETIME results in a NUMERIC." And you also mention mobile devices in your comments.

First, in the context of mobile, rarely will you need to address large datasets. So the most efficient code you write is code that isn't confusing when you need to change it and works even as the framework evolves.

So a string or an integer may work, depending on what you are most comfortable working with. If you get confused working with dates as integers, use Strings. If you have to expose the work to a group that likes strings, go for it.

That said, if you are truly device performance driven, then integer comparisons are the fastest when using indexes. And if you have a performance problem, it is usually best solved using an index.

So, to answer question all of the questions - yes, casting results in delays, but those delays usually do not matter because you are dealing with small datasets. However, when it does matter, integer comparisons are the fastest (which is well documented like the questions you cite), so convert your DATETIME values to integers to optimize query performance.

Your title then specifically mentions "INSERT" and "UPDATE" - which then raises the question, "do you really need an index if INSERT and UPDATE performance is your concern? Indexing adds overhead to INSERT and UPDATE statements." This is also usually does not matter due to the relatively high processing power and the inability to create situations with large numbers of INSERT or UPDATE statements in mobile devices in general.

However, if this is really an issue, then standard DB optimization for transaction vs. query becomes the issue. You may need to build two databases, one to support INSERT/UPDATE activity and another for querying, with some synchronization process. Or you may have to make conscious trade-offs for the INSERT/UPDATE vs. SELECT situations you encounter.

In other words, like a very many detailed questions, the answer largely depends on the particular problems your app/software is facing, the needs/demands of the user, the hardware you target and/or support and the team that is building/maintaining the product.

Even so, the short answer is "it probably doesn't matter" and if it does, then use INTEGERs unless you can be more specific about your particular use case.