我正在尝试计算 Postgres 上两个日期之间的差异。首先我得到日期:
SELECT * FROM
(
SELECT
to_char(to_timestamp(t.time/1000000), 'DD/MM/YYYY HH24:MI:SS') AS initial_date
FROM
book.ticket AS t
WHERE status = 'planning'
) AS initial_date,
(
SELECT
to_char(to_timestamp(t.time/1000000), 'DD/MM/YYYY HH24:MI:SS') AS final_date
FROM
book.ticket AS t
WHERE status = 'closed'
) AS final_date
然后我想做类似的事情来计算之前恢复日期之间的差异:
SELECT DATE_PART('day', final_date::timestamp - initial_date::timestamp)
但我不知道如何将这最后一部分(计算的那一部分)与其他查询放在一起。有人能帮我吗?
好的,伙计们!经过所有这些讨论,我解决了我的问题。我将在下面发布代码。谢谢大家的帮助,大家的意见很重要!
SELECT t1.id, t1.initial_date, t2.id, t2.final_date, t2.final_date - t1.initial_date as dif
FROM (
(
SELECT
t.id,
to_timestamp(t.time/1000000) AS initial_date
FROM
book.ticket AS t
WHERE status = 'planning'
) t1
JOIN (
SELECT
t.id,
to_timestamp(t.time/1000000) AS final_date
FROM
book.ticket AS t
WHERE status = 'closed'
) t2
ON t1.id = t2.id
)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句