139

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.

ruffin
  • 16,507
  • 9
  • 88
  • 138
Khairil Ushan
  • 2,358
  • 5
  • 26
  • 29

5 Answers5

97

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.

gobernador
  • 5,659
  • 3
  • 32
  • 51
neo108
  • 5,156
  • 3
  • 27
  • 41
  • 14
    Important to note -- all the methods of storing dates use formats that can be compared using the standard =, <, > and BETWEEN operators. – Larry Lustig Dec 09 '15 at 18:19
  • 5
    "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 – Andrew Slabko Apr 06 '17 at 14:14
  • 19
    @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". – Jasper Oct 13 '17 at 11:41
  • "SQLite does not have a storage class set aside for storing dates and/or times" is no longer mentioned at linked documentation page. – reducing activity Oct 07 '22 at 03:33
27

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.

Zso
  • 476
  • 4
  • 6
  • 1
    Why there is 2038 problem? INTEGER seems to support 64 bit storage. – guan boshen May 20 '20 at 02:09
  • 2
    @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()` (https://www.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. – Zoë Sparks Jun 14 '20 at 03:48
  • 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` – bmaupin Oct 13 '21 at 20:34
  • 1
    @jave.web I meant "space" as in amount of storage, since an ISO8601 string uses more bytes than an integer. – bmaupin Mar 08 '22 at 20:10
  • @bmaupin 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. – jave.web Mar 08 '22 at 20:30
20

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

Diego Torres Milano
  • 65,697
  • 9
  • 111
  • 134
  • 2
    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. – Karakuri Jun 21 '13 at 04:29
  • 1
    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? – rayzinnz May 10 '16 at 03:56
  • 2
    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](https://en.wikipedia.org/wiki/Double-precision_floating-point_format), this gives you better than millisecond resolution. `RSQLite` seems to be converting `POSIXct` to numeric epoch, so it works well enough for me. – r2evans Sep 02 '16 at 18:40
  • @r2evans I'm not sure what you are saying. If I want to store milliseconds since the Epoch, how do I do that? – Michael Apr 12 '19 at 02:51
  • 1
    It depends on your programming language or application that is accessing the sqlite database. In raw SQL, though, according to https://www.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. – r2evans Apr 12 '19 at 03:30
  • @Diego Torres Milano can you explain **why** would you use seconds in integer over the 'YYYY-MM-DD HH:II:SS' text? – jave.web Mar 08 '22 at 19:22
9

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.

Kelvin E. Williams
  • 91
  • 1
8

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

Jorgesys
  • 124,308
  • 23
  • 334
  • 268
koem
  • 554
  • 4
  • 24