How do DATETIME values work in SQLite?

sqlite datetime
Khairil Ushan picture Khairil Ushan · Jun 21, 2013 · Viewed 114.1k times · Source

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.

Answer

neo108 picture neo108 · Jun 21, 2013

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.

Related questions

How do I insert datetime value into a SQLite database?

I am trying to insert a datetime value into a SQLite database. It seems to be sucsessful but when I try to retrieve the value there is an error: <Unable to read data> The SQL statements are: create …

Read More
How can I store the current timestamp in SQLite as ticks?

I have a SQLite database where I store the dates as ticks. I am not using the default ISO8601 format. Let's say I have a table defined as follows: CREATE TABLE TestDate (LastModifiedTime DATETIME) Using SQL, I wish to insert …

Read More
SQLite: express the difference as days, hours, minutes between two given dates

I am trying to express the difference of two given dates in days, hours, and minutes (like 1 day, 6 hours, 17 minutes.) as SQLite query output. I have entryin and entryout as datetime fields in a SQLitedatabase. I tried all combinations of …

Read More