Группировка по времени с преобразованием часового пояса в Postgresql

Question

Я работаю с данными о времени, которые в настоящее время хранятся в UTC, но я хочу, чтобы они были в формате PST, который отстает на 8 часов. У меня довольно длинный и сложный запрос, но меня сейчас интересует только время, поэтому я включил эти части. Я хочу преобразовать время в PST, а затем сгруппировать по дате последней недели данных. Запрос имеет следующую структуру:

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)

Это приводит к следующим временным группировкам. Насколько я понимаю, он группируется с 0:00 UTC, то есть 16:00 по тихоокеанскому времени. Тем не менее, я хочу, чтобы группа начала в 0:00 по тихоокеанскому времени. Как мне это сделать? Прямо сейчас подсчеты в каждой группе вводят в заблуждение за каждый день, потому что они идут с 16:00 до 16:00 вместо 12:00 до 12:00. Например, воскресенья имеют нехарактерно высокие показатели, потому что воскресенье включает часть данных за понедельник в группу. Я был бы признателен за любой вклад, чтобы исправить эту проблему. Спасибо.

Answer 1

Ответ зависит от того, является ли это меткой времени с часовым поясом или без:

1. Если это временная метка с часовым поясом, вы можете преобразовать в PST с select time1 AT TIME ZONE 'US/Pacific' и получить дату с select date_trunc('day', time1 AT TIME ZONE 'US/Pacific')

2. Если вы хотите преобразовать временную метку без часового пояса в UTC, сначала вы должны указать PostgreSQL интерпретировать ее как UTC, а затем преобразовать ее следующим образом: select (time1 AT TIME ZONE 'Z') AT TIME ZONE 'US/Pacific' и, конечно, вы можете получить дату с select date_trunc('day', (time1 AT TIME ZONE 'Z') AT TIME ZONE 'US/Pacific')

В любом случае вам нужно конвертировать часовые пояса перед усечением до уровня дня, иначе вы можете получить неточные результаты.