添加和递增日期和时间（Postgresql）

Question

以下是一个表格，旨在展示媒体播放的时间。基本上它有一个开始时间（starts），曲目长度（clip_length），结束时间（ends = starts + clip_length），以及曲目的位置。

|starts              | ends                 |position   |file_id| clip_length
|2013-08-30 22:00:00 | 2013-08-30 22:03:08  |0          |16     |00:03:08.081768
|2013-08-30 22:03:08 | 2013-08-30 22:06:33  |1          |17     |00:03:25.436485
|2013-08-30 22:06:33 | 2013-08-30 22:09:07  |2          |7      |00:02:33.79968
|2013-08-30 22:09:07 | 2013-08-30 22:12:21  |3          |3      |00:03:14.020273
|2013-08-30 22:12:21 | 2013-08-30 22:15:31  |4          |8      |00:03:10.466689

我想要做的是在下面的位置=2处添加一条记录。我已经成功地增加了这些位置，但问题在于时间都被弄乱了。

|starts              | ends                 |position   |file_id|clip_length
|2013-08-30 22:00:00 | 2013-08-30 22:03:08  |0          |16     |00:03:08.081768
|2013-08-30 22:03:08 | 2013-08-30 22:06:33  |1          |17     |00:03:25.436485
|2013-08-30 22:06:33 | 2013-08-30 22:09:07  |2          |7      |00:02:33.79968
|2013-08-30 22:06:33 | 2013-08-30 22:11:03  |3          |1      |00:04:30.006958
|2013-08-30 22:09:07 | 2013-08-30 22:12:21  |4          |3      |00:03:14.020273
|2013-08-30 22:12:21 | 2013-08-30 22:15:31  |5          |8      |00:03:10.466689

所以可以使用第一个启动时间作为00点，并将 clip_length 添加到 starts 中并保存在 ends 中，对于第一个位置。然后对于第二个位置，使用第一个 ends 值作为起点，并递归执行此操作直到结束（按照位置顺序）。

提前感谢您的帮助。

- Charm_quark

---

难以理解您想要什么。区间的累计总和？ - Craig Ringer

基本上我想在“开始”字段中添加“剪辑长度”字段，并将其保存在同一行的“结束”字段中。然后在下一行中，使用上一首歌曲结束的时间作为即将到来的歌曲的开始时间。 - Charm_quark

顺带一提：如果这些是本地时间，而应用程序运行在具有夏令时的时区中，则在转换期间会出现错误。 - Matt Johnson-Pint

好的，这里显示的时间是格林威治标准时间。因此，系统已经将其转换为GMT时间... - Charm_quark

Answer 1

SQL Fiddle

update clip c
set
    starts = s.starts,
    ends = s.ends
from (
    select
        starts,
        starts + clip_length as ends,
        file_id,
        position
    from (
        select
            '2013-08-30 22:00:00'::timestamp
            + sum(clip_length) over(order by position)
            - clip_length as starts,
            clip_length,
            file_id,
            position
        from clip
    ) s
) s
where c.file_id = s.file_id

- Clodoaldo Neto

---

嘿，我不认为我理解得很好。当我执行它时，它将所有值更改为'sum（clip_length）'作为“ends”值，并将'sum（clip_length）-最后一个剪辑长度'作为“starts”值。对于所有行，它都是相同的值。 - Charm_quark

Answer 2

你的数据模型存在一些问题，存储了至少两个冗余信息。你需要存储 starts 和 file_id，以及其中一个信息 clip_length、ends 或 position，它们可以相互计算得出。

目前你在存储 冗余 数据，这导致你现在遇到数据不一致的问题，在自身存在冲突。

在这种情况下，似乎只信任 position 而其他信息不被信任。这是我提出的解决方法：

SELECT 
  (SELECT min(starts) FROM Sometable)
    + COALESCE(
       sum(clip_length) OVER all_rows_but_last,
       INTERVAL '0' second
    )
  AS starts,
  (SELECT min(starts) FROM Sometable)
    + COALESCE(
       sum(clip_length) OVER all_rows_but_last,
       INTERVAL '0' second
    ) + clip_length
  AS ends, 
  position, 
  clip_length
FROM Sometable
WINDOW all_rows_but_last AS (ORDER BY position ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING);

请看: http://sqlfiddle.com/#!12/ca5fa/1 这里的原则是找到最低的起点 - 已知为有效的 - 并将后续的结束和开始视为冗余和无用。相反，将前面持续时间的累加总和作为起点，将同样的累加总和加上当前间隔作为结束。

你会注意到这不是一个更新。那是因为我认为你应该实际上：

- 将position更改为浮点值或大整数，以便在插入条目时不需要重新编号。如果您在1和2之间放置了某些内容，请将其位置设置为0.5。或者从10000、20000等开始，以便您可以插入15000。 - 完全摆脱ends列。计算它从starts+clip_length - 将starts移到单独的表中，仅为片段系列存储一次。根据自开始以来以前剪辑长度的总和即时计算剪辑开始时间。 - 重新定义当前表作为上述两个表的视图。

这对于您的数据及其使用方式做出了很多猜测，但您并没有多说。

- Craig Ringer

---

:) ，很遗憾，我无法控制数据库（不能删除列），也无法控制设计和数据字典。这是一个第三方应用程序，我正在尝试为其开发API。 - Charm_quark

哦，而且Field_id并不是非常相关的。它是自动生成的。 - Charm_quark

Answer 3

with cte as (
    select
        '2013-08-30 22:00:00'::timestamp
         + sum(clip_length) over(order by position) as ends,
         file_id
    from clip
)
update clip as cl set
    starts = c.ends - cl.clip_length,
    ends = c.ends
from cte as c
where cl.file_id = c.file_id;

=>sql fiddle

实际上，您的表中完全可以没有开始时间和结束时间。您可以从表中删除这些列，并创建以下函数：

create or replace function clip_layout(_starts timestamp)
returns table(
    starts timestamp, ends timestamp,
    "position" int, file_id int,clip_length interval
)
as
$$
with cte as (
    select
        _starts + sum(clip_length) over(order by position) as ends,
        file_id, clip_length, position
    from clip
)
select
    ends - clip_length as starts,
    ends, position,
    file_id, clip_length
from cte
$$
language sql;

所以你可以看到从任何时间开始/结束的起始和结束时间：

select * from clip_layout('2013-08-30 22:00:00'::timestamp)

=>sql fiddle演示

- Roman Pekar