CODEWITHSUNDEEP
mysqlsql
jemz
jemz

Reputation: 5153

calculate two dates to get hour and minute

I have this data in my table, and I want to calculate the hour and minute if my query from start_time and end_time time '06:00:00' - '17:00:00' and compute the dates which is not between '06:00:00' to '17:00:00'

Note: the start_time('06:00:00') and end_time('17:00:00') are not static they are inputted in the form.

here is my data in my table.

    +----+---------------------+---------------------+
    | id |     start_time      |      end_time       |
    +----+---------------------+---------------------+
    |  3 | 2015-11-09 04:00:00 | 2015-11-09 20:00:00 |
    |  6 | 2015-11-09 04:00:00 | 2015-11-09 10:00:00 |
    |  7 | 2015-11-09 06:00:00 | 2015-11-09 17:00:00 |
    |  8 | 2015-11-09 15:00:00 | 2015-11-09 20:00:00 |
    |  9 | 2015-11-09 01:00:00 | 2015-11-09 05:00:00 |
    +----+---------------------+---------------------+


              06:00      17:00             
                |          |
                |          |
1.  S------E    |          |
                |          |
                |          |
2.          S------E       |
                |          |
                |          |
3.              | S------E |
                |          |
                |          |
4.          S----------------------E
                |          |
                |          |
5.              |      S------E
                |          |
                |          |
6.              |          |    S------E

here is my attempt.

SELECT id,Time_to_sec(Timediff(Least(Time(end_time), '17:00:00'),
                          Least(Time(start_time), '06:00:00'))) / 3600 AS
       outside
FROM   activelogs
WHERE  (Time(start_time) < '06:00:00' OR Time(end_time) > '17:00:00')


     AND Date(start_time) >= '2015-11-01'
     AND Date(end_time) <= '2015-11-20'

Expected output

+----+---------+
| id | outside |
+----+---------+
|  3 |       5 |
|  6 |       2 |
|  8 |       3 |
|  9 |       4 |
+----+---------+

Upvotes: 2

Views: 79

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

If I understand correctly, you want the time spent outside the particular boundaries. Let's do the calculation in seconds. You can convert that to whatever you like:

SELECT id,
       (greatest(time_to_sec(least(time(end_time), '06:00:00')) - time_to_sec(start_time), 0) +
        greatest(time_to_sec(end_time) - time_to_sec(greatest(time(start_time), '17:00:00')), 0)
       ) as outside_seconds
FROM activelogs
WHERE Date(start_time) >= '2015-11-01' AND Date(end_time) <= '2015-11-20';

This calculation has two components. The first is for the period before 6 a.m. and the second for the period after 5 p.m.

To eliminate id = 7, you can modify the where clause:

WHERE Date(start_time) >= '2015-11-01' AND Date(end_time) <= '2015-11-20' AND
      (Time(start_time) < '06:00:00' OR Time(end_time) > '17:00:00')

Upvotes: 4

Suing
Suing

Reputation: 453

This Also does the trick

SELECT 
    id,    
    CASE 
        WHEN (HOUR(start_time) < 6) AND (HOUR(end_time) > 17) THEN 
            (6 - HOUR(start_time) + HOUR(end_time) -17)
        WHEN (HOUR(start_time) between 6 AND 17) AND (HOUR(end_time) > 17) THEN 
            (HOUR(end_time) -17)
        WHEN (HOUR(start_time) < 6) AND (HOUR(end_time) between 6 AND 17) THEN 
            (6 - HOUR(start_time))
        WHEN (HOUR(start_time) > 17) AND (HOUR(end_time) > 17) THEN 
            (HOUR(end_time) - HOUR(start_time)) 
        WHEN (HOUR(start_time) < 6) AND (HOUR(end_time) < 6) THEN 
            (HOUR(end_time) - HOUR(start_time)) 
        ELSE 0
    END as outside
FROM activelogs

Upvotes: -1

Related Questions