Select elapsed time from MySQL

Question

I'm using this query: SELECT NOW() - date AS elapsed FROM ...

Date column is TIMESTAMP. It returns 41632 when elapsed time is about 4 hours and 20 minutes. It's not timestamp. What is it?

Or is there any better way to get elapsed time and generate with PHP like this: "2 minutes ago" "3 hours ago" "yesterday 22:32"

Thanks a lot!

http://stackoverflow.com/questions/2915864/php-how-to-find-the-time-elapsed-since-a-date-time — Marc B, Sep 13 '11 at 17:33

Conrad Frix · Answer 1 · 2011-09-13T17:51:03.533

19

Use the TimeStampDiff function

  select TIMESTAMPDIFF(MINUTE, NOW(), timestamp_column) FROM my_table

edited Sep 13 '11 at 17:51

answered Sep 13 '11 at 17:36

Conrad Frix

51,984
12
96
155

3

`select TIMESTAMPDIFF(MINUTE, NOW(), timestamp_column) FROM my_table;` – Erik Sep 13 '11 at 17:45
@Erik Thanks. I've updated my answer (and marked it CW so feel free to edit) – Conrad Frix Sep 13 '11 at 17:51

score 0 · Answer 2 · answered Nov 29 '20 at 00:13

This will show values like:

1 month
6 months
4 years
1 year
12 years
...

IF (
   TIMESTAMPDIFF(YEAR, timestamp_column, NOW()) > 0,
   CONCAT(TIMESTAMPDIFF(YEAR, timestamp_column, NOW()), " year", IF(TIMESTAMPDIFF(YEAR, timestamp_column, NOW())=1,"","s")),
   CONCAT(TIMESTAMPDIFF(MONTH, timestamp_column, NOW()), " month", IF(TIMESTAMPDIFF(MONTH, timestamp_column, NOW())=1,"","s"))
)

Select elapsed time from MySQL

2 Answers2