PostgreSQL的时间戳时差（小时） [英] Timestamp Difference In Hours for PostgreSQL

问题描述

PostgreSQL是否有 TIMESTAMPDIFF（）等效项？

Is there a TIMESTAMPDIFF() equivalent for PostgreSQL?

我知道我可以减去两个时间戳来获取postgresql INTERVAL 。我只想要两个时间戳之间的时差，以INT表示。

I know I can subtract two timestamps to get a postgresql INTERVAL. I just want the difference between the two timestamps in in hours represented by an INT.

我可以在MySQL中这样做：

I can do this in MySQL like this:

TIMESTAMPDIFF(HOUR, links.created, NOW())

我只需要两个时间戳之间的时差即可

I just need the difference between two timestamps in hours represented as an integer.

解决方案对我有用：

SELECT "links_link"."created",
"links_link"."title",
(EXTRACT(EPOCH FROM current_timestamp - "links_link"."created")/3600)::Integer AS "age" 
FROM "links_link"

推荐答案

第一件事突然出现

EXTRACT(EPOCH FROM current_timestamp-somedate)/3600

可能并不漂亮，但可以畅通无阻。如果定义了间隔除以间隔，可能会更漂亮。

May not be pretty, but unblocks the road. Could be prettier if division of interval by interval was defined.

编辑：如果希望大于零，则使用abs或great（。 ..，0）。

Edit: if you want it greater than zero either use abs or greatest(...,0). Whichever suits your intention.

编辑++ ：我不使用 age 是带有单个参数的 age 用来引用文档：减去current_date（在午夜）。这意味着除非在午夜跑步，否则您无法获得准确的年龄。现在快到凌晨1点了：

Edit++: the reason why I didn't use age is that age with a single argument, to quote the documentation: Subtract from current_date (at midnight). Meaning you don't get an accurate "age" unless running at midnight. Right now it's almost 1am here:

select age(current_timestamp);
       age        
------------------
 -00:52:40.826309
(1 row)

这篇关于PostgreSQL的时间戳时差（小时）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！