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