Display Day, Hour, Minute Time Difference

[SalientAnimal]

Hi All,

 

Again I have a very simple question. I would like to show the TAT of a particular item in the format of DD:HH:MM. The creation date is stored in my database as a TIMESTAMP and the time difference is calculated on the current time.

 

Here is what I have at the moment:

, TIMESTAMPDIFF(HOUR,sys_date,NOW()) AS current_tat

 

I tried

, TIMESTAMPDIFF(DAY, HOUR, MINUTE,sys_date,NOW()) AS current_tat

which did not work and also other simplar variations. What is the corect way to do this please?

 

Thanks

[Barand]

SELECT SEC_TO_TIME(TIMESTAMPDIFF(SECOND,'2012-06-22 16:00:00', NOW()))

 

at 19:15 today the result was 03:15:00

[SalientAnimal]

Thanks, that did the trick :-)...

 

One last thing though is there anyway to have it show days along with the time rather than reflect say 73:53:45. so in this example it will show 03:01:53:45? Or something to this effect?

 

[Barand]

you could do this as a compromise

SELECT
TIMESTAMPDIFF(DAY,'2012-06-22 16:00:00', NOW()) as dayportion,
SEC_TO_TIME(TIMESTAMPDIFF(SECOND,'2012-06-22 16:00:00', NOW()) MOD 86400) as timeportion

 

result-->

+------------+-------------+
| dayportion | timeportion |
+------------+-------------+
|          2 | 18:35:12    |
+------------+-------------+

 

edit:

NOW() is approx 2012-06-25 10:37:11

[SalientAnimal]

Thanks

 

Just for my understanding, as I don't jsut want to plug and play the code, what exactly does the MOD 86400 do?

 

I know the 86400 is a representation of 3600 minutes in a day ,etc...

 

Also I am using a system generated date, and not a predefined date as you have captured, hopefully this won't effect the results?

[Barand]

MOD leaves the remainder when dividing by 86400.

 

it's the sql equivalent of the php "%" operator

 

eg echo 15%4;  // -->3

[SalientAnimal]

Thanks for that.