How to store date and time in SQLite

Question

This is very similar to another question , which closed as not a real question. I tried to edit it to make it valid for reopening, but I was told that I better ask a new question.

I am developing android and I need to store datetime values ​​in a sqlite database to track recurring events that will generate notifications. I will also need to query the database based on time ranges.

SQLite documentation claims that it does not support certain types of dates, but these dates can be represented using the TEXT, REAL, or INTEGER types:

TEXT as strings of ISO8601 ("YYYY-MM-DD HH: MM: SS.SSS").

REAL, as the number of Julian days, the number of days since noon in Greenwich on November 24, 4714 BC according to the predictive Gregorian calendar.

INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

The benefits of each of the initially seem to be as follows:

• TEXT is useful for readability in a database with the ability to display directly later (without the need for conversion), but expensive if you need to perform calculations. Enters a possible error from time zones.
• REAL is useful for dates before 1970, good for calculating or comparing dates. It does not represent the time of day, only days.
• INTEGER is useful for calculating or comparing time and time, very good compatibility, since it is a widely supported standard.

Does this sound right? Will using INTEGER for datetimes make queries in time ranges noticeably faster than using TEXT? Anything else that I have not considered?

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

Answer 1

TEXT is useful for readability in the database with the ability to display directly later (without the need for conversion)

The ISO format is usually not used for display; you must transform it too. (This format is more useful for debugging.)

expensive if you need to perform calculations for them

In databases, the bottleneck is usually I / O. I doubt that you will ever see a query in which the actual format of the date values ​​will have a noticeable difference.

Enters a possible error from time zones.

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

REAL is useful for dates before 1970.

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

Does not display the time of day, only days.

The time of day is represented as a fractional value.

INTEGER ... very good compatibility, as it is a widely supported standard.

But Java uses milliseconds, not seconds.

Anything else that I have not considered?

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

---

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

I would say TEXT, but I don’t think there will be a big difference.

Answer 2

The answers here will be mostly opinions, but if I were you, I would use the INTEGER type and save the unix timestamp. It seems to be less dependent on format conversion / parsing and is the most universal standard.

Answer 3

Does not display the time of day, only days.

What for fractional parts REAL for.

Will using INTEGER for datetimes make queries in time ranges noticeably faster than using TEXT?

Most likely. I cannot imagine a scenario in which row comparisons will be faster than whole comparisons, especially for indexed columns in queries.

Anything else that I have not considered?

I have no idea if you examined the effects of gamma rays on man-meadow marigolds , but that doesn't matter here :-)

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

INTEGER , IMHO.

Answer 4

Save time in UTC format as an 8-byte 64-bit integer in SQLite. Millis sence 1970.

 long time= System.currentTimeMillis(); 

With an indexed database, you get an excellent response for about 10 thousand rows in order and between datetime read data.

Leave the time zone manipulation at the presentation level, because the database with all UTC will work all over the world.

Do not store dates as text that is so ancient and does not have a place in a modern application.

I would not only store dates, because modern applications take into account when an event occurred. You can store data with 8 bytes. But if you can just put the iso date in the integer 1999-12-31 as 19991231 and store the integer in 4 bytes in sqllite.