获取两个不同行中两个时间戳列的差异

qlzsbp2j 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(294)

我有下面的postgresql表“event”

CREATE TABLE event (
    user INTEGER,
    name VARCHAR(255),
    begin_timestamp TIMESTAMP,
    end_timestamp TIMESTAMP
);

表中数据：

╔══════╦══════╦═════════════════════╦═════════════════════╗
║ user ║ name ║   begin_timestamp   ║    end_timestamp    ║
╠══════╬══════╬═════════════════════╬═════════════════════╣
║ 5    ║ foo  ║ 2020-07-23 16:11:00 ║ 2020-07-23 16:28:00 ║
║ 5    ║ bar  ║ 2020-07-23 16:40:00 ║ 2020-07-23 16:40:00 ║
║ 5    ║ abcd ║ 2020-07-23 00:00:00 ║ 2020-07-23 00:37:00 ║
║ 7    ║ foo  ║ 2020-07-23 18:12:00 ║ 2020-07-23 18:23:00 ║
║ 7    ║ bar  ║ 2020-07-23 11:00:00 ║ 2020-07-23 11:00:00 ║
║ 8    ║ foo  ║ 2020-07-23 00:00:00 ║ 2020-07-23 03:00:00 ║
║ ...  ║ ...  ║ ...                 ║ ...                 ║
╚══════╩══════╩═════════════════════╩═════════════════════╝

如何获取特定用户的两个事件之间的时间(不是特定事件的持续时间）
示例：用户“5”的事件“foo”和事件“bar”之间的时间是12分钟，因为事件“foo”在2020-07-23 16:28:00结束，事件“bar”在2020-07-23 16:40:00开始。
到目前为止，我有以下问题：

SELECT end_timestamp FROM event WHERE name = 'foo' and user = 5
SELECT begin_timestamp FROM event WHERE name = 'bar' and user = 5
SELECT (end_timestamp - begin_timestamp) AS duration FROM event WHERE name = 'foo' AND user = 5;

sql postgresql DateTime timestamp

来源：https://stackoverflow.com/questions/63052042/get-difference-of-two-timestamp-columns-in-two-different-rows

1条答案

按热度按时间

mgdq6dx11#

你可以用 lag() . 假设每个事件最多有一个“foo”和“bar”：

select e.*,
       (begin_timestamp - prev_end_timestamp) as duration
from (select e.*, lag(end_timestamp) over (partition by name order by begin_timestamp) as prev_end_timestamp
      from event e
      where e.name in ('foo', 'bar')
     ) e
where e.name = 'bar';

您还可以使用聚合来表达这一点：

select user,
       ( max(begin_timestamp) filter (where name = 'bar') -
         min(end_timestamp) filter (where name = 'foo')
       ) as duration
from event e
where e.name in ('foo', 'bar')
group by user

赞(0）回复(0）举报 2021-07-24

我来回答

获取两个不同行中两个时间戳列的差异

1条答案

相关问题

热门标签

最新问答