Search code examples
sqlpostgresqlgroup-bytimezonepostico

Group by time with timezone conversion in Postgresql

I am working with time data that is currently stores in UTC but I want it to be in PST, which is 8 hours behind. I have a pretty lengthy and involved query, but the only thing I am interested in is the time right now so I have included those parts. I want to convert the times to PST and then group by the date for the last week of data. The query has the following structure:

select
 date_trunc('day', time1) AT TIME ZONE 'US/Pacific'
 ...
where 
 time1 AT TIME ZONE 'US/Pacific' > now() AT TIME ZONE current_setting('TimeZone') -  INTERVAL '168 HOURS'
 ...
group by date_trunc('day', time1)

This results in the following time groupings. From my understanding, it groups from the 0:00 UTC, which is 16:00 in PST. However, I want the groupby to start at 0:00 PST. How do I do this? Right now, the counts in each group are misleading for each day because they go from 4 pm to 4 pm instead of 12 am to 12 am. For example, Sundays have uncharacteristically high counts because Sunday includes part of Monday's data in the groupby. I would appreciate any input to fix this issue. Thank you.

enter image description here

Solution

  • The answer depends on whether it is a timestamp with time zone or one without:

    1. If it's a timestamp with time zone, you can convert to PST with select time1 AT TIME ZONE 'US/Pacific' and get the date with select date_trunc('day', time1 AT TIME ZONE 'US/Pacific')

    2. If it's a timestamp without time zone stored in UTC that you want to convert, you first have to tell PostgreSQL to interpret it as UTC, then convert it, like so: select (time1 AT TIME ZONE 'Z') AT TIME ZONE 'US/Pacific' and of course you can get the date with select date_trunc('day', (time1 AT TIME ZONE 'Z') AT TIME ZONE 'US/Pacific')

    In either case you have to convert time zones before truncating to the day level or you may end up with inaccurate results.