How I get difference between 2 times in words like "x hours y minutes z seconds" using mysql select query

Question

I have two time column in a table name is start_time and end_time.

I need difference between these tile. e.g. start_time = 18:40:00 and end_time = 20:50:00.

output will be 2 hours 10 minutes.

Possible duplicate of [Calculate difference between two datetimes in MySQL](http://stackoverflow.com/questions/10907750/calculate-difference-between-two-datetimes-in-mysql) — G07cha, Aug 31 '16 at 05:57

1000111 · Answer 1 · 2016-08-31T06:05:06.020

2

Use TIMEDIFF function of MySQL

SELECT 
TIMEDIFF(end_time,start_time) AS duration
FROM your_table;

TIMEDIFF() function

MySQL TIMEDIFF() returns the differences between two time or datetime expressions. It is to be noted that two expressions must be of same type.

Syntax :

TIMEDIFF(expr1,expr2)

Arguments

Name    Description
expr1   A datetime value.
expr2   A datetime value.

EDIT:

If you want to get the duration in words (e.g. x hours y minutes z seconds):

SELECT 
CONCAT
( 
IF(HOUR(TIMEDIFF(end_time,start_time)) > 0, CONCAT(HOUR(TIMEDIFF(end_time,start_time)), ' hours'),''),
IF(MINUTE(TIMEDIFF(end_time,start_time)) > 0 , CONCAT(MINUTE(TIMEDIFF(end_time,start_time)),' minutes'),''),
IF(SECOND(TIMEDIFF(end_time,start_time)) > 0 , CONCAT(SECOND(TIMEDIFF(end_time,start_time)),' seconds'),'')
) AS duration
FROM your_table

Demonstration:

SET @end_time := '20:50:00';
SET @start_time := '18:40:00';

SELECT 
CONCAT( 
IF(HOUR(TIMEDIFF(@end_time,@start_time)) > 0, CONCAT(HOUR(TIMEDIFF(@end_time,@start_time)), ' hours '),''),
IF(MINUTE(TIMEDIFF(@end_time,@start_time)) > 0 , CONCAT(MINUTE(TIMEDIFF(@end_time,@start_time)),' minutes '),''),
IF(SECOND(TIMEDIFF(@end_time,@start_time)) > 0 , CONCAT(SECOND(TIMEDIFF(@end_time,@start_time)),' seconds'),'')
) AS duration;

output: 2 hours 10 minutes

edited Aug 31 '16 at 06:05

answered Aug 31 '16 at 05:27

1000111

13,169
2
28
37

I need output will be 2 hours 10 minutes. – Harendra Singh Aug 31 '16 at 05:31
isn't it giving you `02:10:00` this? it's 2 hours 10 minutes. – 1000111 Aug 31 '16 at 05:33
I know this but can query give me result in format of 2 hours 10 minutes – Harendra Singh Aug 31 '16 at 05:35
2 hours 10 minutes 0 Seconds preferred? or you don't want words for 0 values. what? – 1000111 Aug 31 '16 at 05:37
Only need minutes – Harendra Singh Aug 31 '16 at 05:58
the updated answer work ok. now in this next is there are multiple rows of the start_time and end_time for a date range like 2016-09-01 to 2016-09-30. I want total sum of time between any given range. – Harendra Singh Aug 31 '16 at 06:12
I think this is beyond the scope of this question. You can post it as a separate question. – 1000111 Aug 31 '16 at 06:14
Please practice marking answer as accepted if this works for you so that others having same problem would find this useful. [**How to accept answer**](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) @HarendraChaudhary – 1000111 Aug 31 '16 at 11:41

How I get difference between 2 times in words like "x hours y minutes z seconds" using mysql select query

1 Answers1