How to store datetime in SQLite

Question

This is very similar to another question that got closed as not a real question. I tried to edit it to make it valid for reopening but was told I would be better off asking a new question.

I'm developing on android and need to store datetime values in a sqlite database to track repeating events that will generate notifications. I will also need to be able to query the database based on time ranges.

The SQLite documentation states that it does not support specific date types but that dates can be represented using TEXT, REAL, or INTEGER types:

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.

The advantages of each initially seem to be:

• TEXT useful for readability in the database with ability to be displayed directly later (no transformation necessary), but costly if calculations need to be performed on them. Introduces possible error from time zones.
• REAL useful for dates before 1970, good for calculations or date comparisons. Does not represent time of day, only days.
• INTEGER useful for calculations or datetime comparisons, very good compatibility since it is a widely supported standard.

Does this sound correct? Would using an INTEGER for datetimes make querying time ranges noticeably faster than when using TEXT? Anything else I haven't considered?

Given my use case, which of these solutions would be best?

Answer 1

TEXT useful for readability in the database with ability to be displayed directly later (no transformation necessary)

The ISO format is usually not used for display; you have to transform this, too. (This format is more useful for debugging.)

costly if calculations need to be performed on them

With databases, the bottleneck usually is the I/O. I doubt you will ever see a query where the actual format of date values would make a noticeable difference.

Introduces possible error from time zones.

The TEXT format does not have a time zone specifier, but neither have the other formats. If you say that all your DB values are in UTC, there is no difference.

REAL useful for dates before 1970

All formats support all years between 0 and 9999. (Integers can be negative.)

Does not represent time of day, only days.

Time of day is represented as a fractional value.

INTEGER ... very good compatibility since it is a widely supported standard.

But Java uses milliseconds, not seconds.

Anything else I haven't considered?

The default output format of most of the built-in date functions is TEXT.

---

Given my use case, which of these solutions would be best?

I'd say TEXT, but I don't think there would be much of a difference.

Answer 2

Does not represent time of day, only days.

That's what the fractional portion of the REAL is for.

Would using an INTEGER for datetimes make querying time ranges noticeably faster than when using TEXT?

Most likely. I cannot envision a scenario in which string comparisons would be faster than integer comparisons, particularly for indexed columns in queries.

Anything else I haven't considered?

I have no idea if you have considered the effects of gamma rays on man-in-the-moon marigolds, but that's not important here. :-)

Given my use case, which of these solutions would be best?

INTEGER, IMHO.

Answer 3

Answers here are going to be primarily opinion, but if I were you I'd use the INTEGER type and store the unix timestamp. It seems less dependent on format conversions/parsing, and is the most universally supported standard.