MySQL Query for timetable information

Question

I have a table in MySQL as following:

+----+--------+------------------+------+  
| id | userid | fecha_ingreso    | tipo |  
+----+--------+------------------+------+  
|  1 | 1      | 2015-06-08 20:00 | 1    |  
|  3 | 1      | 2015-06-09 05:00 | 2    |  
| 18 | 2      | 2015-06-09 23:30 | 1    |  
| 19 | 2      | 2015-06-10 05:00 | 2    |  
| 20 | 2      | 2015-06-10 06:00 | 1    |  
| 21 | 2      | 2015-06-10 09:00 | 2    |  
| 22 | 1      | 2015-06-09 23:30 | 1    |  
| 23 | 1      | 2015-06-10 05:00 | 2    |  
| 24 | 1      | 2015-06-10 06:00 | 1    |  
| 25 | 1      | 2015-06-10 09:00 | 2    |  
+----+--------+------------------+------+  
10 rows in set  

This table has information of every user (userid) who into the work and out of the work (fecha_ingreso). For example with userid=1 has enter (tipo=1) at '2015-06-08 20:00' and hes left the job at '2015-06-09 05:00'.

Then I have the userid=2 who is entered (tipo=1) to job at '2015-06-09 23:30' (nighttime) and he left (tipo=2) job for breakfast at '2015-06-10 05:00' and he entered again at '2015-06-10 06:00' and he finally left job at '2015-06-10 09:00'.

I'm not able to make a query that only shows me something like this:

+--------+------+---------------------+---------------------+-----------------+ 
| userid | INS | OUTS | time_after_22pm_and_lessOrEqual6am | 
+--------+------+---------------------+---------------------+-----------------+ 
| 1 | 2015-06-25 15:00:00 | 2015-06-26 23:15:00| 01:15:00 
| 2 | 2015-06-25 23:00:00 | 2015-06-26 13:30:00| 07:00:00 
+--------+------+---------------------+---------------------+-----------------+ 
2 rows in set 

This is the output I need. Even when a user can have several ins and outs, I need to show his first entrance datetime and the last out datetime with an aditional column saying how many hours and minutes and seconds hes been at work on range from 22:00 and 6am next day.

Tim, Your query is perfect but it's almost close. However I don't know how to get the hour: minute: secs when a user has been after 22pm (as my sample shows).

Answer 1

It should be easier to calculate the total working time, assuming that the worker can go work after 08:00pm and work at most 10 hours (you can adjust that):

select userid, time_ins, cnt_start, time_outs, cnt_end, seconds,     TIME_FORMAT(SEC_TO_TIME(seconds),'%H:%i:%s') as night_shift
from (
select userid, time_ins, cnt_start, time_outs, cnt_end, 
(select sum(timestampdiff(second, cnt_start,
                case when fecha_ingreso < a.cnt_start then a.cnt_start  
                    when fecha_ingreso > a.cnt_end then a.cnt_end
                    else fecha_ingreso end )  * case when tipo =1 then -1 else 1 end ) seconds 
    from test_t n
    where n.userid = a.userid and n.fecha_ingreso >= a.time_ins and n.fecha_ingreso <=a.time_outs) seconds
from (
 select userid, time_ins, cnt_start, time_outs , 
 case when cast(time_outs as date) = cast(time_ins as date) then time_outs 
    when cast(time_outs as time)>'06:00:00' then       date_add(date(time_outs), interval 6 hour) end cnt_end
 from (
 select userid, time_enter as time_ins , case when cast(time_enter as time) < '20:00:00' then 
    date_add(date(time_enter), interval 22 hour)
     else time_enter end as cnt_start,
     (select max(fecha_ingreso) from test_t l where l.userid=e.userid and l.tipo=2 and l.fecha_ingreso > e.time_enter 
     and l.fecha_ingreso < date_add(e.time_enter, interval 22 hour) )
    as time_outs
  from (
    select userid, min(fecha_ingreso) as time_enter 
      from test_t 
      where cast(fecha_ingreso as time)>='06:00:00'
      and tipo=1
      group by userid ,cast(fecha_ingreso as date)
    ) e
   ) t
  ) a
) ff;

I didn't optimize it so you can see the steps.

You can try:

select l1.* from l1 
left join (
select l.id, m.id  mid from l1 l 
join l1 m
on l.userid=m.userid and l.tipo=2 and m.tipo=1 and l.fecha_ingreso >=date_add(m.fecha_ingreso, interval -1 hour)
and l.id < m.id) x
on l1.id=x.id or l1.id =x.mid
where mid is null;

it returns

# id, userid, fecha_ingreso, tipo
'1', '1', '2015-06-08 20:00:00', '1'
'3', '1', '2015-06-09 05:00:00', '2'
'18', '2', '2015-06-09 23:30:00', '1'
'21', '2', '2015-06-10 09:00:00', '2'
'22', '1', '2015-06-09 23:30:00', '1'
'25', '1', '2015-06-10 09:00:00', '2'

by filtering out any 1-2 entries of same users within 1 hour.