在这篇文章中，我将介绍一些按日期查询数据的关键函数。有关在 Postgres 中存储日期和时间的最佳方法的摘要.
interval——日期操纵的瑞士刀
是
interval用于修改其他时间的数据类型。例如，可以从已知时间中添加或减去间隔。间隔非常方便，是您按日期快速汇总数据的第一个方法。就像瑞士刀一样，它并不总是完成工作的最佳工具，但它可以在紧急情况下使用。让我们谈谈它的优势所在。
我们如何运行查询来返回过去 90 天的订单总额？当然可以使用间隔。如果没有间隔，我们经常看到人们使用从生成日期的外部源传递的日期变量。使用
now() - INTERVAL '90 days'，无论日期如何，您都可以使用相同的查询。另一个秘诀是使用
now()服务器上当前时间的时间戳。
SELECT SUM(total_amount)FROM ordersWHERE order_date >= NOW () - INTERVAL '90 days'; sum----------- 259472.99(1 row)
sum ----------- 259472.99 (1 row)
除了使用 之外
now()，
current_date还可以用来返回日期而不是时间。
SELECT SUM(total_amount)FROM ordersWHERE 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。 SQL
CASE在查询中执行条件逻辑。 的格式为
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
#PG证书#PG考试#postgresql培训#postgresql考试#postgresql认证
