在本实验中，你将探索 PostgreSQL 强大的日期和时间操作功能。你将学习如何处理包含时区信息的时间戳，这是处理来自不同地理位置数据的应用程序的关键技能。
你将首先创建一个用于存储事件数据的表，使用 TIMESTAMPTZ 数据类型来确保时区感知。然后，你将插入发生在不同时区的事件数据，并学习 PostgreSQL 如何规范化这些数据。最后，你将练习使用 EXTRACT、AT TIME ZONE 子句等函数以及通过 INTERVAL 计算时间差来查询和操作这些时间数据。
在此步骤中，你将连接到 PostgreSQL 数据库并创建一个表来存储事件信息。此表将使用 TIMESTAMPTZ 数据类型，这对于准确存储来自不同时区的时间戳至关重要。
理解 TIMESTAMPTZ
PostgreSQL 中的 TIMESTAMPTZ (timestamp with time zone) 数据类型旨在处理来自世界各地日期和时间。当你插入一个具有特定时区的值时，PostgreSQL 会将其转换为协调世界时 (UTC) 进行存储。这种标准化使得无论原始时区如何，都可以轻松地比较和计算时间。
步骤 1: 连接到 PostgreSQL
首先，打开一个终端。使用 psql 交互式 shell 连接到 PostgreSQL 数据库。你将在该 shell 中执行所有后续的 SQL 命令。
sudo -u postgres psql你现在将看到 PostgreSQL 提示符，它看起来像 postgres=#。
步骤 2: 创建 events 表
现在，执行以下 SQL 命令来创建 events 表。此表将存储每个事件的 ID、名称和时间。
CREATE TABLE events (
id SERIAL PRIMARY KEY,
event_name VARCHAR(255),
event_time TIMESTAMPTZ
);此命令创建一个名为 events 的表，其中包含三个列：
id: 一个自动递增的主键。event_name: 用于事件名称的文本字段。event_time: 一个支持时区的时间戳。步骤 3: 验证表创建
为了确认表已正确创建，你可以使用 \d 命令检查其结构。
\d events你应该会看到 events 表的结构，确认其列和数据类型。
Table "public.events"
Column | Type | Collation | Nullable | Default
------------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('events_id_seq'::regclass)
event_name | character varying(255) | | |
event_time | timestamp with time zone | | |
Indexes:
"events_pkey" PRIMARY KEY, btree (id)你已成功设置用于存储基于时间的数据的表。
在此步骤中，你将向 events 表插入记录。每条记录都将有一个与特定时区关联的时间戳，演示 PostgreSQL 如何处理来自不同地理位置的数据。
步骤 1: 插入事件数据
在 psql shell 中，执行以下 INSERT 语句将三个事件添加到你的表中。请注意每个 event_time 如何包含一个时区标识符。
插入一个来自洛杉矶 (UTC-8) 的会议电话事件：
INSERT INTO events (event_name, event_time) VALUES
('Conference Call', '2024-01-20 10:00:00 America/Los_Angeles');插入一个来自伦敦 (UTC+0) 的项目会议：
INSERT INTO events (event_name, event_time) VALUES
('Project Meeting', '2024-01-20 18:00:00 Europe/London');插入一个同样来自洛杉矶的跟进会议：
INSERT INTO events (event_name, event_time) VALUES
('Follow-up Meeting', '2024-01-21 12:00:00 America/Los_Angeles');步骤 2: 验证数据插入
要查看 PostgreSQL 如何存储这些数据，请查询 events 表。
SELECT * FROM events;你应该会看到以下输出。
id | event_name | event_time
----+-------------------+------------------------
1 | Conference Call | 2024-01-20 18:00:00+00
2 | Project Meeting | 2024-01-20 18:00:00+00
3 | Follow-up Meeting | 2024-01-21 20:00:00+00
(3 rows)请注意，所有 event_time 值都显示为 UTC (由 +00 偏移量指示)。例如，洛杉矶 (UTC-8) 的 10:00:00 被转换为 UTC 的 18:00:00。这种内部转换使得能够进行一致的时间比较。
现在你已经将数据存储为 UTC，你可以根据需要查询并以任何时区显示它。在此步骤中，你将使用 EXTRACT 函数和 AT TIME ZONE 子句来操作和格式化你的时间戳。
步骤 1: 使用 EXTRACT 提取时间戳的各个部分
EXTRACT 函数允许你从时间戳中提取特定组件（如年、月、小时）。让我们获取每个事件的年份。
SELECT event_name, EXTRACT(YEAR FROM event_time) AS event_year FROM events;该查询从存储的 UTC 时间戳中提取年份。
event_name | event_year
-------------------+------------
Conference Call | 2024
Project Meeting | 2024
Follow-up Meeting | 2024
(3 rows)步骤 2: 使用 AT TIME ZONE 转换时间戳
要查看以特定本地时区存储的 UTC 时间，请使用 AT TIME ZONE 子句。让我们将所有事件时间转换回 America/Los_Angeles 时区。
SELECT event_name, event_time AT TIME ZONE 'America/Los_Angeles' AS los_angeles_time FROM events;此查询获取 UTC event_time 并显示其在洛杉矶的等效本地时间。
event_name | los_angeles_time
-------------------+---------------------
Conference Call | 2024-01-20 10:00:00
Project Meeting | 2024-01-20 10:00:00
Follow-up Meeting | 2024-01-21 12:00:00
(3 rows)如你所见，“Project Meeting”发生在 UTC 18:00:00，在洛杉矶时间中正确显示为 10:00:00。
步骤 3: 结合使用 EXTRACT 和 AT TIME ZONE
你可以组合使用这些功能来进行更强大的查询。例如，要查找每个事件在 Europe/London 时区发生时的小时：
SELECT event_name, EXTRACT(HOUR FROM event_time AT TIME ZONE 'Europe/London') AS event_hour_london FROM events;此查询首先将时间转换为伦敦时区，然后提取小时。
event_name | event_hour_london
-------------------+-------------------
Conference Call | 18
Project Meeting | 18
Follow-up Meeting | 20
(3 rows)一个常见的需求是计算两个事件之间的时间间隔。在 PostgreSQL 中，将一个时间戳减去另一个时间戳会得到一个 INTERVAL 数据类型，它表示一个时间段。
步骤 1: 计算两个事件之间的时间差
让我们找出“Conference Call”和“Follow-up Meeting”之间经过的时间。我们可以通过将 events 表与其自身连接来实现这一点。
SELECT
e1.event_name AS event1,
e2.event_name AS event2,
e2.event_time - e1.event_time AS time_difference
FROM
events e1
JOIN
events e2 ON e1.event_name = 'Conference Call' AND e2.event_name = 'Follow-up Meeting';此查询的工作原理如下：
events 表的两个虚拟实例，即 e1 和 e2。e1，只保留 'Conference Call' 行。e2，只保留 'Follow-up Meeting' 行。e2 的 event_time 减去 e1 的 event_time。结果是一个 INTERVAL 值。
event1 | event2 | time_difference
-----------------+-------------------+-----------------
Conference Call | Follow-up Meeting | 1 day 02:00:00
(1 row)时间差为 1 天 2 小时。
步骤 2: 从 INTERVAL 中提取组件
你也可以在 INTERVAL 上使用 EXTRACT 来获取时间段的特定部分，例如天数或小时数。让我们从之前的计算中只提取天数。
SELECT
EXTRACT(DAY FROM (e2.event_time - e1.event_time)) AS days_difference
FROM
events e1
JOIN
events e2 ON e1.event_name = 'Conference Call' AND e2.event_name = 'Follow-up Meeting';这将为你提供一个表示天数的数值。
days_difference
-----------------
1
(1 row)步骤 3: 退出 PostgreSQL
你已完成此实验的练习。现在你可以退出 psql shell 并返回主终端。
\q在此实验中，你探索了 PostgreSQL 中基本日期和时间操作。你已学会如何：
TIMESTAMPTZ 数据类型创建表，以正确存储时区感知信息。EXTRACT 函数从时间戳中检索特定组件，如年份或小时。AT TIME ZONE 子句以任何所需的本地时区显示存储的 UTC 时间。INTERVAL，并从中提取组件。这些技能对于构建能够跨不同区域准确管理时间数据的健壮应用程序至关重要。
