CODEWITHSUNDEEP
sqlitedatetimetypes
Damon
Damon

Reputation: 10809

If I'm creating a date/datetime in a column in Sqlite, do I set it to anything other than 'Text'?

Just getting into SQLite and I understand it does not use datatypes the same way as other languages. I'm building a database and it has to store date and time quite a lot. And I've read a lot about the date and time functions, etc, but I just want to make sure that in my CREATE script I shouldn't have anything other than 

BirthDate TEXT
DateTime TEXT

Upvotes: 1

Views: 2990

Answers (1)

Jason
Jason

Reputation: 529

I'm not sure exactly what your question is, but I think the below excerpt may prove to be useful (taken from http://www.sqlite.org/datatype3.html):

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.

Upvotes: 2

Related Questions