First and Last record including night

Question

First and Last record including night

Navigation

#1 by (3 votes)

2

I'm having the following problem I have the data this 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

You'd need to get the first and last times of the day, but also when nighttime breaks, like this:

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 the days 24 and 25 were divided due to 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 get the expected result?

sql postgresql

asked by anonymous 28.08.2018 / 22:57

1 answer

Preserve 'rownames' after use of family functions apply Error creating variable inside IF

score 3 · Answer 1

At first, your query is correct, it took the lowest and highest% of% of each day.

But if this "nocturnal" you say would consider certain times as being the previous day, what you can do is, before grouping, remove x hours. For example, if any time before 08:00 is considered the previous day, it would be enough to do so (the problem would be if someone entered earlier in the day, like 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: link

PS: Your sample data has twice the month up there

Update 01/09/2018 - Using a Timetable of Entry

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

I set the fiddle here: link

I have created a table with the employee code and an integer indicating the time of day for each employee. With this, it was enough to make the join in the query and adjust the clause of group by a little, thus:

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, which starts at 5:00 at the same time.