时区处理：TIMEZONE

假设你有个订机票的app。航班从纽约当地时间10:00起飞，到伦敦当地时间22:00到达。如果你不考虑时区，你的服务器可能会把一切搞得一团糟，显示错误的到达时间。

时区——要么是你最靠谱的朋友，要么是最坑爹的敌人（当一切不按计划走的时候）。如果你的用户分布在不同国家，或者你要处理依赖本地时间的日程（比如航班时间表或活动安排），那时区处理就变得超级重要。

时间数据类型

我们之前聊过，处理时间戳有两种数据类型：

• TIMESTAMP：日期和时间不带时区信息。
• TIMESTAMPTZ：日期和时间带时区信息。

我们再举个例子来说明一下。

-- 创建一个有两列的表：TIMESTAMP和TIMESTAMPTZ
CREATE TABLE flight_schedule (
    flight_id SERIAL PRIMARY KEY,
    departure_time TIMESTAMP,
    departure_time_with_tz TIMESTAMPTZ
);

-- 插入数据
INSERT INTO flight_schedule (departure_time, departure_time_with_tz)
VALUES
    ('2023-10-25 10:00:00', '2023-10-25 10:00:00+00');

-- 检查数据
SELECT * FROM flight_schedule;

结果会根据你服务器的时区而变化。比如：

关键区别：

• departure_time这一列只是存了日期和时间，没和任何时区绑定。
• departure_time_with_tz这一列存了日期和时间，还有时区信息（这里是+00）。

时间在不同的时区之间转换

在PostgreSQL里，处理时区用的是AT TIME ZONE函数。

把UTC时间转成本地时间

假设我们有个UTC格式的时间戳（世界协调时间）。我们想把它显示给在America/New_York时区的用户。

SELECT
    '2023-10-25 14:00:00+00'::TIMESTAMPTZ AT TIME ZONE 'America/New_York' AS local_time;

结果：

AT TIME ZONE就像魔法棒一样：它把UTC时间转换成你指定的时区时间。

把本地时间转成UTC

现在反过来：我们有America/New_York的本地时间，想把它转成UTC。

SELECT
    '2023-10-25 10:00:00'::TIMESTAMP AT TIME ZONE 'America/New_York' AS utc_time;

结果：

注意，结果是TIMESTAMPTZ格式，因为它带了时区信息（这里是UTC）。

用TIMESTAMPTZ数据类型

你用TIMESTAMPTZ的时候，PostgreSQL会自动考虑你服务器的时区（或者你自己设置的）。

你可以用下面的命令给当前会话设置时区：

SET TIMEZONE = 'Europe/Istanbul';

之后所有TIMESTAMPTZ的操作都会用这个时区。

例子：插入和查询数据

-- 设置时区
SET TIMEZONE = 'Europe/Istanbul';

-- 插入数据
INSERT INTO flight_schedule (departure_time_with_tz)
VALUES ('2023-10-25 10:00:00+00');

-- 查询数据
SELECT departure_time_with_tz FROM flight_schedule;

在Europe/Istanbul时区下的结果：

PostgreSQL会自动把UTC时间转换成你指定的时区。

实用例子

时区在日程表里的应用。 假设我们有个航班时刻表，每条记录都用UTC存储出发时间。我们想把每个航班的出发时间按本地时间显示出来。

SELECT
    flight_id,
    departure_time_with_tz AT TIME ZONE 'America/New_York' AS local_time
FROM flight_schedule;

对比不同城市的时间数据。 假如我们要比较两个在不同城市发生的事件。PostgreSQL能自动把数据转换到同一个时区来比较。

SELECT
    '2023-10-25 10:00:00+03'::TIMESTAMPTZ > '2023-10-25 07:00:00+00'::TIMESTAMPTZ AS event_one_later;

结果：

比较结果是true，因为10:00+03等于07:00+00。

小贴士和常见坑

处理时间——真的是个大坑。常见的翻车点有：

• 用TIMESTAMP而不是TIMESTAMPTZ，然后发现时间对不上——因为时区信息被无视了。
• 不知道服务器用的啥时区，结果插入和读取的数据时区不一样。
• 用AT TIME ZONE时时区名字写错——要么报错，要么时间不对。

想避坑的话：

• 几乎所有场景都用TIMESTAMPTZ，特别是数据和时区有关的时候。
• 时间最好都用UTC存，显示的时候再转成用户需要的时区。
• 想深入了解的话，这里有PostgreSQL官方关于时间和时区的文档——超级有用。