How to get hour and minutes

Databases
1

I have this table ACT.

+---------+---------------------+---------------------+
| user_id |     start_time      |      end_time       |
+---------+---------------------+---------------------+
|      25 | 2015-11-09 06:00:00 | 2015-11-09 15:00:00 |
|      25 | 2015-11-09 06:00:00 | 2015-11-09 18:00:00 |
|      25 | 2015-11-09 01:00:00 | 2015-11-09 05:59:59 |
|      25 | 2015-11-09 03:00:00 | 2015-11-09 10:00:00 |
+---------+---------------------+---------------------+

I want to get total hours and minuets with the two dates base on my search query form, from 2015-11-01 06:00:00 to 2015-11-25 17:00:00, and get the hours and minuets before or after this time 06:00 - 17:00 .

my brain is dry in solving this problem.

Thank you in advance.

2

Hi Jemz,

A quick Google search turned up MySQL’s TIMEDIFF() function.

3

Hello fretburner,

Thank you for the reply…yes I tried that it works if I will not specify date range to my query.I cannot get the hour and minuets if my start_time before 06:00

4

I’m not sure I understand. Can you show your SQL and what you’re trying to do?

5

This is what I am having now, and I am far away from what I want to achieve.

select time_to_sec(timediff(endtime, strtime )) / 3600 as thetime
from (
   SELECT start_time as strtime,end_time as endtime 
   
    from activelogs
) as a
where time(strtime) > '06:00' AND time(endtime) <'18:00'

as you can see the last row my problem is that i cannot get the hour and minuet

2015-11-09 03:00:00 to 06:00 ->get the hour and minuet (as outside)

and the end_time 2015-11-09 10:00:00 ->from 06:00 to 10:00 get the hour and minuet.as ( inside)

and also the second row have this start_time and end_time

2015-11-09 06:00:00 and 2015-11-09 18:00:00

I want to get the hour and minuet for 06:00 to 17:00 as(inside)
and get the hour 17:00 to 18:00 as (outside)

I don’t know if this will be achieve in mysql query.

6

before OR after?

are you sure?

7

is it possible ?

8

of course it’s possible

WHERE time_value NOT BETWEEN '06:00' AND '17:00'
9

where do I put that in my subquery ?

10

i would start by writing a query that simply calculates the hours and minutes first, i.e. it generates a value for every row

then you can apply grouping (to add up the times)

i don’t know whether you need a subquery at all

11

Ok Thank you, I will try.

12

Hi r937,

Here is what I have now , my problem is how can I get the hour and minuet
on this dates.

+---------+---------------------+---------------------+
| user_id |     start_time      |      end_time       |
+---------+---------------------+---------------------+
|      25 | 2015-11-09 15:00:00 | 2015-11-09 20:00:00 |
+---------+---------------------+---------------------+

as you can see that I have 2 hours from ‘15:00:00’ to ‘17:00’ because my end_time is ‘20:00:00’ as ‘insidetime’ and also I want to get the the hours and minuet from 17:00:00 to 20:00:00 as ‘outsidetime’

Here is my sql but I could not get my desired output.I tried to get the inside first but the ouput is wrong.

SELECT Time_to_sec(Timediff(end_time, start_time)) / 3600 AS inside
FROM   activelogs
WHERE  Time(start_time) BETWEEN '06:00' AND '17:00'
       AND start_time BETWEEN '2015-11-01 06:00:00' AND '2015-11-20 17:00:00'
13

you will need to determine the difference between “inside” time and “outside” time

“inside” time needs to be calculated on cases 2 through 5 in the following diagram

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

where S=start_time E=end_time

so for “inside” time you need this filter –

WHERE end_time   >= '06:00'   /* eliminates case 1 */
  AND start_time <= '17:00'   /* eliminates case 6 */

for the calculation, you want the greater of S and 06:00 as the start of the “inside” time

similarly, you want the lesser of 17:00 and E as the end of the “inside” time

hint: use the mysql GREATER and LEAST functions

14

:sweat: I will try , thank you for the reply.

15

@r937,

can I ask ? is there no inner join or sub queries for this ?

16

inner join? to which table?

subquery? why?

17

Okay, :confused: I think I don’t need the join and subquery, because only 1 table.

19

@r937,

I think i fixed it to show the output :), but I don’t know if this is correct

SELECT Time_to_sec(Timediff(Least(Time(end_time), '17:00:00'),
                                      Greatest(Time(start_time), '06:00:00'))) /
       3600
       AS inside
FROM   activelogs
WHERE  start_time >= '06:00:00'
       AND end_time <= '17:00:00'

if that is the correct, how can I get the outside ?like the case 1 ? do I need to create another sql query for this ?

Thank you in advance.

20

you can verify it by performing the calculations manually for a comprehensive sample

21

[quote=“jemz, post:19, topic:206803, full:true”]… how can I get the outside ?
[/quote]

in a similar manner – by examining all possible cases of overlapping, and choosing the correct formula for the non-overlapped part

yes, you’ll probably want to create another sql query for this