英文:
Convert postgres timestamp with timezone between timezones
问题
I have data stored in a timestamp with timezone column (timestamptz) in UTC.
我有数据存储在一个带有时区的时间戳列(timestamptz),时区为UTC。
I want to query in a different timezone (Australia/Sydney +10) but retain the timezone.
我想在不同的时区(Australia/Sydney +10)进行查询,但保留时区信息。
SELECT
'2022-05-04 14:00:01.546+00'::timestamp AT time zone 'UTC',
timezone('Australia/Sydney', '2022-05-04 14:00:01.546+00'::timestamp AT time zone 'UTC')
;
This returns:
这将返回:
"2022-05-04 14:00:01.546+00" timestamp with time zone
"2022-05-05 00:00:01.546" timestamp without time zone
What I want is:
我想要的是:
"2022-05-05 00:00:01.546+10" timestamp with time zone
i.e. convert from +00 to +10 and retain the time zone. I keep finding examples that convert to local time without the timezone info. I'm trying to map to a pyarrow.Table and want to infer the correct type timestamp[ns, tz=Australia/Sydney]
rather than timestamp[ns, tz=UTC]
.
即从+00转换为+10并保留时区信息。我一直找到的示例都是将时间转换为本地时间,而没有时区信息。我正在尝试将其映射到pyarrow.Table,并希望推断出正确的类型 timestamp[ns, tz=Australia/Sydney]
而不是 timestamp[ns, tz=UTC]
。
英文:
I have data stored in a timestamp with timezone column (timestamptz) in UTC.
I want to query in a different timezone (Australia/Sydney +10) but retain the timezone
SELECT
'2022-05-04 14:00:01.546+00'::timestamp AT time zone 'UTC',
timezone('Australia/Sydney', '2022-05-04 14:00:01.546+00'::timestamp AT time zone 'UTC')
;
This returns:
"2022-05-04 14:00:01.546+00" timestamp with time zone
"2022-05-05 00:00:01.546" timestamp without time zone
What I want is:
"2022-05-05 00:00:01.546+10" timestamp with time zone
i.e. convert from +00 to +10 and retain the time zone. I keep finding examples that convert to local time without the timezone info. I'm trying to map to a pyarrow.Table and want to infer the correct type timestamp[ns, tz=Australia/Sydney]
rather than timestamp[ns, tz=UTC]
Edit: To make it clearer I've tried the following:
select
ts,
timezone('Australia/Sydney', ts),
ts at time zone 'Australia/Sydney'
from timeseries
limit 1
答案1
得分: 1
设置 timezone
参数为具有该UTC偏移的时区,才能获得输出 2022-05-05 00:00:01.546+10
。
英文:
The way PostgreSQL works, the only way to get the output 2022-05-05 00:00:01.546+10
is to set the timezone
parameter to a time zone with that UTC offset:
SET timezone = 'Australia/Sydney';
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论