由 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
可能会非常有用