How to date_trunc with offset?

user_9072849 0
Viewed 169

I have an api service which returns data with an offset of 1 hour forward aprx. It's not timezone it's the way they aggreagate data for previous date and it happens during first hour of following date after midnight. So if I date_trunc by day and group it this way then I lose some portion of data which comes after midnight. The task is to group this data for current date with offset in mind skipping starting hour from current date and grab data from following date as if it were this current date.

How would you approach this case?

Thanks.

clickhouse postgresql sql
1 Answers

Just subtract one hour:

select date_trunc('day', column - interval '1 hour')

You can include this in an aggregation query using something like this:

select date_trunc('day', column - interval '1 hour') as the_day,
       count(*)
from t
group by the_day;
user_11440350
Related