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
The SQLite date() function is used to calculate the date and return it in the format 'YYYY-MM-DD'. The SQLite datetime() function is used to calculate a date/time value, and return it in the format 'YYYY-MM-DD HH:MM:SS'. The SQLite julianday() function returns the date according to julian day.
The date() function returns the date as text in this format: YYYY-MM-DD. The time() function returns the time as text in this format: HH:MM:SS. The datetime() function returns the date and time as text in their same formats: YYYY-MM-DD HH:MM:SS.
To calculate the difference between the timestamps in SQLite, use the JULIANDAY() function for both timestamps, then subtract one from the other. This way, you get the difference in days. The integer part of the difference is the number of full days, and the decimal part represents a partial day.
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')
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With