SQLite Query For Dates Equals Today

Question

How to make the query to retrieve from database only the records that have the time equals to 'today'. I store my dates as long.

E.g: DateColumn (The name of my column) and the name of my table is MyTable

1360054701278 (Tuesday, February 5, 2013 8:58:21 AM GMT) 1359795295000 (Saturday, February 2, 2013 8:54:55 AM GMT)

So how should I make the query for this example in order to retrieve the first record (because it is the date equal to today)?

Any suggestion would be appreciated. Thanks

Answer 1

sorry for not seeing that, your problem were the additional milliseconds saved in your column.

The solution was to remove them by division ;-)

SELECT * FROM MyTable WHERE date(datetime(DateColumn / 1000 , 'unixepoch')) = date('now') 

Answer 2

Use a range for fastest query. You want to avoid converting to compare.

SELECT *
FROM   My Table
WHERE  DateColumn BETWEEN JulianDay('now') AND JulianDay('now','+1 day','-0.001 second')

Note: I just realized your dates are not stored as Julian Dates which SQLite supports natively. The concept is still the same, but you'll need to use your own conversion functions for whatever format you're storing your dates as.