MySQL Forums
Forum List  »  Newbie

Re: Calculating TIME ELAPSED SINCE in days OR hours OR minutes
Posted by: Dan LeGate
Date: February 27, 2009 01:02PM

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

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.