MySQL date_add

By  on  

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.

Recent Features

  • By
    Create a CSS Cube

    CSS cubes really showcase what CSS has become over the years, evolving from simple color and dimension directives to a language capable of creating deep, creative visuals.  Add animation and you've got something really neat.  Unfortunately each CSS cube tutorial I've read is a bit...

  • By
    JavaScript Promise API

    While synchronous code is easier to follow and debug, async is generally better for performance and flexibility. Why "hold up the show" when you can trigger numerous requests at once and then handle them when each is ready?  Promises are becoming a big part of the JavaScript world...

Incredible Demos

  • By
    MooTools FontChecker Plugin

    There's a very interesting piece of code on Google Code called FontAvailable which does a jQuery-based JavaScript check on a string to check whether or not your system has a specific font based upon its output width. I've ported this functionality to MooTools. The MooTools...

  • By
    Do / Undo Functionality with MooTools

    We all know that do/undo functionality is a God send for word processing apps. I've used those terms so often that I think of JavaScript actions in terms of "do" an "undo." I've put together a proof of concept Do/Undo class with MooTools. The MooTools...

Discussion

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

  2. macol

    I prefer using php function strtotime() because queries using MySQL functions like NOW() can’t be cached

  3. @macol: Ohhhh, good tip!

  4. 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?

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

  6. @Jeremy Parrish: Why didnt I think of this? :) Thanks!

  7. This query will not be used index.

  8. Metric Stormtrooper

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

  9. Metric Stormtrooper

    @Metric Stormtrooper: SELECT * FROM logs WHERE logstamp = ‘::php today() ::’

    hm, cant post php syntax here, but you get the idea

  10. Or you can use this: DATE_SUB(NOW(), INTERVAL 1 DAY)

Wrap your code in <pre class="{language}"></pre> tags, link to a GitHub gist, JSFiddle fiddle, or CodePen pen to embed!