How do DATETIME values work in SQLite?
SqliteDatetimeSqlite Problem Overview
I’m creating Android apps and need to save date/time of the creation record. The SQLite docs say, however, "SQLite does not have a storage class set aside for storing dates and/or times" and it's "capable of storing dates and times as TEXT, REAL, or INTEGER values".
Is there a technical reason to use one type over another? And can a single column store dates in any of the three formats from row to row?
I will need to compare dates later. For instance, in my apps I will show all records that are created between date A until date B. I am worried that not having a true DATETIME column could make comparisons difficult.
Sqlite Solutions
Solution 1 - Sqlite
SQlite does not have a specific datetime type. You can use TEXT, REAL or INTEGER types, whichever suits your needs.
Straight from the DOCS
> 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.
SQLite built-in Date and Time functions can be found here.
Solution 2 - Sqlite
> 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.
Having said that, I would use INTEGER and store seconds since Unix epoch (1970-01-01 00:00:00 UTC).
Solution 3 - Sqlite
One of the powerful features of SQLite is allowing you to choose the storage type. Advantages/disadvantages of each of the three different possibilites:
-
ISO8601 string
-
String comparison gives valid results
-
Stores fraction seconds, up to three decimal digits
-
Needs more storage space
-
You will directly see its value when using a database browser
-
Need for parsing for other uses
-
"default current_timestamp" column modifier will store using this format
-
Real number
-
High precision regarding fraction seconds
-
Longest time range
-
Integer number
-
Lowest storage space
-
Quick operations
-
Small time range
-
Possible year 2038 problem
If you need to compare different types or export to an external application, you're free to use SQLite's own datetime conversion functions as needed.
Solution 4 - Sqlite
Store it in a field of type long. See Date.getTime() and new Date(long)
Solution 5 - Sqlite
For practically all date and time matters I prefer to simplify things, very, very simple... Down to seconds stored in integers.
Integers will always be supported as integers in databases, flat files, etc. You do a little math and cast it into another type and you can format the date anyway you want.
Doing it this way, you don't have to worry when [insert current favorite database here] is replaced with [future favorite database] which coincidentally didn't use the date format you chose today.
It's just a little math overhead (eg. methods--takes two seconds, I'll post a gist if necessary) and simplifies things for a lot of operations regarding date/time later.