0

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.

avancho marchos
  • 180
  • 1
  • 1
  • 9
  • I understand this as: the result is correct, but you want to show 9:00 instead of 0:00. Is this correct? Or do you want to exclude earlier times, such as 8:10 from the calculation? – Thorsten Kettner Mar 23 '21 at 08:39
  • @Thorsten Kettner .i want to group the data from 9 am .No need to sum 12-9 am data – avancho marchos Mar 23 '21 at 09:31

1 Answers1

2

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;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73