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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sqlite] How do DATETIME values work in SQLite?

Examples related to sqlite

Examples related to datetime