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,644
  • 11
  • 44
  • 65
  • 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 Jansari
  • 30,115
  • 28
  • 106
  • 129
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 Jansari
  • 30,115
  • 28
  • 106
  • 129
Alex Monthy
  • 1,737
  • 1
  • 13
  • 22
  • 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