将PostgreSQL带有时区的时间戳在不同时区之间转换。

huangapple go评论59阅读模式
英文:

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

将PostgreSQL带有时区的时间戳在不同时区之间转换。

答案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';

huangapple
  • 本文由 发表于 2023年3月7日 15:13:04
  • 转载请务必保留本文链接:https://go.coder-hub.com/75658931.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定