2

SQLite doesn't support DATE/DATETIME data type. Therefore datetime can be presented in database as unix timestamp e.g. integer, number of seconds since Jan 01 1970 or as IS0-8601 string

YYYY-MM-DD HH:MM:SS

When datetime is stored as unix timestamp we can perform queries like this: 

 select * from table where c1 < datetime(1452598502, 'unixepoch', 'localtime')

Also if date is stored as string in the form:

2016-01-10 15:44:42

queries like upper are still correctly executed (lexicographical comparison on the strings will match datetime comparison).

select * from table where c1 < '2016-01-10 15:43:52'

Futher more unix timestamp has max value year 2038, afterthat it's overflow. YYYY has maximum date 9999. Both have a max value. Is there any advantage one over another? I just can't seem to prefer one over another. Maybe I prefer datetime as string YYYY-MM-DD HH:mm:ss as it has max value greater than unixtimestamp.

broadband
  • 3,266
  • 6
  • 43
  • 73

1 Answers1

2

Unix timestamps overflow in the year 2038 only if they are stored as 32-bit values. SQLite supports 64-bit values.

A single number requires less storage space than a string (but this matters only if you have a very large number of records). You have to balance this against the easier debuggability of strings.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Link http://sqlite.org/datatype3.html mentions that integer can be stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. You are right, thanks. Another point you mentioned, if stored as string, it's easy to see datetime - you don't have to convert it. – broadband Jan 13 '16 at 06:58
  • You have to be careful when storing readed integer back from db to programming language variable. So you don't store in for example c# int .. (probably exception would be thrown but haven't tested it) – broadband Jan 13 '16 at 07:04