PostgreSQL 将UTC时间戳转换为timestamptz

在本文中，我们将介绍如何在 PostgreSQL 中将以 UTC 时区表示的时间戳转换为带有时区信息的时间戳（timestamptz）。

阅读更多：PostgreSQL 教程

UTC 时间戳和 timestamptz 的区别

在 PostgreSQL 中，时间戳（timestamp）和带有时区信息的时间戳（timestamptz）是不同的数据类型。

• 时间戳（timestamp）：表示一个特定的时间点，不带时区信息。例如：’2022-01-01 10:00:00′
• 带有时区信息的时间戳（timestamptz）：表示一个特定的时间点，包含了时区信息。例如：’2022-01-01 10:00:00+00′

将 UTC 时间戳转换为 timestamptz

要将 UTC 时间戳转换为 timestamptz，我们可以使用 PostgreSQL 的内置函数 timezone 和 to_timestamp。

SELECT timezone('UTC', to_timestamp(1641024000));

在上面的示例中，我们将时间戳 1641024000 使用 to_timestamp 函数转换为一个时间值。然后，我们使用 timezone 函数将其转换为 UTC 时区。

示例解析

假设我们有一个表 orders，其中包含一个表示订单创建时间的 UTC 时间戳字段 created_at。

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  created_at INT
);

我们可以通过以下方式将 UTC 时间戳转换为 timestamptz。

SELECT id, timezone('UTC', to_timestamp(created_at)) AS created_at_utc
FROM orders;

在上述查询中，我们使用 to_timestamp 函数将 created_at 字段转换为时间戳，然后使用 timezone 函数将其转换为 UTC 时区的 timestamptz 值。我们使用别名 created_at_utc 来表示转换后的时间。

将 timestamptz 转换为本地时间

如果我们想要将 timestamptz 转换为本地时间，可以使用 AT TIME ZONE 子句和相应的时区。

SELECT created_at_utc AT TIME ZONE 'Asia/Shanghai' AS created_at_local
FROM (
  SELECT timezone('UTC', to_timestamp(created_at)) AS created_at_utc 
  FROM orders
) AS subquery;

在上述查询中，我们将 created_at_utc 使用 AT TIME ZONE 子句转换为 ‘Asia/Shanghai’ 时区的本地时间。

总结

本文介绍了如何在 PostgreSQL 中将以 UTC 时区表示的时间戳转换为 timestamptz。我们使用 timezone 和 to_timestamp 函数来进行转换，并演示了示例查询。

通过掌握这些函数和技巧，您可以在 PostgreSQL 中轻松地处理不同时区的时间，并准确地表示时间戳。使用正确的时区信息可以确保在全球范围内的应用程序中正确处理时间。