CODEWITHSUNDEEP
mysql
user3296183
user3296183

Reputation: 23

How to calculate hour of time range with SQL Query (MySQL)

Is there any way to calculate hour of time range with SQL Query.

The time accept are 08.00 - 12.00 AND 13.00-16.00

I have 2 columns with start_time , end_time in time_table Table

Problem 1

start_time = 08:00:00

end_time = '12:00:00'

Hour of time range = 4 hours from 08.00 - 12.00

Problem 2

start_time = '07:00:00'

end_time = '10:00:00'

Hour of time range = 2 hours from 08.00 - 10.00

Problem 3

start_time = '10:00:00'

end_time = '17:00:00'

Hour of time range = 5 hours from 10.00 - 12.00 AND 13.00 - 16.00

Thank you , Sharif

Upvotes: 2

Views: 3292

Answers (4)

Amin Cheloh
Amin Cheloh

Reputation: 475

start_time and end_time are in integer for for easy to understand

SELECT id, SUM(hour) FROM (
    SELECT id, LEAST(12, end_time) - GREATEST(8, start_time) hour FROM `schedule` WHERE start_time<=12
    UNION
    SELECT id, LEAST(16, end_time) - GREATEST(13, start_time) hour FROM `schedule` WHERE end_time >=13
) x GROUP BY id

Upvotes: 2

Strawberry
Strawberry

Reputation: 33935

Considering the last problem...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,start_time INT NOT NULL
,end_time INT NOT NULL
);


INSERT INTO my_table (start_time,end_time) VALUES
(8,12),(13,16),(10,17);

 SELECT * FROM my_table;
 +----+------------+----------+
 | id | start_time | end_time |
 +----+------------+----------+
 |  1 |          8 |       12 |
 |  2 |         13 |       16 |
 |  3 |         10 |       17 |
 +----+------------+----------+

                          3
        <----------------------------------->
        | 1        |   |       2       |
<------------------>   <--------------->
8   9   10   11   12   13   14   15   16   17

SELECT SUM(LEAST(x.end_time,y.end_time - GREATEST(x.start_time,y.start_time) )) diff
  FROM my_table x
  JOIN my_table y 
    ON x.id <> y.id 
   AND x.end_time >= y.start_time 
   AND x.start_time < y.end_time 
 WHERE x.id = 3;

 +------+
 | diff |
 +------+
 |    5 |
 +------+

Upvotes: 0

Deepak M
Deepak M

Reputation: 93

You can use Timediff function for the same.

Like syntax timediff(end_time,start_time);

select timediff('12:00:00','08:00:00'); -- 04:00:00 select timediff('10:00:00','07:00:00'); -- 03:00:00 select timediff('17:00:00','10:00:00'); -- 07:00:00

if you want only hours to be returned then type as select HOUR(timediff('12:00:00','08:00:00')); -- 4 select HOUR(timediff('10:00:00','07:00:00')); -- 3 select HOUR(timediff('17:00:00','10:00:00')); -- 7

Upvotes: 1

elcabronel
elcabronel

Reputation: 25

Try this

SELECT TIMEDIFF('your_time_in_col', 'your_time_out_col') FROM yourtable WHERE your_key = 'your_key_value'

Upvotes: 0

Related Questions