如何在Postgresql中比较日期的datetime字段？

Question

在 postgresql (Windows版本9.2.4) 中比较日期时，我遇到了奇怪的情况。

我的表中有一列叫做 update_date，类型为 timestamp without timezone。
客户端可以使用只带日期（例如：2013-05-03）或日期和时间（例如：2013-05-03 12:20:00）的方式搜索这个字段。

目前所有行的这一列都是时间戳，并且具有相同的日期部分2013-05-03，但时间部分不同。

当我比较这一列时，得到的结果却不同。例如以下结果：

select * from table where update_date >= '2013-05-03' AND update_date <= '2013-05-03' -> No results

select * from table where update_date >= '2013-05-03' AND update_date < '2013-05-03' -> No results

select * from table where update_date >= '2013-05-03' AND update_date <= '2013-05-04' -> results found

select * from table where update_date >= '2013-05-03' -> results found

我的问题是如何让第一个查询能够获得结果，我的意思是为什么第三个查询有效但第一个查询无效？

- user2866264

---

仅供参考，请查看一些有用的日期/时间函数和运算符，例如 NOW() + INTERVAL '1 hour'。 - Ricardo

Answer 1

@Nicolai对于类型转换和条件为什么对于任何数据都是false是正确的。我猜你更喜欢第一种形式，因为你想避免对输入字符串进行日期操作，是吗？你不需要害怕：

SELECT *
FROM table
WHERE update_date >= '2013-05-03'::date
AND update_date < ('2013-05-03'::date + '1 day'::interval);

- just somebody

---

这个语法('2013-05-03'::date 和 '1 day'::interval)是仅适用于PostgreSQL吗？ - Frozen Flame

是的，标准语法应该是CAST('2013-05-03' AS DATE) + CAST('1 day' AS INTERVAL)（如我所记）。DATE和INTERVAL的存在和行为因情况而异。 - just somebody

@FrozenFlame 是正确的，如果不将字符串转换为日期类型，则答案无法正常工作。仍然缺少一个转换。第一部分的 where 子句需要添加 ::DATE。 - StillLearningToCode

WHERE update_date::date = '2013-05-03' 这样写也可以，而且可能更易读一些。 - MikeF

@MikeF OP说update_date是没有时区的时间戳。我假设在该列上有一个索引。你的谓词不会使用该索引。 - just somebody

Answer 2

在比较 update_date >= '2013-05-03' 时，PostgreSQL会将值强制转换为相同的类型以进行比较。因此，您的“2013-05-03”被转换为“2013-05-03 00:00:00”。

所以，对于update_date = '2013-05-03 14:45:00'，您的表达式将是：

'2013-05-03 14:45:00' >= '2013-05-03 00:00:00' AND '2013-05-03 14:45:00' <= '2013-05-03 00:00:00'

这始终为false

要解决这个问题，请将update_date转换为date：

select * from table where update_date::date >= '2013-05-03' AND update_date::date <= '2013-05-03' -> Will return result

- Nicolai

---

将表中的每个 update_date 进行强制转换与仅将查询参数的单个值进行强制转换相比，效率非常低下，并且确保服务器无法利用该列上的索引。我很想给它打负分。 - just somebody

我同意对每个值进行转换的效率低下，因此你可能会因为这个解决方案而给出-1分。但我已经解释了问题的原因并举了一个例子来证明这个问题。现在，用户2866264知道为什么他的查询没有返回预期的行，并将决定哪种解决方案更适合他的独特情况。 - Nicolai

@Nicolai：非常感谢您的回答。按照您的答案，它可以正常工作。同时也感谢您的解释。 - user2866264

@Nicolai - 根据您关于Postgres将日期文字扩展到午夜的说法，如果目标是查找标记在单个日期（5月3日）上的记录，那么这段代码是否正确且更有效：SELECT * FROM my_table WHERE update_date >= '2013-05-03' AND update_date < '2013-05-04'; （请注意使用5月4日而不是3日，并使用小于号而不是小于等于号。） - Basil Bourque

Answer 3

使用range类型。如果用户输入一个日期：

select *
from table
where
    update_date
    <@
    tsrange('2013-05-03', '2013-05-03'::date + 1, '[)');

如果用户输入时间戳，则不需要::date + 1这部分。 http://www.postgresql.org/docs/9.2/static/rangetypes.html http://www.postgresql.org/docs/9.2/static/functions-range.html

- Clodoaldo Neto

---

那是一个简洁有趣的回答！ - yuriploc

Answer 4

使用日期转换后再进行比较：

尝试以下代码：

select * from table 
where TO_DATE(to_char(timespanColumn,'YYYY-MM-DD'),'YYYY-MM-DD') = to_timestamp('2018-03-26', 'YYYY-MM-DD')

- Yenky Bustamante

Answer 5

您还可以使用BETWEEN运算符。

以下是一个简单的示例：

SELECT
    customer_id,
    payment_id,
    amount,
    payment_date
FROM
    payment
WHERE
    payment_date BETWEEN '2007-02-07' AND '2007-02-15';

您也可以选择不在这些日期之间的所有内容：

SELECT
    customer_id,
    payment_id,
    amount,
    payment_date
FROM
    payment
WHERE
    payment_date NOT BETWEEN '2007-02-07' AND '2007-02-15';

这是一个更高级的示例，涉及基于天数的时间戳差异：

SELECT
    api_project.name,
    api_project.created,
    survey_response.created AS response_date,
    CASE
        WHEN survey_response.created
            BETWEEN api_project.created AND
                   (api_project.created + INTERVAL '180 days')
            THEN 'first_6_months'
        ELSE '6_months_after'
    END AS when_it_was_answered,
    EXTRACT(DAYS FROM survey_response.created - api_project.created)
      AS days_since_response
FROM
    bfb_survey_surveyresponseppent

- ivanleoncz