Comparing dates in MySQL Database

Question

Being experienced in PHP, I lack in the area of comparing two dates in database.

I have two values stored in my database. Which are simply just unix time stamps.

Value 1: post_time

Value 2: expire_time

The expire time is just 7 days after the posted time. I simply need to get all posts that haven't "expired".

For the time being I've been using: if(($post['post_time']) >= time() - (60*60*24*7)), which works fine. But I'd much rather find a solution right in the query to get the results. Rather than pull all results and then filter them later.

Here is my query:

SELECT id, uid, title, description, 
       price, image, tags, post_type, 
       category, post_time, expire_time 
  FROM posts 
 ORDER BY RAND() LIMIT 8

Your help is appreciated.

convert to mysql syntax: .. WHERE post_time >= UNIX_TIMESTAMP(NOW()) - (60*60*24*7) — user557846
– user557846, Commented Jan 22, 2015 at 0:44
if leap seconds\hours\days\years matter to you, this is not the correct approach — user557846
– user557846, Commented Jan 22, 2015 at 0:48
the real date functions instead of basic arithmetic: dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html — user557846
– user557846, Commented Jan 22, 2015 at 0:53

codeispoetry · Accepted Answer · 2015-01-22 04:11:19Z

2

Just do it the way @Dagon did it:

SELECT id, uid, title, description, 
       price, image, tags, post_type, 
       category, post_time, expire_time 
  FROM posts WHERE post_time >= UNIX_TIMESTAMP(NOW()) - (60*60*24*7) 
 ORDER BY RAND() LIMIT 8

answered Jan 22, 2015 at 4:11

codeispoetry

936 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Comparing dates in MySQL Database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related