postgresql - Postgres : how do I filter by a datetime field with infinity and -infinity?

Question

我正在尝试创建一个选择查询，并过滤一个等于“无穷大”和“-无穷大”的日期时间字段:

SELECT * FROM mytable WHERE dt = <??? infitity ???> OR dt = <??? -infinity ???>

我该怎么做？

Answer 1

SELECT * 
FROM mytable 
WHERE dt = 'infinity'
   OR dt = '-infinity';

这假设 dt 是一个 DATE 或 TIMESTAMP 列。

您也可以明确说明这一点(以明确表明这不是错字):

SELECT * 
FROM mytable 
WHERE dt = 'infinity'::timestamp
   OR dt = '-infinity'::timestamp;