使用 PostgreSQL 处理时间

由 John Doe 十月 24, 2024

摘要：在本文中，我们将学习如何在 PostgreSQL 中处理时间值。

介绍

自从人类第一次开始记录数据以来，他们一直在跟踪时间。时间管理是绝对关键的数据库任务之一，而 Postgres 在这方面做得很好。Postgres 有很多用于存储和查询时间的方法，因此本文将概述存储和检索时间数据的一些最常见需求。

如果你问 Postgres 现在什么时间，

SELECT now();

您将获得

              now
-------------------------------
 2024-10-23 12:34:25.084845+00

此处默认的时间表示形式，是一个完整的时间戳字符串，其中包含日期、时间和时区的引用。在这种情况下，+00 表示与 UTC 相等。长期以来，UTC 一直是格林威治标准时间的标准时间测量。

如果我想知道我本地时区的时间

SELECT now() AT TIME ZONE 'Asia/Shanghai';

您可以使用的时区名称的完整列表存储在系统表中，可以使用select * from pg_timezone_names进行检索。

用于时间的数据类型

如果您想将时间与日期分开存储，Postgres 中有一个TIME数据类型，可以带时区也可以不带时区。通常不建议这样做，因为在大多数情况下，时间需要一个相随的日期。有一个数据类型TIMESTAMP。要给TIMESTAMP添加时区的话，可以用TIMESTAMP WITH TIMEZONE，或者别名TIMESTAMPTZ。毫无疑问，TIMESTAMPTZ将成为 Postgres 时间存储的最佳选择。如果您以完整的日期、时间和时区存储数据，则永远不必担心服务器时间、用户输入数据的时间、查询数据或任何那些复杂计算时的时间。您或您的应用程序可以提取时间，并以您需要的任何本地用户时区显示它。

使用 Postgres 时，您还会看到 epoch，这是秒的表示方式。这不是时间戳，而是一个整数（双精度浮点数，64 位），它表示自 1970 年 7 月 1 日以来的秒数。如果您需要进行特定的比较，或需要该格式的时间，可以使用此方法。Postgres 可以轻松地在时间戳和 epoch 之间来回转换。要查找当前 epoch：

SELECT EXTRACT (EPOCH FROM now());

时间格式和函数

ISO 有一些时间格式标准，即 ISO 8601，它规定日期将如下所示 1949-10-01 12:00:00.000，年-月-日-时间。这种日期格式是 TIMESTAMP 中使用的格式，也是您在数据库和工程领域最常看到的日期格式。

时间存储具有 ISO8601 最佳实践，但是，根据您的最终用户或业务需求，您可能希望更改查询中的时间格式，以输出查询。因此，要更改查询的时间格式，您可以使用TO_CHAR函数，将时间字符串转换为不同字符。

SELECT TO_CHAR(NOW(), 'DY, Mon dd, yyyy HH24:MI:SS OF');

TO_CHAR用于将时间间隔字符串转换为文本字符。然后，使用一些格式化函数，我可以提取星期几、中国日期格式和 UTC 时间。该查询的结果将是：

            to_char
--------------------------------
 WED, Oct 23, 2024 12:33:46 +00

时间间隔

既然我们已经很清楚，可以得到任何我们想要的格式的日期，那么如何计算不同格式的间隔和用时呢？

我们已经用一些火车时刻表数据，加载到了一个示例表中，请看一看

SELECT * FROM train_schedule LIMIT 3;

它看起来像这样

 trip_id | track_number | train_number |  scheduled_departure   |   scheduled_arrival    |    actual_departure    |     actual_arrival
---------+--------------+--------------+------------------------+------------------------+------------------------+------------------------
       1 |            1 |          683 | 2023-04-29 11:15:00+00 | 2023-04-29 12:35:00+00 | 2023-04-29 11:21:00+00 | 2023-04-29 12:52:00+00
       2 |            1 |          953 | 2023-04-29 13:49:00+00 | 2023-04-29 15:10:00+00 | 2023-04-29 13:50:00+00 | 2023-04-29 15:17:00+00
       3 |            1 |          140 | 2023-04-29 15:06:00+00 | 2023-04-29 15:23:00+00 | 2023-04-29 15:06:00+00 | 2023-04-29 15:22:00+00
(3 rows)

假设您要存储 update_time 字段。要找到你数据集中到达时间的下限和上限，您可以执行下面操作

SELECT min(actual_arrival) FROM train_schedule;

和

SELECT max(actual_arrival) FROM train_schedule;

要找出它们之间的间隔，请执行下面操作：

SELECT
  (SELECT max(actual_arrival) FROM train_schedule)
    -
  (SELECT min(actual_arrival) FROM train_schedule);

好的，我们这里有大约 10 天的火车时刻表信息。

更进一步，如果我想查看计划出发时间和实际出发时间之间的间隔。我可以创建一个 arrival_delta 和一个子查询，以比较实际到达时间与计划到达时间。

SELECT avg(arrival_delta) 
FROM
(SELECT scheduled_arrival, actual_arrival,
   actual_arrival - scheduled_arrival AS arrival_delta 
 FROM train_schedule) q;

您还可以添加过滤条件来查找间隔大小。如果我们基于上述查询，但仅针对比原计划时间晚 10 分钟以上的发车，我们可以将添加条件：间隔 > ‘10 minutes’。

SELECT avg(arrival_delta) 
FROM
(SELECT scheduled_arrival, actual_arrival,
   actual_arrival - scheduled_arrival AS arrival_delta 
 FROM train_schedule 
 WHERE (actual_arrival - scheduled_arrival) > INTERVAL '10 minutes') q;

重叠/相交时间

如果我想查找在特定时间或现在运行的所有火车，该怎么办。您可以将 OVERLAP 运算符与 INTERVAL 一起使用。

SELECT count(*) FROM train_schedule
WHERE (actual_departure, actual_arrival)
OVERLAPS (now(), now() - INTERVAL '2 hours');

时间范围类型

Postgres 还支持处理包括单个范围甚至多个范围的时间范围。单个范围的 timestamptz 叫做tstzrange，多个范围的 timestamptz 为tstzmultirange。

例如，如果我们想在火车数据库中，创建一个包含一些旅游旺季票价的表，我们可以这样做：

CREATE TABLE fares (
peak_id int,
peak_name text,
peak_times tstzmultirange,
fare_change numeric);

INSERT INTO fares(peak_id, peak_name, peak_times, fare_change) VALUES
(1, 'holiday', '{[2023-12-24 00:00:, 2023-12-27 00:00],[2023-12-31 00:00, 2024-01-02 00:00]}', 50),
(1, 'peak_summer', '{[2023-05-27 00:00:, 2023-05-30 00:00],[2023-07-03 00:00, 2023-08-30 00:00]}', 30);

现在，要查询具有多时区范围的内容，Postgres 有一个特殊的运算符@>。让我们看看今天的旅行是否在高峰时段。

SELECT * from fares WHERE peak_times @> now();

时间列的索引

每当你要大量查询时间时，你都需要添加一个索引，以便更快地进行时间查找。时间戳列的索引，可以使用传统的 B 树索引以及 BRIN 索引。一般来说，如果您按顺序输入了大量数据，则可能建议使用 BRIN 索引。

可以这样创建 B 树索引：

CREATE INDEX btree_actual_departure ON train_schedule (actual_departure);

还有 BRIN 索引

CREATE INDEX brin_sequential ON train_schedule USING BRIN (actual_departure);

汇总

假设您有相当多的时间数据。使用date_trunc函数，您可以轻松地按天或日期提取时间戳数据，然后您可以使用查询按天或日期进行计数。

如果我想在我的火车数据中，查找每天的火车行程计数，可以这样做：

SELECT
  date_trunc('day', train_schedule.actual_departure) d,
  COUNT(actual_departure)
FROM train_schedule
GROUP BY d
ORDER BY d;

汇总不会是处理大量时间数据的唯一方法。一旦您有大量时间数据可以轻松划分，分区就会非常有帮助。如果你要做测量分析或指标分析，也有一些方法，比如 hyperloglog。

总结

感谢您花时间来了解时间。一些总结

以 UTC +/- 值来存储时间
timestamptz是最佳的时间类型
to_char和所有格式化函数，可以让您根据需要来查询时间
Postgres 有很多interval和overlap方面的函数，因此您可以查看相交的时间值
如果您想汇总时间字段并按天或月计数，则date_trunc可能会非常有用

介绍