sql - PostgreSQL 获取时间戳之间的时间差

Question

我看过很多与此相关的 SO 问题，但我似乎无法解决任何问题。我不太擅长半复杂的 SQL 查询。

我想获取当前时间与 unix 时间戳中的列之间的时差。

我不确定我在这方面做错了什么或做对了。目标是只拉取不到 24 小时的行。如果有更好的方法或示例可以有效，那就太好了。

我从这里尝试了几个答案Timestamp Difference In Hours for PostgreSQL

无论我尝试多少种不同的方法，我都无法使这个查询起作用。 wc.posted 是 bigint 存储为 unix timestamp

SELECT w.wal_id, wc.com_id, w.posted AS post_time, wc.posted AS com_time 
FROM wall as w LEFT JOIN wall_comments as wc ON w.wal_id=wc.wal_id 
WHERE (EXTRACT(EPOCH FROM wc.posted)) > current_timestamp - interval '24 hours'

然后是错误:

ERROR:  function pg_catalog.date_part(unknown, bigint) does not exist
LINE 1: ... wall_comments as wc ON w.wal_id=wc.wal_id WHERE (EXTRACT(EP...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

********** Error **********

ERROR: function pg_catalog.date_part(unknown, bigint) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 148

这是一个简化的 fiddle

Answer 1

来自fine manual :

A single-argument to_timestamp function is also available; it accepts a double precision argument and converts from Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp with time zone. (Integer Unix epochs are implicitly cast to double precision.)

因此，将 bigint seconds-since-epoch 转换为 timestampz:

to_timestamp(wc.posted)

也许您正在寻找这个:

WHERE to_timestamp(wc.posted) > current_timestamp - interval '24 hours'