CODEWITHSUNDEEP
c++databasedatabase-designsqlite
cprogrammer
cprogrammer

Reputation: 5675

how to choose SQLite date format

Sqlite has a different approach in storing time than other databases:

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.

what is the best way to store date data in a sqlite database ?. TEXT, REAL or INTEGER ?
I'm interested in something like "using TEXT is space space consuming, using INTEGER is fine but you will have a big problem in the year 2038"

Upvotes: 1

Views: 886

Answers (3)

Klaas-Z4us-V
Klaas-Z4us-V

Reputation: 327

The easiest way if you don't need fractions of seconds is epoch (integer number of seconds before or after 1970-01-01 00:00) accurate from -4714-11-24 until 5352-11-01 10:52:47

For calculation to human readable dates there is the function strftime

Upvotes: 0

ravenspoint
ravenspoint

Reputation: 20575

Is this correct?

Format  Resolution      Min Year        Max Year        Bytes/Date

Text    Milliseconds    0    AD         9999  AD        23 or 46
Real    Milliseconds?   4713 BC         ????            8
Integer Seconds         1970 AD         ????            8

Upvotes: 1

Benoit
Benoit

Reputation: 79205

  • Integers are stored on 64 bits since SQLite 3.0 so year 2038 is not really a problem.
  • With REAL or INTEGER, you must perform calculations in fractions of days or fractions of seconds when you insert. With Integer, the resolution will be one second. With TEXT, it is one millisecond.

If you are concerned about total space and do not need any milliseconds or dates prior to 1970, then go for INTEGERs.

Upvotes: 4

Related Questions