PostgreSQL将时间范围分成几天
我正在尝试使用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 对于两个检索到的条目,其他列中的值相同. 我已经看到this question似乎或多或少地解决了我想要的问题,但它是针对PostgreSQL的“非常老”的版本,所以我不确定它是否仍然适用. 我也看过this question,它完全符合我的要求,但据我所知,CONNECT BY语句是SQL标准的Oracle扩展,所以我不能使用它. 我相信我可以使用PostgreSQL的generate_series来实现这一点,但是我希望有一个简单的例子可以证明它是如何用来做到这一点的. 这是我目前正在处理的查询,目前无效(因为我无法在连接的子查询中引用FROM表),但我相信这或多或少是正确的轨道. Here’s the fiddle,包含架构,示例数据和我的工作查询. 更新:我刚刚发现一个有趣的事实,感谢this question,如果你在查询的SELECT部分??使用set-returns函数,PostgreSQL将“自动”在集合和行上进行交叉连接.我想我已接近完成这项工作. 解决方法
首先,你的上边界概念被打破了. 23:59:59的时间戳不好.数据类型时间戳具有小数位数.怎么样2013-10-18 23:59:59.123 ::时间戳?
包括下边框并在逻辑中的任何位置排除上边框.相比: > Calculate number of concurrent events in 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,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,interval '1d')::date AS d FROM timesheet_entries t ) sub ORDER BY id,stime; 更简单的可能更快. 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,interval '1d')::date AS d FROM timesheet_entries t ) sub1 ORDER BY id,stime ) sub2 WHERE etime <> stime; Postgres 9.3 在Postgres 9.3中,你最好使用LATERAL SELECT id,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; Details in the manual. SQL Fiddle展示全部. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |