没有date/time类型存储时区,因为它是一个单独的信息。如果源数据带有时区偏移量,请不要使用普通的timestamp,因为它会被修剪掉-timestamptz同样轻便,同样灵活,而且它不会截断偏移量。如果您希望保留源偏移量/时区,你需要将它保存到一个单独的列中-时间戳是用来存储一个 when 而不是一个 where -后者只用于澄清前者。 如果偏移量或其他有效的时区信息存在于值literal/constant中,则它用于将时间戳转换为UTC以进行内部存储。当db在您select时将其读回给您时,它将根据您的timezone设置再次转换:demo at db<>fiddle
create table test(tstz timestamptz, ts timestamp);
insert into test
select '2012-08-24 14:00:00+03:00'::timestamptz,
'2012-08-24 14:00:00+03:00'::timestamp
returning *;
Postgres将所有TIMESTAMP WITH TIME ZONE输入调整为UTC,偏移量为零。检索到的值仍为UTC,偏移量为零。 如果需要原始偏移量,请在另一列中记录该信息。
Postgres调整为UTC(偏移量为零)
timestamp with timezone column'2012-08-24 14:00:00+03:00' and after trying to do a select i'm getting '2012-08-24 11:00:00+00 正如您所看到的,对于TIMESTAMP WITH TIME ZONE类型的列,Postgres使用输入的offset-from-UTC调整为0小时-分钟-秒的偏移量。 您的偏移量表示时间为14:00,比UTC提前3小时。因此Postgres将时间调整为11:00,偏移量为零。相同的时刻,时间轴上的相同点,不同的视角。
小心带有自动调整反功能的工具
Postgres检索偏移量为零的存储值。 不幸的是,许多工具选择动态地将一些默认时区应用到检索到的值上。虽然初衷是好的,但这个反功能混淆了图片,造成了一种错觉,即该时刻是以特定的时区或偏移量存储的。但是,不,在Postgres中,TIMESTAMP WITH TIME ZONE中的值 * 总是 * 以UTC存储(偏移量为零),并且 * 总是 * 以UTC检索(偏移量为零)。
2条答案
按热度按时间3ks5zfa01#
没有date/time类型存储时区,因为它是一个单独的信息。如果源数据带有时区偏移量,请不要使用普通的
timestamp
,因为它会被修剪掉-timestamptz
同样轻便,同样灵活,而且它不会截断偏移量。如果您希望保留源偏移量/时区,你需要将它保存到一个单独的列中-时间戳是用来存储一个 when 而不是一个 where -后者只用于澄清前者。如果偏移量或其他有效的时区信息存在于值literal/constant中,则它用于将时间戳转换为UTC以进行内部存储。当db在您
select
时将其读回给您时,它将根据您的timezone
设置再次转换:demo at db<>fiddle字符串
| 茨茨茨|ts|
| --|--|
| 2012-08-24 11:00:00+00| 2012-08-24 14:00:00|
当您选择
timestamptz
时,默认看到的偏移量是您当前的时区:它基本上意味着 * 这个时间戳,就像在一个具有这个偏移量的时区中观察到的那样。注意,除非你添加分钟:00
,否则它在默认输出和to_char()
formatting function中都被认为是无关紧要的。如果你真的想得到你指定的输出,出于一个只有你知道的原因,您可以通过各种方式简单地设置相应的设置:型
| 茨茨茨|ts|
| --|--|
| 2012-08-24 14:00:00+03| 2012-08-24 14:00:00|
型
| 充电|充电|
| --|--|
| 2012-08-24 02-00-00PM+03| 2012-08-24 02-00-00PM+00|
型
| 茨茨茨|ts|
| --|--|
| 2012-08-24 02:15:00-08:45| 2012-08-24 14:00:00|
型
| 充电|充电|
| --|--|
| 2012-08-24 02-15-00AM-08:45| 2012-08-24 02-00-00PM+00|
2skhul332#
tl;dr
Postgres将所有
TIMESTAMP WITH TIME ZONE
输入调整为UTC,偏移量为零。检索到的值仍为UTC,偏移量为零。如果需要原始偏移量,请在另一列中记录该信息。
Postgres调整为UTC(偏移量为零)
timestamp with timezone column'2012-08-24 14:00:00+03:00' and after trying to do a select i'm getting '2012-08-24 11:00:00+00
正如您所看到的,对于
TIMESTAMP WITH TIME ZONE
类型的列,Postgres使用输入的offset-from-UTC调整为0小时-分钟-秒的偏移量。您的偏移量表示时间为14:00,比UTC提前3小时。因此Postgres将时间调整为11:00,偏移量为零。相同的时刻,时间轴上的相同点,不同的视角。
小心带有自动调整反功能的工具
Postgres检索偏移量为零的存储值。
不幸的是,许多工具选择动态地将一些默认时区应用到检索到的值上。虽然初衷是好的,但这个反功能混淆了图片,造成了一种错觉,即该时刻是以特定的时区或偏移量存储的。但是,不,在Postgres中,
TIMESTAMP WITH TIME ZONE
中的值 * 总是 * 以UTC存储(偏移量为零),并且 * 总是 * 以UTC检索(偏移量为零)。不同数据库引擎的行为不同
SQL标准几乎没有处理日期时间问题,各种数据库中的许多日期时间处理行为都是特定于实现的。
一些其他的数据库引擎和Postgres做的一样,自动调整到零的偏移量。但是一些数据库引擎可能不会。一定要研究文档。
将偏移量存储到另一列
如果知道原始偏移量对你来说很重要,你需要将这个事实作为一个值保存在另一列中,我建议使用文本类型的列,以标准的ISO 8601存储偏移量值。