So I have a Postgres table structure like this:

id|data         |start_at               |end_at                 |contractor|
-- ------------- ----------------------- ----------------------- ---------- 
 1|first insert |2022-11-22 08:00:00.000|2022-11-22 18:30:00.000|         1|
 2|second insert|2022-11-22 17:00:00.000|2022-11-22 23:30:00.000|         1|
 3|third insert |2022-11-22 22:00:00.000|2022-11-23 05:30:00.000|         1|
 4|fourth insert|2022-11-22 22:00:00.000|2022-11-23 05:30:00.000|         1|
 5|fifth insert |2022-11-21 19:30:00.000|2022-11-22 04:30:00.000|         1|

I also know there are three working shifts:

• Morning (from 06:00 to 14:00)
• Afternoon (from 14:00 to 22:00)
• Night (from 22:00 to 06:00 morning the next day)

I need to do a select that will give me total time per shift for each day depending on the inserted timestamps, for example:

id|data         |start_at               |end_at                 |contractor|morning|afternoon|night|
-- ------------- ----------------------- ----------------------- ---------- ------- --------- ----- 
 1|first insert |2022-11-22 08:00:00.000|2022-11-22 18:30:00.000|         1|      6|        4|    0|
 2|second insert|2022-11-22 17:00:00.000|2022-11-22 23:30:00.000|         1|      0|        6|    1|

I have no problem extracting total work hours, like this:

extract(hour from age(t.end_at  ,t.start_at)) as total_work_hours

but I'm not sure how to go about this particular case. Any help would be welcomed.

I tried converting the start_at and end_at timestamps to minutes after midnight, and then treating the while thing as a range of integer numbers with CASE statements, but that whole thing looks like a mess and I would like to avoid it if possible.

CodePudding user response：

This solution is a bit verbose but basically calculates the beginning end the end of the period within the corresponding shift (using greatest and least), calculates the period length in seconds and divides by 3600 and then sets the negative values to zero. Note the 1 in the night shift end expression. demo

with t as
(
select *, 
 extract ('epoch' from 
    least   (end_at,   start_at::date   '14:00'::time) 
  - greatest(start_at, start_at::date   '06:00'::time)) / 3600 first_shift,
 extract ('epoch' from 
    least   (end_at,   start_at::date   '22:00'::time) 
  - greatest(start_at, start_at::date   '14:00'::time)) / 3600 second_shift,
 extract ('epoch' from 
    least   (end_at,   start_at::date   1   '06:00'::time) 
  - greatest(start_at, start_at::date   '22:00'::time)) / 3600 third_shift
from the_table
)
select id, data, start_at, end_at, contractor, 
       case when first_shift > 0 then first_shift else 0 end morning, 
       case when second_shift > 0 then second_shift else 0 end afternoon,
       case when third_shift > 0 then third_shift else 0 end night
from t;