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:
1. Tuesday, February 5, 2013 8:10:17 PM GMT
2. Friday, February 1, 2013 8:10:17 PM GMT
3. Wednesday, February 20, 2013 8:10:17 PM GMT
4. Saturday, February 16, 2013 8:10:17 PM GMT
5. Tuesday, February 5, 2013 3:10:17 PM GMT
Now, what if we want to get from database only the records that represent the date of today, where today is 5 February 2013? This is very simple (after you find the answer :P). You will have to use the following query:
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.
So as you can see if we save our date as integer type our date will be interpreted in seconds (not milliseconds), so this is why we have to divide milliseconds to 1000 (1 second = 1000 milliseconds).
Now after you execute this query you can see that the returned records will be the first and last records (1360095017422 and 1360077017000) because they have the date equal to today
That’s all :)
