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

Question

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.

DATE(), perhaps. As in WHERE DATE(date) = DATE(CURRENT_TIMESTAMP) — bishop
– bishop, Commented Mar 18, 2017 at 1:07

Barmar · Accepted Answer · 2017-03-18 01:07:16Z

1

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).

answered Mar 18, 2017 at 1:07

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Eilleen Over a year ago

this works fine but when I do WHERE DATE(FROM_UNIXTIME(date)) = 2017-03-18 it fails for some reason, reason I am trying this is because sometimes the date is chosen dynamically, ie it might not need to equal to now.

Barmar Over a year ago

You need to put 2017-03-18 in quotes. Otherwise it's subtracting numbers, and it's 1996

Eilleen Over a year ago

Thank you very much it works...you saved me a lot of trouble because I was originally fetching everything and then checking for dates with php which would be extremely slow in the real world with real data.

Jay Lord Ibe · Accepted Answer · 2017-03-18 02:05:44Z

0

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

answered Mar 18, 2017 at 2:05

Jay Lord Ibe

1134 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related