PostgreSQL 日期和时间操作

介绍

在这个实验中，你将探索 PostgreSQL 的日期和时间操作，重点关注处理时区和执行计算。你将首先把带有时区信息的时间戳插入到数据库表中，演示如何存储发生在不同地理位置的事件。

然后，本实验将指导你使用诸如 EXTRACT 和 INTERVAL 等函数来操作和查询日期和时间数据。你将学习如何跨时区查询数据，以及计算事件之间的时间差，从而为你提供在 PostgreSQL 中管理时序数据的实用技能。

Skills Graph

连接到 PostgreSQL 并创建 events 表

在这一步中，你将连接到 PostgreSQL 数据库并创建 events 表。该表将存储事件名称及其对应的时间戳，并包含时区信息。

首先，打开你的终端。要以 postgres 用户身份连接到 PostgreSQL 数据库，请执行以下命令：

sudo -u postgres psql

此命令将打开 PostgreSQL 交互式终端，也称为 psql shell。你将使用此 shell 执行 SQL 命令。

现在，让我们创建 events 表。执行以下 SQL 命令：

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(255),
    event_time TIMESTAMPTZ
);

此命令创建一个名为 events 的表，其中包含三列：

• id：一个 serial 主键，对于每个新事件自动递增。
• event_name：一个包含事件名称的字符串。
• event_time：一个带有时区信息的时间戳（TIMESTAMPTZ）。这种数据类型对于处理发生在不同时区的事件至关重要。

要验证表的创建，你可以使用以下命令描述表结构：

\d events

这将显示表模式（schema），确认 events 表已成功创建。

插入带时区信息的时间戳

在这一步中，你将把数据插入到 events 表中，并为每个事件指定时区。这将演示如何存储发生在不同地理位置的事件。

使用 psql shell（在上一步中连接），将以下数据插入到 events 表中：

INSERT INTO events (event_name, event_time) VALUES
('Conference Call', '2024-01-20 10:00:00-08 America/Los_Angeles');

INSERT INTO events (event_name, event_time) VALUES
('Project Meeting', '2024-01-20 18:00:00+00 Europe/London');

INSERT INTO events (event_name, event_time) VALUES
('Follow-up Meeting', '2024-01-21 12:00:00-08 America/Los_Angeles');

在这些 INSERT 语句中：

• '2024-01-20 10:00:00-08 America/Los_Angeles' 指定了 2024 年 1 月 20 日上午 10:00 的时间戳，时区为 America/Los_Angeles（UTC-8）。
• '2024-01-20 18:00:00+00 Europe/London' 指定了 2024 年 1 月 20 日下午 6:00 的时间戳，时区为 Europe/London（UTC+0）。

要验证数据插入，请查询 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)

请注意，PostgreSQL 将时间戳存储为 UTC（协调世界时）。当你插入带有时间戳的时区时，PostgreSQL 会将其转换为 UTC 并在内部存储。

使用 EXTRACT 和 AT TIME ZONE

在这一步中，你将学习如何使用 EXTRACT 函数提取时间戳的特定部分（如年、月、日），以及使用 AT TIME ZONE 子句将时间戳转换为不同的时区。

首先，让我们从 event_time 列中提取年份。执行以下查询：

SELECT event_name, EXTRACT(YEAR FROM event_time) AS event_year FROM events;

此查询使用 EXTRACT 函数从 event_time 列获取年份，并将其别名为 event_year。输出将是：

   event_name   | event_year
----------------+------------
 Conference Call |       2024
 Project Meeting |       2024
 Follow-up Meeting |       2024
(3 rows)

接下来，让我们看看如何使用 AT TIME ZONE 子句将 event_time 转换为特定的时区。要查看 America/Los_Angeles 时区的 event_time，请使用以下查询：

SELECT event_name, event_time AT TIME ZONE 'America/Los_Angeles' AS los_angeles_time FROM events;

此查询将 event_time 转换为 America/Los_Angeles 时区，并将其显示为 los_angeles_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 04:00:00
(3 rows)

你可以组合 EXTRACT 和 AT TIME ZONE 来提取特定时区中时间戳的特定组成部分。例如，要查找 America/Los_Angeles 时区中事件的小时数，请使用以下查询：

SELECT event_name, EXTRACT(HOUR FROM event_time AT TIME ZONE 'America/Los_Angeles') AS event_hour_la FROM events;

这将输出 America/Los_Angeles 时区中每个事件的小时数。

使用 INTERVAL 计算时间差

在这一步中，你将学习如何使用 INTERVAL 数据类型来计算两个事件之间的时间差。

要计算时间差，你可以从一个时间戳中减去另一个时间戳。结果将是一个表示差值的 INTERVAL 值。

让我们计算‘Follow-up Meeting’（后续会议）和‘Conference Call’（电话会议）之间的时间差。为此，你可以使用子查询或将表连接到自身。这是一个使用连接的示例：

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。然后，它从两个表中选择 event_name，并计算‘Follow-up Meeting’的 event_time 和‘Conference Call’的 event_time 之间的时间差。输出将是：

    event1     |     event2      |   time_difference
----------------+-----------------+---------------------
 Conference Call | Follow-up Meeting | 1 day 02:00:00
(1 row)

time_difference 列显示了两个事件之间的时间间隔（interval）。

你还可以使用 EXTRACT 函数从 INTERVAL 值中提取特定的组成部分。例如，要从时间差中提取天数，你可以使用以下查询：

SELECT
    e1.event_name AS event1,
    e2.event_name AS event2,
    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';

此查询从 time_difference 中提取天数，并将其显示为 days_difference。

最后，退出 PostgreSQL shell：

\q

这将使你返回到终端。

总结

在这个实验中，你探索了 PostgreSQL 的日期和时间操作。你学习了如何：

• 创建一个包含 TIMESTAMPTZ 列的表，以存储包含时区信息的时间戳。
• 将数据插入到表中，为每个事件指定时区。
• 使用 EXTRACT 函数提取时间戳的特定部分，例如年、月或日。
• 使用 AT TIME ZONE 子句将时间戳转换为不同的时区。
• 使用 INTERVAL 数据类型计算两个时间戳之间的时间差。

这些技能对于在 PostgreSQL 中管理时态数据至关重要，尤其是在处理发生在不同地理位置的事件时。