了解 Postgres extract() 函数

免费试用

立即开始增强您的 PostgreSQL。

A female developer looking at her computer screen. There's an elephant on it.

作者：Timescale 团队

PostgreSQL extract() 函数允许您从日期中提取特定单位。如果您只需要年份、月份或日期，可以使用 extract() 访问这些值。

语法

extract(<字段> FROM <时间戳 | 时间间隔>) 以下是您可以提取的字段列表

世纪
日期
十年
dow（星期几，星期日为 0，星期六为 6）
doy（一年中的哪一天）
纪元
小时
isodow（星期几，星期一为 1，星期日为 7）
isoyear
儒略历
微秒
千年
毫秒
分钟
月份
季度
秒
时区
时区小时
时区分钟
星期
年份

示例

对于第一组示例，我们将使用从两个城市收集的五天内的气象数据。

测量时间	城市	温度	降水量
2021-09-01	迈阿密	65.30	0.28
2021-09-01	亚特兰大	63.14	0.20
2021-09-02	迈阿密	64.40	0.79
2021-09-02	亚特兰大	62.60	0.59
2021-09-03	亚特兰大	62.60	0.39
2021-09-03	迈阿密	71.60	0.47
2021-09-04	迈阿密	68.36	0.00
2021-09-04	亚特兰大	67.28	0.00
2021-09-05	迈阿密	72.50	0.00
2021-09-05	亚特兰大	70.80	0.00

提取月份、年份和日期

使用 extract() 非常简单。以下查询将日期列的日期、月份和年份分解为结果集中的单独列。请注意，我们为每个提取的字段设置了别名，然后可以按其中一个别名对结果进行排序。

SELECT
	EXTRACT(DAY FROM measured) "day",
	EXTRACT(MONTH FROM measured) "month",
	EXTRACT(YEAR FROM measured) "year",
	city,
	temperature
FROM city_data
ORDER BY "day";

结果如下

日期	月份	年份	城市	温度
1	9	2021	迈阿密	65.30
1	9	2021	亚特兰大	63.14
2	9	2021	亚特兰大	62.60
2	9	2021	迈阿密	64.40
3	9	2021	亚特兰大	62.60
3	9	2021	迈阿密	71.60
4	9	2021	迈阿密	68.36
4	9	2021	亚特兰大	67.28
5	9	2021	迈阿密	72.50
5	9	2021	亚特兰大	70.80

按提取值分组

最后一个示例展示了 extract() 的简单用法，用于从查询返回的结果中提取各种单位；我们可以按这些提取的值进行排序。您还可以按这些值进行分组，以根据它们进行聚合查询。

以下是一种获取每天两个城市之间平均温度的方法。虽然我们可以直接按日期本身进行分组，但这演示了如何使用 extract() 对包含日期的表执行更高级的查询。

SELECT
	EXTRACT(DAY FROM measured) "day",
	avg(temperature)
FROM city_data
GROUP BY "day"
ORDER BY "day";

结果如下

日期	平均值
1	64.22
2	63.50
3	67.10
4	67.82
5	71.65

高级示例

对于下一个示例，我们将使用名为 conditions 的表中的数据，该表如下所示。它包含来自 200 个独立传感器的温度和湿度信息，每两分钟采集一次。

时间	设备 ID	温度	湿度
2016-11-16 16:18:00.000 -0600	weather-pro-000279	42	48
2016-11-16 16:18:00.000 -0600	weather-pro-000000	42	54.6
2016-11-16 16:18:00.000 -0600	weather-pro-000001	42	54.4
2016-11-16 16:18:00.000 -0600	weather-pro-000002	42	55.2
2016-11-16 16:18:00.000 -0600	weather-pro-000003	42	52.7
2016-11-16 16:18:00.000 -0600	weather-pro-000004	70	49

在此查询中，我们要获取每天每小时每个设备的平均温度，因此我们从 time 列中提取小时数，并按小时数和 device_id 对结果进行分组。

SELECT
  device_id,
  EXTRACT(hour from time) as hours,
  avg(temperature)
FROM conditions
GROUP BY hours, device_id;

以下是一些结果示例

设备 ID	小时	平均值
weather-pro-000000	0	38.0599999999999446
weather-pro-000001	0	37.9699999999999428
weather-pro-000002	0	36.9233333333332606
weather-pro-000003	0	36.8799999999999271
weather-pro-000004	0	61.9766666666666389
weather-pro-000005	0	62.7766666666666493
weather-pro-000006	0	37.8733333333332746

以下是使用相同数据集的另一个示例，该示例统计了每 10 分钟间隔内进行的读数次数。我们可以使用 trunc() 函数按 10 分钟间隔聚合读数。

SELECT
  EXTRACT(hour from time) as hours,
  trunc(EXTRACT(minute from time) / 10)*10 AS ten_mins,
  COUNT(*)
FROM conditions
GROUP BY hours, ten_mins;

虽然此表中的传感器读数非常一致，但如果其中一个计数错误，则表明传感器可能出现故障或存在网络问题。

以下是结果的第一部分

小时	十分钟	计数
0	0	5000
0	10	5000
0	20	5000
0	30	5000
0	40	5000

后续步骤

要了解更多关于在 PostgreSQL 中使用 extract() 的信息，您可以阅读我们关于日期/时间函数的文章。有关如何在您自己的 TimescaleDB SQL 查询中使用 extract() 的更多示例，请参阅以下 Timescale 文档部分