postgresql - Postgres : difference between two timestamps (hours:minutes:seconds)

Question

我正在创建一个计算两个时间戳之间差异的选择

这里是代码:(你不需要理解下面的表格。只需按照线程)

  (select value from demo.data where id=q.id and key='timestampend')::timestamp 
- (select value from demo.data where id=q.id and key='timestampstart')::timestamp) as durata

看看这个例子，如果你想要更简单的话:

select timestamp_end::timestamp - timestamp_start as duration

---

这里是结果:

//"durata"是持续时间

问题是第一个时间戳是 2017-06-21，第二个是 2017-06-22，所以我们有 1 天和几个小时的差异。我该怎么做才能显示结果，而不像“1 天 02:06:41.993657”，而是没有毫秒 (26:06:41) 的“26:06:41.993657”？

---

---

更新我正在测试这个查询:

select id as ticketid,
(select value from demo.data where id=q.id and key = 'timestampstart')::timestamp as TEnd,
(select value from demo.data where id=q.id and key = 'timestampend')::timestamp as TStart,
(select
make_interval
(
0,0,0,0, -- years, months, weeks, days
extract(days from duration1)::int * 24 + extract(hours from duration1)::int, -- calculated hours (days * 24 + hours)
extract(mins from duration1)::int, -- minutes
floor(extract(secs from duration1))::int -- seconds, without miliseconds, thus FLOOR()
) as duration1
from
(
(select value from demo.data where id=q.id and key='timestampstart')::timestamp - (select value from demo.data where id=q.id and key='timestampend')::timestamp
) t(duration) as dur
from (select distinct id from demo.data) q

错误是一样的:[Err] ERROR: syntax error at or near "::"id = q.id 有错误

数据表是这样的:

Answer 1

您可以使用EXTRACT 函数并用MAKE_INTERVAL 和一些数学运算将其包装起来。这非常简单，因为您将时间戳的每个部分传递给它:

select 
  make_interval(
    0,0,0,0, -- years, months, weeks, days
    extract(days from durdata)::int * 24 + extract(hours from durdata)::int, -- calculated hours (days * 24 + hours)
    extract(mins from durdata)::int, -- minutes
    floor(extract(secs from durdata))::int -- seconds, without miliseconds, thus FLOOR()
    ) as durdata
from (
  select '2017-06-22 02:06:41.993657'::timestamp - '2017-06-21'::timestamp
  ) t(durdata);

输出:

 durdata
----------
 26:06:41

---

您可以将其包装在一个函数中，以便于使用。不用担心 timestamp - timestamp 返回精度超过天数的输出，从而丢失一些信息，因为即使计算不同年份仍会返回天数和额外的时间部分。

例子:

postgres=# select ('2019-06-22 01:03:05.993657'::timestamp - '2017-06-21'::timestamp) as durdata;
        durdata
------------------------
 731 days 01:03:05.993657