PostgreSQL中如何比较时间戳和长的Unix时间戳

Question

我在一张表中有一个时间戳属性，我想在SQL查询中放置一个条件，其中条件值是UNIX时间戳（即数值长值）。

[...] table.timestampattr > 6456454654 [...]

我该怎么做？

- Jack Gibson

Answer 1

你可以使用extract(epoch from ...)从任何PostgreSQL时间和日期类型中获取Unix时间戳（请参见手册中的日期/时间函数）。因此，你的查询片段可以这样写：

[...] extract(epoch from table.timestampattr) > 6456454654 [...]

或者，to_timestamp 函数执行相反的转换，因此您也可以编写：

或者，to_timestamp 函数执行相反的转换，因此您也可以编写：

[...] table.timestampattr > to_timestamp(6456454654) [...]

- IMSoP

---

使用 to_timestamp() 是更好的选择，它会计算出一个常数。而从时间戳列中提取epoch必须对每行进行计算。 - Mike Sherrill 'Cat Recall'

@MikeSherrill'Catcall' 说得好。关键在于 to_timestamp 被定义为 IMMUTABLE，因此查询规划器应该知道只运行一次并缓存结果。 - IMSoP

两个函数都是不可变的。区别在于，在这种情况下，to_timestamp()使用常量参数调用，而extract()在每一行上使用不同的参数调用。如果操作员将Unix纪元存储在表中，并需要将其与类型为时间戳的常量值进行比较，则extract()将是更好的选择。 - Mike Sherrill 'Cat Recall'

@MikeSherrill'Catcall' 你说得对！我想我之前所说的是给那些不熟悉的人一个提示，即是函数的“immutable”属性使这一切成为可能，而不是某种内部魔法。如果用户定义的函数（例如，用于转换 MS “ticks” 值的函数）被定义为“immutable”，它将获得相同的性能优势，但如果没有定义为“immutable”，则会为每一行计算一次。 - IMSoP

@JackGibson "Sat, 09 Feb 2013 13:45:25 UTC" 的 Unix 时间戳是 1360417525；看起来你使用的是毫秒精度而不是秒。to_timestamp 接受一个浮点数，所以你可以加上小数点得到你想要的值：select to_timestamp(1360417525.156)。 - IMSoP

Answer 2

你可以创建一个常量并将其设置为当前数据，然后像下面这样进行选择：

    WITH myconstants (current_unix_date) as (
       values (extract(epoch from now() at time zone 'utc')) 
    )

    SELECT * From table, myconstants
    WHERE table.target_date_time_unix > myconstants.current_unix_date 

- Kaveh Naseri