我有两个表,一个提到事件的开始和结束时间以及相应的值,另一个表记录每天午夜的值.
CREATE TABLE table1 (
id integer,
date_strt timestamp,
date_end timestamp,
strt_unit integer,
end_unit integer
);
INSERT INTO table1 (id, date_strt, date_end,strt_unit,end_unit)
VALUES
(1, '2023-10-27 12:00:00','2023-10-31 12:00:00', 5,72),
(2, '2023-10-30 12:15:00','2023-11-02 00:00:00', 78,90);
CREATE TABLE table2 (
id integer,
dates timestamp,
unit integer
);
INSERT INTO table2 (id, dates, unit)
VALUES
(1, '2023-10-28 00:00:00', 55),
(1, '2023-10-29 00:00:00', 60),
(1, '2023-10-30 00:00:00', 65),
(1, '2023-10-31 00:00:00', 70),
(2, '2023-10-30 00:00:00', 75),
(2, '2023-10-31 00:00:00', 80),
(2, '2023-11-01 00:00:00', 85),
(2, '2023-11-02 00:00:00', 90);
我想获得这样的数据集,其中我使用表2中可用的日期获得从表1中提到的开始日期到结束日期在午夜00:00:00小时的每一天的数据差异.
id start_time start_value end_time end_value
1, '2023-10-27 12:00:00', 5, '2023-10-28 00:00:00', 55
1, '2023-10-28 00:00:00', 55, '2023-10-29 00:00:00', 60
1, '2023-10-29 00:00:00', 60, '2023-10-30 00:00:00', 65
1, '2023-10-30 00:00:00', 65, '2023-10-31 00:00:00', 70
1, '2023-10-31 00:00:00', 70, '2023-10-31 12:00:00', 72
2, '2023-10-30 12:15:00', 78, '2023-10-31 00:00:00', 80
2, '2023-10-31 00:00:00', 80, '2023-11-01 00:00:00', 85
2, '2023-11-01 00:00:00', 85, '2023-11-02 00:00:00', 90
我计划使用1天作为间隔来生成开始日期和结束日期之间的日期序列,但无法生成适当的日期,因此无法在其中进一步使用领先或滞后. 我已经加了fiddle link.