How do DATETIME values work in SQLite?

Question

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.

Sihon

sqlite.org/datatype3.html – Patten

Answer 1

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.

Eighth

Important to note -- all the methods of storing dates use formats that can be compared using the standard =, <, > and BETWEEN operators. – Extravaganza

"SQLite does not have a storage class set aside for storing dates and/or times" - except it does have DATE and DATETIME types which are never mentioned in documentation – Fugate

@Slabko It doesn't. SQLite allows anything (including DATETIME) as declared type of a column. Based on that, it gives that column an affinity with a storage class (it even has the example of how this works for DATETIME in the documentation). That affinity is more like a hint, as each entry the column can actually have a different storage class. A storage class is still a step weaker than a type and can be backed by multiple types. So yes, you can use DATETIME. No, it does not actually support it as a type or storage class. Yes, the documentation actually contains the word "DATETIME". – Banderilla

"SQLite does not have a storage class set aside for storing dates and/or times" is no longer mentioned at linked documentation page. – Dylan

Answer 2

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.

Foucquet

Why there is 2038 problem? INTEGER seems to support 64 bit storage. – Aut

@guanboshen As far as I can tell, the only reason to worry about 2038 would be support on the host platform. The SQLite documentation claims to use C's localtime_r() (sqlite.org/lang_datefunc.html#caveats_and_bugs) in the reference implementation, and localtime() can potentially be vulnerable to 2038 if the host platform has a 32-bit time_t. That said, since SQLite claims to guard against this possibility by mapping outside dates into a safe range pre-conversion (see the same link), I think it's unlikely to be an issue except maybe in esoteric cases. – Detriment

For simple apps where space isn't an issue, strings are great. Where space is an issue, integer still works pretty well and can be converted when browsing the database fairly easily (provided it's stored properly), e.g. select datetime(date, 'unixepoch') from table – Pulcheria

@jave.web I meant "space" as in amount of storage, since an ISO8601 string uses more bytes than an integer. – Pulcheria

@Pulcheria thanks for clarification :-) for others: max ISO 8601 format is 27 characters (±YYYYYY-MM-DDTHH:mm:ss.sssZ) if we assume 1 character per byte, for 1 milion records, that's roughly 27MB(~25.7MiB) of storage taken by this datetime field. – Pappose

Answer 3

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).

Bensky

I prefer this as well. The standard date/time related classes are backed internally by longs anyway, and it's pretty easy to compare longs. – Somerset

INTEGER only uses 8 bytes, TEXT is using 23 bytes in this example. This is not clear how to choose what type it stores the data in. Does it mean if I create a column of INTEGER type, the functions will automatically store as Unix Time? – Trig

REAL uses 8 bytes as well. Epoch seconds will be 10 digits until late in 2286, and since IEEE double supports 15-17 significant digits, this gives you better than millisecond resolution. RSQLite seems to be converting POSIXct to numeric epoch, so it works well enough for me. – Rider

@Rider I'm not sure what you are saying. If I want to store milliseconds since the Epoch, how do I do that? – Terpstra

It depends on your programming language or application that is accessing the sqlite database. In raw SQL, though, according to sqlite.org/lang_datefunc.html, you can use SELECT (julianday('now') - 2440587.5)*86400.0 for seconds; so just use *86400000.0 at the end for milliseconds epoch. – Rider

@Diego Torres Milano can you explain why would you use seconds in integer over the 'YYYY-MM-DD HH:II:SS' text? – Pappose

Answer 4

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.

Michaelmas

Answer 5

Store it in a field of type long. See Date.getTime() and new Date(long)

Brag

how can i compare it ?? can u give me sample of query .. :D – Sihon

Look at Joda Time for comparisons in code (joda-time.sourceforge.net) and use simple long comparison in SQL (e.g. numerical comparison). – Abell

select * from table where creation between a and b; – Brag