Re: Calculating TIME ELAPSED SINCE in days OR hours OR minutes

[Dan LeGate]

Thanks Jay!

Since I need to do this against datetime fields, I created a test table named time_elapsed with the following values:

Fieldname: date_updated
Values:
2009-02-27 10:23:33
2009-02-27 03:23:33
2009-02-26 08:23:33
2009-02-27 10:24:16

and then ran:

SELECT TIMESTAMPDIFF(MINUTE, date_updated, now()) FROM time_elapsed;

which got me all the minutes since those times, very similar to your data.

Then I tried the following query (modified from yours):

SELECT TIMESTAMPDIFF(MINUTE, date_updated, now()) as MinutesElapsed
CASE
WHEN MinutesElapsed < 60 THEN concat(MinutesElapsed, " minutes")
WHEN MinutesElapsed > 60 and MinutesElapsed < 1440 THEN concat(MinutesElapsed/60, " hours")
WHEN MinutesElapsed > 1440 THEN concat(MinutesElapsed/1440, " days") END as "The Display"
from time_elapsed;

but it gives me:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE
WHEN MinutesElapsed < 60 THEN concat(MinutesElapsed, " minutes")
WHEN Min' at line 2

Can I not use an alias (MinutesElapsed) here? Or did I screw up in another way? I'm on 5.0.37-community-nt

Any advice/help is appreciated.

Thanks!

Dan