CODEWITHSUNDEEP
phpmysqldate-format
user1479534
user1479534

Reputation:

Display days ago instead of date

I have a comment area on my website and each comment displays when it was posted in the normal date format (05/Nov/2012)... But I would like to display '1 day ago' of even '3 hours ago' instead of the normal format... Here's what I have so far:

SELECT p.poll_id, p.title, date_format(c.posted, '%D %b %y') as time, COUNT( c.poll_id ) AS count, u.username
FROM users AS u
JOIN polls AS p ON u.user_id = p.user_id
LEFT JOIN comment AS c ON p.poll_id = c.poll_id
LIMIT 5

Upvotes: 1

Views: 131

Answers (1)

Emmanuel N
Emmanuel N

Reputation: 7449

Use date difference function to evaluate date difference 

SELECT p.poll_id, p.title,  DATEDIFF(NOW(), c.posted) AS daysold, COUNT( c.poll_id ) AS   count, u.username
  FROM users AS u
  JOIN polls AS p ON u.user_id = p.user_id
  LEFT JOIN comment AS c ON p.poll_id = c.poll_id
  LIMIT 5

Upvotes: 2

Related Questions