在这篇文章中，我将介绍一些按日期查询数据的关键函数。有关在 Postgres 中存储日期和时间的最佳方法的摘要.
interval——日期操纵的瑞士军刀
是interval用于修改其他时间的数据类型。例如，可以从已知时间中添加或减去间隔。间隔非常方便，是您按日期快速汇总数据的第一个方法。就像瑞士军刀一样，它并不总是完成工作的最佳工具，但它可以在紧急情况下使用。让我们谈谈它的优势所在。
我们如何运行查询来返回过去 90 天的订单总额？当然可以使用间隔。如果没有间隔，我们经常看到人们使用从生成日期的外部源传递的日期变量。使用now() - INTERVAL '90 days'，无论日期如何，您都可以使用相同的查询。另一个秘诀是使用now()服务器上当前时间的时间戳。
SELECT
SUM(total_amount)
FROM
orders
WHERE
order_date >= NOW () - INTERVAL '90 days';复制 sum
-----------
259472.99
(1 row)复制除了使用 之外now()，current_date还可以用来返回日期而不是时间。
SELECT
SUM(total_amount)
FROM
orders
WHERE
order_date >= current_date - INTERVAL '90 days';复制这两个查询不同——current_date从一天的开始开始，并now()包含全天的时间。使用时，now()结果将仅匹配当前时间 90 天后发生的结果。
通常，人们使用更短的形式来表示间隔，但这是相同的查询：
SELECT
SUM(total_amount)
FROM
orders
WHERE
order_date >= NOW() - '90 days'::interval;复制使用间隔进行分箱
要创建间隔范围，我们可以结合使用CASE和interval。 SQLCASE在查询中执行条件逻辑。 的格式为CASE，WHEN .. THEN下面是执行示例 case 语句的查询：
SELECT
CASE
WHEN false THEN 'not this'
WHEN true THEN 'this will show'
ELSE 'never makes it here'
END;复制现在，让我们将订单按时间范围分类：“30-60 天前”、“60-90 天前”
SELECT
CASE
WHEN order_date BETWEEN (NOW() - INTERVAL '60 days') AND (NOW() - INTERVAL '30 days')
THEN '30-60 days ago'
WHEN order_date BETWEEN (NOW() - INTERVAL '90 days') AND (NOW() - INTERVAL '60 days')
THEN '60-90 days ago'
END AS date_range,
COUNT(*) AS total_orders,
SUM(total_amount) AS total_sales
FROM
orders
WHERE
order_date BETWEEN (NOW() - INTERVAL '90 days') AND (NOW() - INTERVAL '30 days')
GROUP BY
date_range
ORDER BY
date_range;复制 date_range | total_orders | total_sales
----------------+--------------+-------------
30-60 days ago | 160 | 101754.20
60-90 days ago | 128 | 88086.24复制这可能看起来有点复杂，但该语句的条件是order_date BETWEEN begining_date_value AND ending_date_value。由于CASE语句在第一个真值条件后结束，我们可以进一步简化它：
SELECT
CASE
WHEN order_date >= NOW() - '30 days'::interval THEN '00-30 days ago'
WHEN order_date >= NOW() - '60 days'::interval THEN '30-60 days ago'
ELSE
'60-90 days ago'
END AS date_range,
COUNT(*) AS total_orders,
SUM(total_amount) AS total_sales
FROM
orders
WHERE
order_date >= NOW() - '90 days'::interval
GROUP BY
date_range
ORDER BY
date_range;复制最好根据 SQL 查询的明确程度来选择模式。使用BETWEEN更明确，可能最适合选择更明确查询的团队。使用困难的部分INTERVAL是最近时间大于较早时间 — 因此，这>=可能会让那些没有使用过大量时间操作的人绞尽脑汁。
总结：用于interval对连续时间进行分箱。
date_trunc - 最简单的日期分箱函数
用于date_trunc对预定义时间进行分箱：如日、周、月、季度和年。间隔逻辑可能很复杂，而date_trunc非常简单。
乍一看，date_trunc的名称可能表明它与格式化有关，但与 结合使用时功能更强大GROUP BY。date_trunc是处理分析时查询工具包的重要组成部分。date_trunc 的简单用法如下：
/* show the beginning of the first day of the month */
SELECT date_trunc('month', current_date);
/* show the beginning of the first day of the week */
SELECT date_trunc('week', current_date);
/* show the beginning of the first day of the year */
SELECT date_trunc('year', current_date);
/* show the beginning of the first day of the current quarter */
SELECT date_trunc('quarter', current_date);复制要生成日期箱，请从记录的日期中提取时间段。例如，让我们编写一个查询来显示每月的订单数量和订单总销售额：
SELECT
date_trunc ('month', order_date) AS month,
COUNT(*) AS total_orders,
SUM(total_amount) AS monthly_total
FROM
orders
GROUP BY 1
ORDER BY
month;复制结果如下：
month | total_orders | monthly_total
---------------------+--------------+---------------
2024-08-01 00:00:00 | 11 | 2699.82
2024-09-01 00:00:00 | 39 | 8439.41
(2 rows)复制使用GROUP BY，Postgres 根据函数返回的唯一值进行计数和求和date_trunc。可用的箱为date_trunc：千年、世纪、十年、年、季度、周、日、小时、分钟、秒、毫秒。
extract- 有时你必须做一些有趣的事情
并非所有日期都能很好地分为日、月、年等。该extract函数提取日期/时间类型的特定值。例如，我通常将extract用于以下情况：
/* returns the epoch value for a date / time */
/* I this use to send date values to Javascript */
SELECT extract('epoch' from current_date);
/* returns the hour from a time type */
SELECT extract('hour' from now());复制如何将其用于对值进行分类？例如，如果您想查找一周中哪一天的哪个小时的订单数量和销售额最高：
SELECT
extract('dow' from order_date) AS day_of_week,
extract('hour' from order_date) AS hour,
COUNT(*) AS total_orders,
SUM(total_amount) AS monthly_total
FROM
orders
GROUP BY 1, 2
ORDER BY 1, 2;复制 day_of_week | hour | total_orders | monthly_total
-------------+------+--------------+---------------
0 | 23 | 35 | 23631.56
1 | 0 | 31 | 19299.88复制您会在这里看到星期日是“0”而星期六是“6”。
其中date_trunc保留较高上下文，extract删除除请求的上下文之外的所有上下文。
to_char - 极端改造日期版
它很尴尬，因为to_char它既是日期分箱中最通用的函数，也是最令人讨厌的函数。该函数将接受时间/日期、文本或数字以进行额外的格式化，因此它不是明确用于日期函数的。它从未失败过，当我使用 to_char 时，有人告诉我我可以使用更好的函数。它可以快速生成人类可读的值，但它不适合发送数据进行额外的机器处理。
以下列举几个例子to_char：
/* extract current day of week and current hour of day based on UTC */
SELECT to_char(now(), 'DayHH24');
/* extract current day of week and current hour of day based on NYC time zone */
SELECT to_char(now() AT TIME ZONE 'America/New_York' , 'DayHH24');复制这将输出当前星期几以及基于 UTC 时间的当前小时。这让你伤透了脑筋吧？“DayHH24”部分是什么意思？Postgres 文档列出了to_char 使用的保留字符串的长列表：
要更改月份的显示方式，请使用 to_char 提取并格式化名称和年份：
SELECT to_char(order_date, 'FMMonth YYYY') AS formatted_month,
COUNT(*) AS total_orders,
SUM(total_amount) AS monthly_total
FROM
orders
GROUP BY 1
ORDER BY 1;复制 formatted_month | total_orders | monthly_total
-----------------+--------------+---------------
August 2024 | 11 | 2699.82
September 2024 | 39 | 8439.41复制转义保留字符串to_char：
财务中季度的常见格式是“Q1”/“Q2”/“Q3”和“Q4”。使用to_char，我们可以以该格式提取某个时间的季度。但是，“Q”是季度的保留关键字。要打印“Q”而不对其进行评估，请将其括在双引号中：
SELECT
to_char(order_date, '"Q"Q-YYYY') AS formatted_quarter,
SUM(total_amount) AS total_amount
FROM
orders
GROUP BY 1
ORDER BY 1;复制 formatted_quarter | total_amount
-------------------+--------------
Q1-2022 | 313872.84
Q1-2023 | 282774.15
Q1-2024 | 287379.33复制概括
分箱是财务报告和数据分析中对数据进行分面处理的重要工具。日期和时间是一种比乍一看更复杂的信息——小时、月、小时、季度、年。因此，一个日期可以以多种方式进行分面处理。
幸运的是，Postgres 具有处理日期所需的函数。简要总结如下：
interval- 通过添加/减去修改日期/时间
date_trunc -截断日期/时间 — 本质上是向下舍入到最接近的值
extract- 从日期/时间（日、周、月、季度、年）中提取单条信息
to_char - 将输出格式化为特定样式的日期格式或文本字符串。
作者：Christopher Winslett
链接：https://www.crunchydata.com/blog/4-ways-to-create-date-bins-in-postgres-interval-date_trunc-extract-and-to_char
【PGCCC】PostgreSQL培训考试认证中心，国内权威PG培训认证机构，由工业和信息化部教育与考试中心直发证书。咨询【加V：pgccc400】
#PG证书#PG考试#postgresql培训#postgresql考试#postgresql认证
