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?

Freeboard

What kind of data are you expecting (hundreds, thousands, millions of records)? SQLite is meant to be a small db, and as such anything over a few hundred records is overkill. I'd suggest you use text to store datetimes. Only if you see a need for it (you need to calculate the datetime on thousands of records), then change the underlying data of the datetime to a real or a int. – Roar

At most the number of records would be in the low hundreds. I suppose that means performance will not be an issue, but text seems to complicate comparisons, and I'm worried about the error that daylight savings could introduce. – Freeboard

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.

Sander

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.

Kyle

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.

Untruth

Answer 4

Store UTC date time as eight byte 64 bit integer in SQLite. Millis sence 1970.

long time= System.currentTimeMillis();

With an indexed database you'll have excellent response for about 10k rows on order by and between datetime reading data.

Leave the time zone manipulations to the view layer because a database with all UTC times will work world wide.

Don't store dates as text that's so ancient and has no place in a modern application.

I would avoid storing just the dates because modern apps take into account when an event occured. You can store datetimes with 8 bytes. But if you must you can simply put the iso date into an integer 1999-12-31 as 19991231 and store a 4 byte integer in sqllite.

Adelaadelaida