CODEWITHSUNDEEP
mysql
Paramesh Reddy
Paramesh Reddy

Reputation: 325

How to get time elapsed in mysql query?

How to get time elapsed in mysql query?.Assume we have a datetime column,therefore the output should come like this.

in 30 secs ago
in 15 minutes ago
in 3 hours ago
in 2 days ago
in 3 months ago
in 125 years ago

this can be implement by using php,but if we do in mysql query ,it will be the perfect solution.

Upvotes: 0

Views: 875

Answers (1)

Tomas M
Tomas M

Reputation: 7343

Lets assume that the date column in your table is called datecol

SELECT *, @sec=TIME_TO_SEC(TIMEDIFF(NOW(), datecol)),
  CASE
       WHEN @sec < 60 THEN CONCAT(@sec," seconds ago")
       WHEN @sec >= 60 AND @sec < 3600 THEN CONCAT(FLOOR(@sec/60)," minutes ago")
       WHEN @sec >= 3600 AND @sec < 86400 THEN CONCAT(FLOOR(@sec/86400)," hours ago")
       WHEN @sec >= 86400 AND @sec < 2592000 THEN CONCAT(FLOOR(@sec/2592000)," days ago")
       WHEN @sec >=2592000 AND @sec < 946080000 THEN CONCAT(FLOOR(@sec/946080000)," years ago")
  END AS elapsed

FROM table

However it may be a bit inaccurate because some months do not have 30 days, and some years do not have 365 days.

Upvotes: 2

Related Questions