CODEWITHSUNDEEP
phpmysqldatetimestampcompare
daGrevis
daGrevis

Reputation: 21333

How to selected results from database filtered by time-stamp?

This question is more like searching for best solution. I know how to do it in my way. :D

I have few time-intervals as words:

The problem is that time is saved in the database as UNIX time-stamp.

I will try to show an example. Lets say it's today. That means that I need to select results from database that has been made from start til end of this day.

This means that I need to get current day's first second:

mktime(0, 0, 0);

...last second:

mktime(23, 59, 59);

And query the database with something like this:

WHERE `timestamp` >= first_second OR WHERE `timestamp` <= last_sencond

And yea, I need to write this for all possible strings that are allowed. Are there any neater solution?

Thanks in an advice! :)

Upvotes: 0

Views: 195

Answers (5)

Jeremiah Gowdy
Jeremiah Gowdy

Reputation: 5622

I'd recommend storing the DateTime in SQLs native DateTime format and convert to unix time stamps in your application as needed. Of course you may not be able to change the schema, but if you can it would be best to use the most native data type the RDBMS offers. This allows the RDBMS to handle range indexes on DateTime columns more intelligently, for example. Although unix time stamps are usable on any platform using Unix Epoch based math, they're still technically a platform specific format. I wouldn't write a DateTime to SQL as a Windows FILETIME either, despite the fact that the math for FILETIME to DateTime is known.

Upvotes: 0

Marco
Marco

Reputation: 57573

For today you could use:

WHERE CAST(FROM_UNIXTIME(timestamp) AS DATE) = CURDATE()

For tomorrow:

WHERE CAST(FROM_UNIXTIME(timestamp) AS DATE) = DATE_ADD(CURDATE(), INTERVAL 1 DAY)

For this month:

WHERE MONTH(CAST(FROM_UNIXTIME(timestamp) AS DATE)) = MONTH(CURDATE())

Upvotes: 2

Kaivosukeltaja
Kaivosukeltaja

Reputation: 15735

Assuming you want records timestamped today:

WHERE date >= UNIX_TIMESTAMP(CURDATE()) AND date <= UNIX_TIMESTAMP(CURDATE()+1)

Note that the second part is not necessary unless you have items timestamped in the future. If you need to get values for other dates, calculate the dates accordingly and convert them to numeric timestamps with UNIX_TIMESTAMP(). This will have better performance than converting the values in the database to date format as that will make using indexes impossible.

Upvotes: 1

Ahmed Masud
Ahmed Masud

Reputation: 22374

Well "neater solutions" are subjective right? ... :-) Your requirement seems to be very similar to the question already answered in Mysql: Select all data between two dates

Have a read of all the links there

Upvotes: 1

ka_lin
ka_lin

Reputation: 9432

where date between $start and $stop

define start and stop depending on the option (day,week,month...etc) with maketime

Upvotes: 1

Related Questions