0

I basically have a simple calendar I set up. As of now, it shows "future" events. And then the event expires that day... I would love to find a WHERE statement that I can use to have that "event" stay up for 1 day past the "post_date"

(so if I post it as Nov. 15th,) The event would show: Name of event - Nov. 15th

And It would stay active until +1 day from post_date? (Nov. 16th would be the expire date)

Here is what I have so far:

WHERE DATE(FROM_UNIXTIME(`date`)) >= DATE(NOW())

Thanks in advance...

ajreal
  • 46,720
  • 11
  • 89
  • 119
eberswine
  • 1,224
  • 3
  • 20
  • 39

2 Answers2

2
WHERE post_date > DATE(NOW())-INTERVAL 1 DAY

and if you really want to keep your post_dates in UNIX timestamps:

WHERE FROM_UNIXTIME(post_date) > DATE(NOW())-INTERVAL 1 DAY
AndreKR
  • 32,613
  • 18
  • 106
  • 168
  • Great! They both work great! Is there a reason I should use : – eberswine Nov 11 '10 at 18:37
  • WHERE FROM_UNIXTIME(post_date)+INTERVAL 1 DAY > DATE(NOW()) – eberswine Nov 11 '10 at 18:38
  • See to it that the expression on that side of the operator that contains the field is not too complicated, otherwise no index is used. I edited my example to make that clear. (Although a simple "+" is optimized away.) – AndreKR Nov 11 '10 at 22:22
0

Change your where statement to:

WHERE DATE(FROM_UNIXTIME(`date`)) + INTERVAL 1 DAY >= CURDATE();

Also a good idea to to use real SQL dates instead of UNIX timestamps. There are functions to do calculations on them.

Cfreak
  • 19,191
  • 6
  • 49
  • 60
  • This works perfect too!! Is there a big difference between the two answers?? – eberswine Nov 11 '10 at 18:38
  • Not a big difference. Mine compares it based explicitly on the date and ignores the time. @AndreKR's version explicitly uses the date portion of 'NOW' (I used CURDATE which returns the date only). The time portion of FROM_UNIXTIMESTAMP is effectively ignored anyway. – Cfreak Nov 11 '10 at 19:49
  • WHERE FROM_UNIXTIME(post_date) + INTERVAL 1 DAY >= CURDATE() which is a combination of the two, would likely be ever so slightly faster. – Cfreak Nov 11 '10 at 19:50
  • In such a comparison that switches over at midnight, one of both operands has to be a date. The other one can be a time. – AndreKR Nov 11 '10 at 22:18