Select records only with availability dates in future

nullSQL0 Comment
< 1 min. reading

Let’s say that we have some posts, movies, files or other items that should be visible to end user in two situations:

  • when availability date is not set (equals 0000-00-00 00:00:00) or
  • when availability date s higher that current date?

Here is how.

Quickest solution:

SELECT * FROM `contents` WHERE
(
    availability_date = "0000-00-00 00:00:00"
    OR
    TIMESTAMPDIFF(MINUTE, NOW(), availability_date) >= 0
);

This will “hide” (not select) any record, which availability_date is current time (in minutes, but you can of course change that) or at least one minute into future. Any record which availability_date is at least one minute “older” than current time will not be selected.

If you wish to exclude also records that have availability_date equal current time (NOW()) then simply change >= operator into >.

Read more in MySQL manual — TIMESTAMPDIFF() function.

Leave a Reply