Sql 将时间范围划分为全天和前后剩余小时

假设数据类型为timestamp(使用样例数据，而不是显示timestamptz的相互矛盾的表定义).否则，您必须执行更多操作.请参见:

• Ignoring time zones altogether in Rails and PostgreSQL个

1.在午夜拆分所有范围，保持合并全天

正如你的 comments 所表明的那样.(此外，与您的‘meter3’样本行一致.)

WITH cte AS (
   SELECT *
        , CASE WHEN start_at::time = '0:0' THEN start_at
               ELSE date_trunc('day', start_at) + interval '1 day' END AS start_day
        , date_trunc('day', end_at) AS end_day
   FROM   metering_ranges
   )
-- nothing to split
SELECT metering_point_id, device_id, start_at, end_at
FROM   cte
WHERE  end_day < start_day

-- split core days
UNION ALL
SELECT metering_point_id, device_id, start_day, end_day
FROM   cte
WHERE  end_day > start_day
-- add leading range
UNION ALL
SELECT metering_point_id, device_id, start_at, start_day
FROM   cte
WHERE  end_day >= start_day
AND    start_at::time > '0:0'
-- add trailing range
UNION ALL
SELECT metering_point_id, device_id, end_day, end_at
FROM   cte
WHERE  end_day >= start_day
AND    end_at::time > '0:0'
ORDER  BY 1,2,3;

fiddle个个

Use UNION ALL, not UNION. Faster.
Also, you can append ORDER BY to a UNION query once at the end. That applies to the whole result set.

2.只列出全天的范围

我最初的解释是.(也许是更常见的用例？)

WITH cte AS (
   SELECT *
        , CASE WHEN start_at::time = '0:0' THEN start_at
               ELSE date_trunc('day', start_at) + interval '1 day' END AS start_day
        , date_trunc('day', end_at) AS end_day
   FROM   metering_ranges
   )
-- nothing to split
SELECT metering_point_id, device_id, start_at, end_at
FROM   cte
WHERE  end_day <= start_day
-- split core days
UNION ALL
SELECT metering_point_id, device_id, start_day, end_day
FROM   cte
WHERE  end_day > start_day
-- add leading range
UNION ALL
SELECT metering_point_id, device_id, start_at, start_day
FROM   cte
WHERE  end_day > start_day
AND    start_at::time <> '0:0'
-- add trailing range
UNION ALL
SELECT metering_point_id, device_id, end_day, end_at
FROM   cte
WHERE  end_day > start_day
AND    end_at::time <> '0:0'
ORDER  BY 1,2,3;

没有嵌套完整天数的only difference:范围仍在午夜拆分.即从00:00开始到第二天24:00之前结束的范围.小提琴证明了这一点.

相关:

• Are results from UNION ALL clauses always appended in order?个

Variants for case 2. with multirange type

对于Case 1.，这些方法不会立即起作用，因为只有在提取全天的情况下，范围才会被分割.不适合‘meter3’的样本行.

需要Postgres 14或更高版本.我们可以使用新的anymultirange - anymultirange→anymultirange操作员.The manual:个

计算多范围的差值.

然后我们可以对得到的多范围进行unnest()，以得到剩余的分数.

WITH cte AS (
   SELECT *
        , CASE WHEN start_at::time = '0:0' THEN start_at
               ELSE date_trunc('day', start_at) + interval '1 day' END AS start_day
        , date_trunc('day', end_at) AS end_day
   FROM   metering_ranges
   )
-- nothing to split
SELECT metering_point_id, device_id, start_at, end_at
FROM   cte
WHERE  end_day <= start_day

-- nested full days to split
UNION ALL
SELECT metering_point_id, device_id, x.*
FROM   cte
CROSS  JOIN LATERAL (
   VALUES (start_day, end_day)
   UNION ALL
   SELECT lower(rest), upper(rest)
   FROM   unnest(tsrange(start_at, end_at)::tsmultirange
               - tsrange(start_day, end_day)::tsmultirange) rest
   ) x
WHERE  end_day > start_day
ORDER  BY 1,2,3;

或者更短，没有CTE，有LEFT JOIN LATERAL分:

SELECT c.metering_point_id, c.device_id
     , COALESCE(x.start_at, c.start_at) AS start_at
     , COALESCE(x.end_at, c.end_at) AS end_at
FROM  (
   SELECT *
        , CASE WHEN start_at::time = '0:0' THEN start_at
               ELSE date_trunc('day', start_at) + interval '1 day' END AS start_day
        , date_trunc('day', end_at) AS end_day
   FROM   metering_ranges
   ) c
LEFT   JOIN  LATERAL (
   VALUES (start_day, end_day)
   UNION ALL
   SELECT lower(rest), upper(rest)
   FROM   unnest(tsrange(start_at, end_at)::tsmultirange
               - tsrange(start_day, end_day)::tsmultirange) rest
   WHERE  c.end_day > c.start_day
   ) x(start_at, end_at) ON c.end_day > c.start_day
ORDER  BY 1,2,3;

fiddle个个

但是，构建、计算和解嵌多范围会增加相当大的开销.我不确定这种复杂程度是否能与更"暴力"的查询相抗衡.