Group By day for custom time interval

Question

I'm very new to SQL and time series database. I'm using crate database. I want to aggregate the data by day. But the I want to start each day start time is 9 AM not 12AM..

Time interval is 9 AM to 11.59 PM.

Unix time stamp is used to store the data. following is my sample database.

|sensorid | reading    | timestamp|
====================================
|1        | 1616457600 | 10       |
|1        | 1616461200 | 100      | 
|2        | 1616493600 | 1        |
|2        | 1616493601 | 10        |

Currently i grouped using following command. But it gives the start time as 12 AM.

select date_trunc('day', v.timestamp) as day,sum(reading)
from sensor1  v(timestamp)
group by  (DAY)

From the above table. i want sum of 1616493600 and 1616493601 data (11 is result). because 1616457600 and 1616461200 are less than 9 am data.

Answer 1

You want to add nine hours to midnight:

date_trunc('day', v.timestamp) + interval '9' hour

Edit: If you want to exclude hours before 9:00 from the data you add up, you must add a WHERE clause:

where extract(hour from v.timestamp) >= 9

Here is a complete query with all relevant data:

select
  date_trunc('day', v.timestamp) as day,
  date_trunc('day', v.timestamp) + interval '9' hour as day_start,
  min(v.timestamp) as first_data,
  max(v.timestamp) as last_data,
  sum(reading) as total_reading
from sensor1  v(timestamp)
where extract(hour from v.timestamp) >= 9
group by day
order by day;