CODEWITHSUNDEEP
postgresql
Arlex
Arlex

Reputation: 77

how to group by detection_time from and to a specific hour? in postgresql

The query needs to count the direction of each day, so for every day in the past months there are many people going inside/outside and I need to know how many where for each day.

At the moment I have this query which work.

select detection_time::date, 
       count(direction) 
from detections 
where client_id ='bench' 
  and direction='inside' 
GROUP BY detection_time::date 
ORDER BY detection_time desc;

The problem is that this query takes the day from 12 to 12 and I need the day to start and finish on 5am, or more specifically starting from 05:00:00 and ending at 04:59:59.

note: the detection_time column is of type: timestamp with time zone

Upvotes: 0

Views: 28

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13059

Just offset (delay) detection_time by 5 hours then use your query.

select 
   (detection_time - interval '5 hours')::date as detection_date, 
   count(direction) as direction_count
from detections 
where client_id = 'bench' and direction = 'inside' 
GROUP BY detection_date
ORDER BY detection_date desc;

Upvotes: 1

Related Questions