First and Last record including night

Question

I have the following problem I have the data that way:

codigo  data_hora        nome_ajudante
-------+----------------+--------------
1000004 2018-08-22 11:11 Carlos Eduardo
1000004 2018-08-22 11:43 Carlos Eduardo
1000004 2018-08-22 11:48 Carlos Eduardo
1000004 2018-08-22 11:54 Carlos Eduardo
1000004 2018-08-22 17:52 Carlos Eduardo
1000004 2018-08-23 08:13 Carlos Eduardo
1000004 2018-08-23 08:28 Carlos Eduardo
1000004 2018-08-23 10:25 Carlos Eduardo
1000004 2018-08-23 10:25 Carlos Eduardo
1000004 2018-08-23 10:25 Carlos Eduardo
1000004 2018-08-23 13:30 Carlos Eduardo
1000004 2018-08-24 22:20 Carlos Eduardo
1000004 2018-08-24 23:27 Carlos Eduardo
1000004 2018-08-25 03:14 Carlos Eduardo
1000004 2018-08-25 05:12 Carlos Eduardo

I would need to take the first and last time of the day, but also when break is night, so:

codigo  inicio              fim                 nome_ajudante 
-------+-------------------+-------------------+--------------   
1000004 2018-08-22 11:11:00 2018-08-22 17:52:00 Carlos Eduardo
1000004 2018-08-23 08:13:00 2018-08-23 13:30:00 Carlos Eduardo
1000004 2018-08-24 22:20:00 2018-08-25 05:12:00 Carlos Eduardo

I used this query, but days 24 and 25 divided, due to the grouping (GROUP BY).

SELECT 
    MAX(codigo) as codigo
    , MIN(data_hora) AS inicio
    , MAX(data_hora) AS fim
    , nome_ajudante 
FROM
    diarias_ajudantes.registro_terceiros 
GROUP BY nome_ajudante, EXTRACT(DAY FROM data_hora), EXTRACT(MONTH FROM data_hora), EXTRACT(YEAR FROM data_hora)
ORDER BY nome_ajudante, inicio ASC

Does anyone know a way to achieve the expected result?

Answer 1

At first, your query is correct, it took the smallest and largest data_hora every day.

But if that "night" you say would consider certain times as the previous day, what you can do is, before grouping, remove x hours. For example, if any time before 8:00 is considered the previous day, it would be enough to do so (the problem would be if someone entered earlier in the day, as 07:50 right?):

SELECT 
    codigo as codigo
    , MIN(data_hora) AS inicio
    , MAX(data_hora) AS fim
    , MIN(data_hora - interval '8' hour ) AS inicioConsiderado
    , MAX(data_hora - interval '8' hour) AS fimConsiderado
    , nome_ajudante 
FROM
    test 
GROUP BY codigo, nome_ajudante, EXTRACT(DAY FROM data_hora - interval '8' hour), EXTRACT(MONTH FROM data_hora - interval '8' hour), EXTRACT(YEAR FROM data_hora - interval '8' hour)
ORDER BY nome_ajudante, inicio ASC

SQL Fiddle with my test: http://sqlfiddle.com/#! 17/d77e4/5

PS: Your example data is twice a month up there

Update 01/09/2018 - using an entry schedule table

As @thiagofred asked in the comments, if you have a table indicating the entry times for each employee, the query can use it to calculate each employee’s first entry.

I set the fiddle, which stayed here: http://sqlfiddle.com/#! 17/ecb1b/1

I created a table with the employee code and a whole indicating the time of entry of each employee’s day. With this, just do the Join in the query and adjust the group clause by a little, so:

SELECT 
    test.codigo as codigo
    , MIN(test.data_hora) AS inicio
    , MAX(test.data_hora) AS fim
    , test.nome_ajudante 
FROM
    test
    join horarios on test.codigo=horarios.codigo
GROUP BY
    test.codigo,
    test.nome_ajudante,
    EXTRACT(DAY FROM test.data_hora - interval '1h' * horarios.hora_entrada),
    EXTRACT(MONTH FROM test.data_hora - interval '1h' * horarios.hora_entrada),
    EXTRACT(YEAR FROM test.data_hora - interval '1h' * horarios.hora_entrada)
ORDER BY nome_ajudante, inicio ASC

In the new SQL Fiddle, you can see that the first employee, who starts at 8:00, has one line less than the other, who starts at the same times at 5:00 am.