PostgreSQL 将时间范围拆分为天

Question

我正在尝试使用PostgreSQL 9.2.4编写一个复杂的查询，但无法使其正常工作。我有一个包含时间范围的表格，以及其他几列。当我在此表中存储数据时，如果所有列都相同，并且时间范围重叠或相邻，则将它们合并为一行。

但是，当我检索它们时，我想在日边界处拆分范围 - 例如：

2013-01-01 00:00:00 to 2013-01-02 23:59:59

将被选为两行：

2013-01-01 00:00:00 to 2013-01-01 23:59:59
2013-01-02 00:00:00 to 2013-01-02 23:59:59

其他列中的值对于两个检索到的条目相同。

我已经看到这个问题似乎或多或少地解决了我想要的问题，但它适用于PostgreSQL的"非常旧"版本，所以我不确定它是否真的仍然适用。

我也看过这个问题，它完全符合我的需求，但据我所知，CONNECT BY语句是 SQL 标准的 Oracle 扩展，所以我不能使用它。

我相信我可以使用PostgreSQL的generate_series来实现这一点，但我希望有一个简单的例子来演示如何使用它来做到这一点。

这是我目前正在处理的查询，目前不起作用（因为我无法在连接的子查询中引用FROM表），但我相信这或多或少是正确的轨道。

下面是对架构、示例数据和我的工作查询的摆弄。

更新：由于这个问题，我刚刚发现了一个有趣的事实，如果你在查询的SELECT部分使用集合返回函数，PostgreSQL 将"自动"在集合和行上进行交叉连接。我想我快要开始工作了。

Answer 1

首先，你的上边界概念被打破了。带有23:59:59的时间戳是不好的。数据类型timestamp具有小数位数。2013-10-18 23:59:59.123::timestamp呢？

在逻辑中的所有位置包括下边框并排除上边框。比较：

• 计算 SQL 中的并发事件数

在此前提之上：

Postgres 9.2 或更早

版本

SELECT id
     , stime
     , etime
FROM   timesheet_entries t
WHERE  etime <= stime::date + 1  -- this includes upper border 00:00
UNION ALL
SELECT id
     , CASE WHEN stime::date = d THEN stime ELSE d END     -- AS stime
     , CASE WHEN etime::date = d THEN etime ELSE d + 1 END -- AS etime
FROM (
   SELECT id
        , stime
        , etime
        , generate_series(stime::date, etime::date, interval '1d')::date AS d
   FROM   timesheet_entries t
   WHERE  etime > stime::date + 1
   ) sub
ORDER  BY id, stime;

或者简单地说：

SELECT id
     , CASE WHEN stime::date = d THEN stime ELSE d END     -- AS stime
     , CASE WHEN etime::date = d THEN etime ELSE d + 1 END -- AS etime
FROM (
   SELECT id
        , stime
        , etime
        , generate_series(stime::date, etime::date, interval '1d')::date AS d
   FROM   timesheet_entries t
   ) sub
ORDER  BY id, stime;

更简单的甚至可能更快。
请注意，当stime和etime都恰好落在00:00时，会有所不同。然后在末尾添加一行时间范围为零。有多种方法可以解决这个问题。我提议：

SELECT *
FROM  (
   SELECT id
        , CASE WHEN stime::date = d THEN stime ELSE d END     AS stime
        , CASE WHEN etime::date = d THEN etime ELSE d + 1 END AS etime
   FROM (
      SELECT id
           , stime
           , etime
           , generate_series(stime::date, etime::date, interval '1d')::date AS d
      FROM   timesheet_entries t
      ) sub1
   ORDER  BY id, stime
   ) sub2
WHERE  etime <> stime;

邮政 9.3+

在Postgres 9.3+中，您最好为此使用LATERAL

SELECT id
     , CASE WHEN stime::date = d THEN stime ELSE d END     AS stime
     , CASE WHEN etime::date = d THEN etime ELSE d + 1 END AS etime
FROM   timesheet_entries t
     , LATERAL (SELECT d::date
                FROM   generate_series(t.stime::date, t.etime::date, interval '1d') d
                ) d
ORDER  BY id, stime;

手册中的详细信息。
与上述相同的角落情况。

SQL Fiddle 演示了所有内容。

Answer 2

有简单的解决方案（如果间隔在同一时间开始）

postgres=# 选择 i， i + 间隔 '1day' - 间隔 '1sec'  来自 generate_series（'2013-01-01 00：00：00'：：timestamp， '2013-01-02 23：59：59'， '1day'） g（i）;          i │ ？列？      ─────────────────────┼───────────────────── 2013-01-01 00：00：00 │ 2013-01-01 23：59：59 2013-01-02 00：00：00 │ 2013-01-02 23：59：59（2 行）

我写了一个表函数，可以执行任何间隔。它很快 - 两年范围划分为 753 毫秒内 10 个范围

创建或替换函数day_ranges（时间戳、时间戳）返回表（T1 时间戳，T2 时间戳）作为 $$开始  t1 ：= $1;  如果 2 美元> 1 美元，那么    圈      如果 T1：:d ate = $2：:d则        t2 ：= $2;        返回下一个;        退出;      结束如果;      t2 ：= date_trunc（"天"， t1） + 间隔 "1天" - 间隔 "1 秒";      返回下一个;      t1 ：= t2 + 间隔 '1 秒';    结束循环;  结束如果;  返回;结束;$$ 语言 plpgsql;

结果：

postgres=# 从day_ranges中选择 *（'2013-10-08 22：00：00'， '2013-10-10 23：00：00'）;         T1 │ T2         ─────────────────────┼───────────────────── 2013-10-08 22：00：00 │ 2013-10-09 23：59：59 2013-10-09 00：00：00 │ 2013-10-09 23：59：59 2013-10-10 00：00：00 │ 2013-10-10 23：00：00（3 行）时间： 6.794 ms

和

基于返回查询的更快（和更长）版本

创建或替换函数day_ranges（时间戳、时间戳）返回表（T1 时间戳，T2 时间戳）作为 $$开始  t1 ：= $1;t2 ：= $2;  如果 $1：:d ate = $2：:d ate 则    返回下一个;  还    -- 第一天    t2 ：= date_trunc（"天"， t1） + 间隔 "1天" - 间隔 "1 秒";    返回下一个;    如果 $2：:d ate> $1：:d ate + 1 则      返回查询选择 D、D + 间隔"1 天" - 间隔"1 秒"                      从 generate_series（date_trunc（'day'， $1 + 间隔 '1day'）：：时间戳，                                           date_trunc（"天"， $2 - 间隔"1天"）：：时间戳，                                           "1天"） g（d）;    结束如果;    -- 最后一天    t1 ：= date_trunc（"天"， $2）;t2 ：= $2;    返回下一个;  结束如果;  返回;结束;$$ 语言 plpgsql;

Answer 3

您可以通过简单地生成整个范围集中的所有天并使用 OVERLAPS 运算符将其连接到范围本身来构建结果。这既会丢弃没有数据的天数，又会为有数据的天数执行所需的笛卡尔乘积。

文档中对重叠行为进行了如下解释：

（开始 1、结束 1） 重叠（开始 2、结束 2）
（开始 1， 长度 1） 重叠 （开始 2， 长度 2）
此表达式在两个时间段时产生 true （由其端点定义）重叠，当它们不重叠时为 false。 端点可以指定为日期、时间或时间对 邮票;或作为日期、时间或时间戳，后跟间隔。 提供一对值时，开始或结束可以是 先写;重叠自动取 配对作为开始。每个时间段都被视为代表 半开区间开始 <= 时间

对于timesheet_entries表，查询将是：

select days.day, timesheet_entries.* from
  (select day from generate_series(
         (select min(stime) from timesheet_entries),
         (select max(etime) from timesheet_entries),
         '1 day'::interval) day
  ) days
 join timesheet_entries 
  on (stime,etime) overlaps (days.day,days.day+'1 day'::interval)
 order by 1;

这种技术也可以很容易地添加没有数据的天数，就像日历一样。为此，只需将join替换为 left join .

正如@Erwin在他的回答中所指出的，etime不应该代表区间的最后一秒（...:59:59），而应该代表下一秒作为排除的上限。对于您当前的数据可能无关紧要，但是如果某些行在...:59:59时stime，或者您碰巧需要亚秒级分辨率，那将是一个问题。