如何在PostgreSQL中获取两个时间戳间的小时列表及对应分钟数？

你可以通过generate_series生成时间序列，配合日期截断和条件判断来实现需求。以下是针对你给出示例的完整查询：

WITH time_range AS (
    SELECT
        '2023-02-23 14:38'::timestamp AS start_ts,
        '2023-02-23 19:32'::timestamp AS end_ts
)
SELECT
    EXTRACT(hour FROM hour_start)::integer AS hour,
    CASE
        -- 处理起始小时，计算从起始时间到当前小时结束的分钟数
        WHEN hour_start = date_trunc('hour', start_ts) THEN
            EXTRACT(minute FROM (LEAST(hour_start + INTERVAL '1 hour', end_ts) - start_ts))::integer
        -- 处理结束小时，计算从当前小时开始到结束时间的分钟数
        WHEN hour_start = date_trunc('hour', end_ts) THEN
            EXTRACT(minute FROM (end_ts - hour_start))::integer
        -- 中间完整小时直接返回60分钟
        ELSE 60
    END AS minutes
FROM time_range
CROSS JOIN generate_series(
    date_trunc('hour', start_ts),
    date_trunc('hour', end_ts),
    INTERVAL '1 hour'
) AS hour_start;

查询说明： #

1. time_range CTE：定义起始和结束时间戳，方便后续复用和修改。
2. generate_series：生成从起始时间整点到结束时间整点的每小时时间序列，覆盖所有涉及的小时区间。
3. 条件判断：
   • 起始小时：取当前小时的下一个整点与结束时间的较小值，减去起始时间，得到该小时内的有效分钟数。
   • 结束小时：用结束时间减去当前小时的整点，得到该小时内的有效分钟数。
   • 中间小时：完整的60分钟，直接返回60。

这个方案同样适用于跨天的时间范围，比如从2023-02-23 23:30到2023-02-24 02:15，会正确返回23点30分、0点60分、1点60分、2点15分的结果。

内容的提问来源于stack exchange，提问作者Tibor