Postgresql：如何将时间戳列转换为UTC时区列的时间戳？-解网

问：

我的数据库中有一个列，当前存储假定为 UTC 的时间。我想将此列升级为 a，并使时间戳保持完全相同，无论数据库在哪个时区下运行。timestamptimestamp with time zone

我们的本地系统在UTC中运行，因此简单对我们来说工作正常;但我想确保我们客户的任何系统也可以升级，并维护数据的UTC时区，即使它们可能在系统上以非UTC运行。alter table tab alter col type timestamp with time zone

这里的文档指出使用 AT TIME ZONE，但这样做需要我指定一个时区，这样做似乎无论如何都会改变日期。

例如，如果我假设我的时间是 UTC，并且我这样做了，那么时间戳就会从转移到at time zone 'UTC-4:00'2023-08-21 00:00:00.0000002023-08-20 20:00:00.000000 +00:00

我怎样才能让它保持与系统的时区无关？2023-08-21 00:00:00.000000 +00:00

为清楚起见，请编辑：

我的服务器目前处于 UTC 状态。
我的列是timestamp without time zone
数据被“作为”UTC插入到数据库中
我需要将该列转换为，以便查询在将本地时间移位后返回正确的时间。timestamp with time zone
- 我从 Java 查询数据库，因此我从数据库中返回的假设时间戳实际上是我需要的时间戳。java.sql.Timestamp2023-08-21T00:00:00.000000-4:002023-08-20T20:00:00.000000-4:00

当我执行以下语句作为测试以查看其行为方式时，数据库中的数据以“错误”的方式移动。

alter table tab
    alter col type timestamp with time zone
using col at time zone 'UTC-4:00';

我本来希望该列包含该值，但它实际上包含，所以当我在 Java 中读出它时，它甚至可以追溯到 .2023-08-21T04:00:00.000000Z2023-08-20T20:00:00.000000Z2023-08-20T16:00:00.000000-4:00

所以我要问的是，我怎样才能告诉 PostgreSQL 列中的数据是什么，而不是我想要的数据是什么？该语句似乎将“到”时区转换为“，而不是”从“时区。at time zone

PostgreSQL 日期时间

数据类型不会调整/考虑不同的时区; 确实如此。看来你所拥有的就是你想要的。如果你需要改变类型，那么也许你可以存储为（不要假设它是UTC），然后检索（选择）指定。我不会考虑将类型从更改为作为升级，只是使用具有相应不同数据语义的不同数据类型。timestamp without time zonetimestamp with time zoneat timezone utcat timezone utctimezonetimezonetz

0赞 Mirrana 8/22/2023

@Belayer我更新了这个问题以使其更清晰。

0赞 Panagiotis Kanavos 8/22/2023

@Mirrana这实际上更令人困惑。UTC 表示。一列将包含，而不是。在 UTC，实际上是 UTC-4 前一个日期的 20：00。这意味着比 UTC 晚 4 小时。你是怎么做到的？您似乎正在再次转换为本地时间00:00without time zone2023-08-21T00:00:00.0000002023-08-21T00:00:00.000000-4:0000:00-4when I read it out in Java,

0赞 Panagiotis Kanavos 8/22/2023

也许你想要的是UTC*+4*？如果该值已经是 UTC，为什么不将其保留在该时区中？比较始终考虑存储的偏移量。您在转换中使用的偏移量也不会影响将来存储的任何值。您可以比较和排序这些值，无论它们包含什么偏移量using col at time zone 'UTC'timestamp with time zone

0赞 Adrian Klaver 8/22/2023

输入到字段中的值将使用值中包含的时区/偏移量旋转为 UTC，如果没有时区/偏移量，则使用时区设置。检索时，该过程将被逆转。与名称相反，原始值的时区不会保存，因此无法恢复。您需要在客户端使用检索时处理该问题。timestamptzAT TIME ZONE

答：

1赞 jjanes 8/22/2023 #1

无论系统的时区如何，我怎样才能让它停留在 2023-08-21 00：00：00.000000 +00：00？

你不能。在发送数据时，时间戳将始终转换为连接时区。因此，除非连接时区是 UTC 或同义词，否则不会使用 +00 发送。这就是 timestamptz 的作用。如果你不想这样，那么就不要使用时间戳。

如果您刚刚使用 AT TIME ZONE 'UTC' 完成了 ALTER TABLE 命令，那么发送到客户端的新内容将等同于“2023-08-21 00：00：00.000000+00”，它只是按照客户端更喜欢其时间戳值的拼写方式拼写，例如“2023-08-20 20：00：00-04”。

事实证明这是正确的。我很困惑，因为事实证明，无论服务器的时区是什么，我的客户端都在UTC中。因此，在对不同时区的临时数据库进行一些测试后，我能够确认在不更改预期值的情况下正确转换列数据。alter col type timestamptz using col at time zone 'UTC'

上一个：解析 timestamp 以考虑时区

下一个：查询将时间戳四舍五入到最接近的 10 分钟

Postgresql：如何将时间戳列转换为UTC时区列的时间戳？

Postgresql: How to convert a timestamp column to a timestamp with time zone column at UTC?

为清楚起见，请编辑：

评论

评论