CODEWITHSUNDEEP
phpmysqli
Eilleen
Eilleen

Reputation: 393

Select date stored as Unix Timestamp and compare with given Unix TimeStamp

I am stuck for couple of Days on SQL specific scenario. The scenario is as follows,

I have a table, lets call it traffic which has 2 columns -> date and `vehicle (well many more but those are the two I need to match).

The date column is stored as Unix Timestamp. Now this would have been easy to just compare the current date (obtain from php from time() function) however the trick here is that some of these dates have time attached to them also.

For example if you run strtotime(13-02-2017 13:00) and strtotime(13-02-2017) you will get 2 different results. Basically I only care to match the date and not the time.

So I need some way to select the vehicle and date from the database that are equalled to the current Unix Timestamp but with the trick explained above, so I just need to much the date ONLY if possible.

Upvotes: 0

Views: 1552

Answers (2)

Jay Lord Ibe
Jay Lord Ibe

Reputation: 113

SELECT * FROM traffic WHERE DATE(date) = DATE(CURRENT_TIMESTAMP);

Upvotes: 0

Barmar
Barmar

Reputation: 780698

You can use FROM_UNIXTIME() to convert a timestamp to a datetime, and then use the DATE() function to get the date part of that.

WHERE DATE(FROM_UNIXTIME(date)) = CURDATE()

However, this can't use an index, so another way that can make use of an index is to check if it's in a range of timestamps for the current date:

WHERE date BETWEEN UNIX_TIMESTAMP(CURDATE()) AND UNIX_TIMESTAMP(CURDATE()) + 86399

(there are 86400 seconds in a day).

Upvotes: 1

Related Questions