MySQL date_add
Here's a quick MySQL tip I wanted to throw your way. I created an event system a while back and one of the requirements of the system was to show events that happened yesterday forward, meaning events older than 2 days were to be hidden. MySQL's date_add will allow you to do just that:
The MySQL Example
SELECT title, venue, url, city, state, DATE_FORMAT(date_starts,\'%b %e\') as formatted_date
FROM events
WHERE date_starts >= DATE_ADD(NOW(), INTERVAL -1 DAY) // yesterday!
ORDER BY date_starts ASC
Note that I'm using a negative date value. You can use a positive date value to get tomorrow, next week, next month, etc..
I like the idea of showing events the from past few days -- they show that there's been action recently. Click here for more about MySQL date_add.
![5 Awesome New Mozilla Technologies You’ve Never Heard Of]()
My trip to Mozilla Summit 2013 was incredible. I've spent so much time focusing on my project that I had lost sight of all of the great work Mozillians were putting out. MozSummit provided the perfect reminder of how brilliant my colleagues are and how much...
![Write Simple, Elegant and Maintainable Media Queries with Sass]()
I spent a few months experimenting with different approaches for writing simple, elegant and maintainable media queries with Sass. Each solution had something that I really liked, but I couldn't find one that covered everything I needed to do, so I ventured into creating my...
![Detect Vendor Prefix with JavaScript]()
Regardless of our position on vendor prefixes, we have to live with them and occasionally use them to make things work. These prefixes can be used in two formats: the CSS format (-moz-, as in -moz-element) and the JS format (navigator.mozApps). The awesome X-Tag project has...
![MooTools-Like Element Creation in jQuery]()
I really dislike jQuery's element creation syntax. It's basically the same as typing out HTML but within a JavaScript string...ugly! Luckily Basil Goldman has created a jQuery plugin that allows you to create elements using MooTools-like syntax.
Standard jQuery Element Creation
Looks exactly like writing out...
Or you can do it like this: date_starts >= NOW() – INTERVAL 1 DAY
Easier to read in my opinion :) Good tip! I do it like this quite often
I prefer using php function strtotime() because queries using MySQL functions like NOW() can’t be cached
@macol: Ohhhh, good tip!
Hmm good tip indeed, but… The queries still cannot be cached if you use something as dynamic as strtotime(‘now’), isn’t that right?
So using strtotime() does not enable the query to be cached? Or am I wrong?
strtotime() would only give you an advantage if the same query is run during the same second?
If you want caching to work, you can truncate the time part like this:
$yesterday = strtotime('-1 day'); $yesterday_str = date('Y-m-d 00:00:00', $yesterday);Then it’ll be good for the whole day. This has the added benefit of matching the logical meaning of “yesterday” rather than being precicely 24 hours ago.
@Jeremy Parrish: Why didnt I think of this? :) Thanks!
This query will not be used index.
@Niklas Berglund: your query must be deterministic – query might provide same result no matter how many times it is run, if data remains the same. So if your query uses non-deterministic functions such as NOW(), UUID(), RAND(), CONNECTION_ID() etc it will not be cached.
However, if you pass in a fixed value from PHP as in
SELECT * FROM logs WHERE logstamp = ”
it will be cached.
(the php function today() is just a one-liner that returns the current day in mysql format)
@Metric Stormtrooper: SELECT * FROM logs WHERE logstamp = ‘::php today() ::’
hm, cant post php syntax here, but you get the idea
Or you can use this: DATE_SUB(NOW(), INTERVAL 1 DAY)