CODEWITHSUNDEEP
phpmysqloptimization
djdy
djdy

Reputation: 6919

Letting MySQL figure out date intervals vs. PHP

In a MySQL 5.1 InnoDB environment, what is the best way to SELECT data based on date intervals?

Letting MySQL do it via something like DATE_SUB(CURDATE(), INTERVAL 5 DAY) > created_at

Or have PHP prepare the date before submission via strtotime?

Upvotes: 4

Views: 243

Answers (4)

Johan
Johan

Reputation: 76557

You can select a date range using BETWEEN ... AND ...

SELECT * FROM table1 t1 WHERE now() BETWEEN t1.startdate AND t1.enddate

or

SELECT * FROM table1 t1 
WHERE t1.somedate BETWEEN DATE_SUB(now(),INTERVAL 1 DAY) AND now()

I prefer this syntax because it's so close to my mental picture of ranges.

Upvotes: 0

Ed Heal
Ed Heal

Reputation: 59997

I would keep as much logic closest to the database - it has more ability to do any optimisations. Also gives the ability to do change tables easier, change the use of PHP (perhaps java/C# ...) in the future. It also differentiates beteen database (for the data), PHP (for HTML delivery), Javascript (for user enjoyment), CSS (to make things pretty)

Upvotes: 0

Alan Delimon
Alan Delimon

Reputation: 817

I would do it via the query to MySql. That way, you keep the logic of selecting dates out of the PHP. The PHP just handles the display, and you get the advantage of smaller chunks of data coming out of the database as well.

Upvotes: 3

a1ex07
a1ex07

Reputation: 37364

I don't think it matters from performance point of view in this case. Your expression (DATE_SUB) will be evaluated just once. Another point is that your webserver and mysql server can use different timezones, so you may have different results.

Upvotes: 1

Related Questions