自人类开始记录数据以来，就一直在记录时间。时间管理是数据库任务中相当关键的一环，而 Postgres 在此方面表现非常出色。Postgres 提供了丰富的时间存储和查询功能，本文对其做一些简单的整理。
在 postgres 中查询当前时间
SELECT now();
将会得到以下格式的内容：
now
-----------------------------
2023-05-15 18:23:58.5603+00
这里的默认格式是一个完整的时间戳字符串，包含日期、时间和对时区的引用。在这种情况下，+00 表示等于 UTC。UTC 代表的是 Coordinated Universal Time（协调世界时），是一种基于原子钟的时间标准，是以原子时秒长为基础，在时刻上尽量接近于世界时的一种时间计量系统。
查询指定时区的时间
SELECT now() AT TIME ZONE 'America/Chicago';
你可以查阅系统表中的完整时区名称列表，通过执行SELECT * FROM pg_timezone_names;语句进行检索。
时间数据类型
Postgres 有一个TIME数据类型，如果你想将时间与日期分开存储，可以选择带有或不带时区的数据类型。通常不建议这样做，因为在大多数情况下，时间需要附带日期。还有一种TIMESTAMP数据类型，TIMESTAMP WITH TIMEZONE可以将时区添加到 TIMESTAMP，别名为TIMESTAMPTZ。毫无疑问，TIMESTAMPTZ 是 PostgreSQL 时间存储的最佳选择。如果以完整的日期、时间和时区存储数据，就永远不必担心服务器时间、用户输入数据的时间、查询数据所在地的时间之间的转换问题。你或你的应用程序可以获取时间，并根据本地用户的所在地时区，将时间转换成所需的指定时区显示。
在使用 Postgres 时，你还会看到 epoch 这个概念，它表示秒数。这不是时间戳，而是一个整数（双精度浮点数，64位），它代表自1970年1月1日以来经过的秒数。如果你需要进行特定的比较或需要以该格式表示时间，则可以使用它。Postgres 可以轻松地在时间戳和epoch之间进行转换。要查找当前的epoch，请执行以下操作：
SELECT EXTRACT (EPOCH FROM now());
时间格式和函数
美国人习惯将日期写成这样：7月 14日, 1789 or 7-14-1789。法国则写成这样：14日 7月 1789 or 14-07-1789。国际标准化组织ISO为时间格式制定了一些标准，即ISO 8601，它规定日期应该这样表示1789-07-14 17:30:00.000，即年-月-日-时间。这种日期格式就是 TIMESTAMP 中使用的格式，也是在数据库和工程领域中最常见的表示方式。
时间存储是使用 ISO8601 的最佳实践，但是，根据你的最终用户或业务需求，可能希望在查询结果输出时更改时间格式。因此，为了改变查询的时间格式，可以使用TO_CHAR函数，它将把时间字符串转换为不同的字符。
SELECT TO_CHAR(NOW(), 'DY, Mon dd, yyyy HH24:MI:SS OF');
TO_CHAR允许你将时间间隔字符串转换为文本和字符。然后使用一些格式化函数，可以提取一周的某一天、美国的日期格式和UTC时间。该查询的结果将是：
MON, May 15, 2023 14:22:28 +00
时间间隔
Postgres可以按照我们想要的任何格式获取日期，那么如何以不同格式计算时间间隔和时间差呢？
加载一个包含部分列车时刻表数据的示例表
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分钟以上的出发时间，我们可以添加 interval > '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;
重叠/交叉时间
如果想找到在特定时间或现在正在运行的所有列车。你可以使用INTERVAL与OVERLAP运算符。
SELECT count(*) FROM train_schedule
WHERE (actual_departure, actual_arrival)
OVERLAPS (now(), now() - INTERVAL '2 hours');
时间范围类型
Postgres还支持使用包括单个时间范围和多个时间范围在内的时间范围操作。timestamptz的单个范围称为tstzrange，而多个范围则称为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();
为类型是时间的列创建索引
在 PostgreSQL 数据库中，当频繁查询时间时，建议为相应的列添加索引，以优化时间查找速度。时间戳列索引既适用于传统的 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;
聚合并不是处理大量时间数据的唯一方法。如果你大量可划分的时间数据，Partitioning 可能非常有用。如果你开始测量分析或指标，则还有其他方法，例如 hyperloglog。
总结
感谢你花时间学习时间操作，以下是一些要点：
- 将时间以 UTC +/- 值形式存储
- timestamptz 函数是你最好的朋友
- to_char 以及所有格式化函数都可以让你按照所需方式查询时间
- Postgres 拥有许多用于 interval 和 overlap 的函数，因此你可以查看交叉的数据
- 如果你想按天或月统计时间字段，请使用 date_trunc 函数，这非常有用
