I am new to PostgreSQL(timescaleDB) and writing the time series queries for my application.
I am having a situation where I have to fetch the data for a selected time frame say (10 am to 6 pm) every day for a date range say (1st Dec to 26th Dec).
Below is my query to do this.
select
time_bucket('1 day',
device_time at time zone 'Asia/Kolkata') as device_timestamp,
round(cast(last(active_energy_delivered_kwh, device_time) - first(active_energy_delivered_kwh, device_time) as numeric), 2) as kwh
from
schneider_new
where
meter_id in ('s3')
and device_time::date between date '2018-12-01' and date '2018-12-26'
and device_time::time between time '10:00:00' and time '18:00:00'
group by
device_timestamp
The above query is working fine and I am getting the proper data as I wanted.
Now, I want to fetch the data from 10 pm tonight to 6 am tomorrow.
As I am grouping the results by device_timestamp i.e a day wise, it is not possible for me to get the data from 10 pm to 6 am using this query.
I tried a lot of things to make it work, but none of the solutions worked.
Please help me to know the correct approach.
