Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLite Query For Dates Equals Today

Tags:

date

time

sqlite

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

like image 548
Laura Avatar asked Feb 05 '13 14:02

Laura


People also ask

What is the use of date () function in SQLite?

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.

How does SQLite handle dates?

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.

How do I find the difference between two dates in SQLite?

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.


2 Answers

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') 
like image 186
Najzero Avatar answered Sep 18 '22 02:09

Najzero


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.

like image 44
Samuel Neff Avatar answered Sep 19 '22 02:09

Samuel Neff