postgresqlgroup-by

Select DISTINCT dates with hour segmentation

I have a table with counters from a machine. timestamp, input, output.

I need to get the input and output which happened at the start and end of a session. A session is a different day BUT it starts at 6:00 a.m. and ends at 6 a.m. next day. I use to do DISTINCT(DATE(timestamp)) to get the different days, BUT it does not cover the part which happens next day AND covers the previous day data till 6:00 am.

How can I group by "sessions"?

Solution

  • Shift the time for grouping purposes:

    DISTINCT(DATE(timestamp - INTERVAL '6' HOUR))