Let’s start with an example:
We have a table called StartDate with some dates stored into it as long (milliseconds) like in the image below:
To understand better which date represents each record I will write below their dates:
SELECT * FROM MyTable WHERE date(datetime(DateColumn / 1000 , 'unixepoch')) = date('now')
NOTE: We have to divide the date to 1000 because we have stored our dates in milliseconds. 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 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.