从时间戳列中选择日期范围

Question

目前我的表有一列用于存储日期和时间。该列的数据类型是无时区的时间戳。因此它的值格式为 '2011-09-13 11:03:44.537'。

我需要检索有关日期的行。如果我使用：

select * from table where mdate >= '2011-09-13 11:03:44.537'
                      and mdate <= '2011-09-12 11:03:44.537'

它将提供“2011-09-13 11:03:44.537”和“2011-09-12 11:03:44.537”之间的值。

但如果我要使用：

select * from table where mdate >= '2011-09-13 00:00:00.0'
                      and mdate <= '2011-09-12 00:00:00.0'

没有日期、月份和秒，它不会显示任何行。

如何从此表中获取与日期相关的值（仅包含日期，忽略小时、分钟和秒）？

即使该列有带时间戳的日期，我也只需要使用日期来搜索它们（忽略时间戳或将小时、分钟和秒设置为 0 例如 '2011-09-13 00： 00:00.0')。

Answer 1

如果您要将字段的 timestamp 值转换为 date（如另一个答案中所建议的那样），性能将会下降，因为列中的每个值都需要转换为不能使用比较和简单索引。您必须在表达式上创建一个特殊的 索引，如下所示：

CREATE INDEX some_idx ON tbl (cast(mdate AS date));

在这种情况下，查询也应该简化为：

SELECT * FROM tbl
WHERE  mdate::date = '2011-09-12'::date; -- 2nd cast optional

但是，据我所知，您的问题是查询中的一个简单的拼写错误/思考：
您切换了上层&下限。尝试：

SELECT * FROM tbl
WHERE  mdate >= '2011-09-12 00:00:00.0'
AND    mdate <= '2011-09-13 00:00:00.0';

或者，为了简化语法并更精确：

SELECT * FROM tbl
WHERE  mdate >= '2011-09-12 00:00'
AND    mdate <  '2011-09-13 00:00';

• 无需拼写秒和 0 的分数。
• 您不想包含 2011-09-13 00:00，因此请使用 < 而不是 <=。
  出于同样的原因，请勿在此处使用 BETWEEN：

WHERE mdate BETWEEN '2011-09-12 00:00' AND '2011-09-13 00:00'

这将包括第二天的 00:00。

另请注意，timestamp [without time zone] 类型的列将根据您当前的时区设置进行解释。日期部分取决于该设置，通常应按预期工作。更多详细信息：

• 在 Rails 和 PostgreSQL 中完全忽略时区< /a>

Answer 2

只需将时间戳视为日期：

select * 
from table 
where mdate::date >= DATE '2011-09-12' 
  and mdate::date <= DATE '2011-09-13'

表达式 mdate::date 会将时间戳转换为 date 类型，这将从值中删除时间部分。

DATE '2011-09-13' 是一个（标准）DATE 文字，它比简单地编写“2011-09-13”更健壮，因为它不受任何语言设置的影响。