1

Possible Duplicate:
MySQL convert timediff output to day, hour, minute, second format

I have a simple query which calculates time difference between two datetime as below:

 SELECT TIMEDIFF( '2012-08-19 08:25:13', '2012-07-19 18:05:52' );

Output: 734:19:21

The output is in hours:min:sec. However I want the output to be formatted as:

days:hours:min:sec

How can I format the output?

Community
  • 1
  • 1
sonam
  • 3,720
  • 12
  • 45
  • 66
  • 3
    Sounds like a job for your application, not a SQL query. SQL's job is to give your application the data, not format it pretty. I'd get the difference in seconds and format it at display time. – Dan Grossman Aug 19 '12 at 07:39

2 Answers2

5
SELECT CONCAT(
   FLOOR(HOUR(TIMEDIFF('2012-08-05 09:56', '2012-08-02 11:20')) / 24), ' days, ',
   MOD(HOUR(TIMEDIFF('2012-08-05 09:56', '2012-08-02 11:20')), 24), ' hours, ',
   MINUTE(TIMEDIFF('2012-08-05 09:56', '2012-08-02 11:20')), ' minutes, ',
   SECOND(TIMEDIFF('2012-08-05 09:56', '2012-08-02 11:20')), ' seconds')
AS TimeDiff

See this fiddle

Himanshu
  • 31,810
  • 31
  • 111
  • 133
0
SELECT date_format(from_unixtime(unix_timestamp('2012-08-19 08:25:13' ) -
unix_timestamp('2012-07-19 18:05:52' )), '%d.%m.%Y %H:%i:%s');
Himanshu
  • 31,810
  • 31
  • 111
  • 133
Alex Monthy
  • 1,827
  • 1
  • 14
  • 24
  • 1
    If the difference is less than 24 hours, the number of days should be 0, but `date_format` will not return you 0 as the day value. Basically, I think the issue is you are trying to have an *interval* interpreted as a *timestamp*. – Andriy M Aug 19 '12 at 11:08
  • SELECT date_format(from_unixtime(unix_timestamp('2012-08-20 18:25:13' ) - unix_timestamp('2012-08-19 08:05:52' )), '%d.%m.%Y %H:%i:%s'); result= 02.01.1970 12:19:21 – Alp Altunel Aug 09 '17 at 13:26