PostgreSQL 的时间戳小时差异 [英] Timestamp Difference In Hours for PostgreSQL
问题描述
是否有 TIMESTAMPDIFF()
等效于 PostgreSQL?
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 或 best(...,0).适合您的意图.
Edit: if you want it greater than zero either use abs or greatest(...,0). Whichever suits your intention.
Edit++:我没有使用 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屋!