CODEWITHSUNDEEP
phpmysqldate
Gabriele
Gabriele

Reputation: 11

How do I compare current date with dates stored in MySQL db?

I'm coding a uploader that saves into the db the date when a file is uploaded using CURDATE(). I want to compare the current date with the one stored into the db in order to change the color of the icon related to the file depending on how long has this feel been uploaded. For instance, if less than a week, the icon will be green, if more than a week, it will be yellow, if more than two weeks, red. How can I do it? Thanks.

Upvotes: 1

Views: 988

Answers (3)

Romain
Romain

Reputation: 12819

SELECT CASE 
    WHEN DATE_FIELD >= NOW() - INTERVAL 1 WEEK THEN 'GREEN' 
    WHEN DATE_FIELD >= NOW() - INTERVAL 2 WEEK THEN 'YELLOW'
    ELSE 'RED' 
END 
FROM A_TABLE`

Basically, this will return the first value (it's the THEN <value> bits) for which the condition (the WHEN <condition> blocks) is true. The way I formulated it is a bit backwards compared to the "natural" logic but it means:

  1. if the date is within 1 week from now, green
  2. else, if the date is within 2 weeks from now, yellow
  3. else, red

Upvotes: 1

Alex
Alex

Reputation: 7374

Once you have your date from MySQL you can use strtotime($result['date']) to get the timestamp and then do pretty much anything you want with it.

Upvotes: 0

Jasmo
Jasmo

Reputation: 828

I think datediff is something you're looking after http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
        -> 1
mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
        -> -31

Upvotes: 2

Related Questions