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

Currently my table has a column to store date and time. The data type of that column is timestamp without time zone. So it has values in the format '2011-09-13 11:03:44.537'.

目前，我的表有一个列来存储日期和时间。该列的数据类型是没有时区的时间戳。所以它的值是“2011-09-13 11:03:46 .537”。

I need to retrieve rows with respect to the date. If I use:

我需要检索关于日期的行。如果我使用:

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

it will provide the values which are in between '2011-09-13 11:03:44.537' and '2011-09-12 11:03:44.537'.

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

But if I am going with:

但是如果我说

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

without date, month and seconds, It is not displaying any rows.

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

How to fetch values from this table with respect to the date (only with date, ignoring hour, minutes and seconds)?

如何从该表中获取与日期相关的值(仅对日期，忽略小时、分钟和秒)?

Even, the column has date with timestamp, I need to search them only with date (ignoring timestamp or making the hour, minutes and seconds to 0 such as '2011-09-13 00:00:00.0').

即使是，这个列也有时间戳，我只需要用日期搜索它们(忽略时间戳，或者把小时、分钟和秒变成0，比如“2011-09-13 00:00:00”)。

2 个解决方案

#1

If you are going to cast the timestamp values of the field to date, as suggested in another answer, performance will degrade because every single value in the column needs to be cast for comparison and simple indexes cannot be used. You would have to create a special index on the expression, like so:

如果您要按照另一个答案中建议的那样，对字段的时间戳值进行强制转换，那么性能将会下降，因为需要对列中的每个值进行强制转换以进行比较，并且不能使用简单的索引。您必须在表达式上创建一个特殊的索引，如下所示:

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

In this case the query should also be simplified to:

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

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

However, as far as I can see, your problem is a simple typo / thinko in your query:
You switched upper & lower boundaries. Try:

然而，在我看来，您的问题是查询中的一个简单的错误/问题:您切换了上下边界。试一试:

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

Or, to simplify your syntax and be more precise:

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

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

There is no need to spell out seconds and fractions that are 0.
没有必要把秒和分数都写成0。
You don't want to include 2011-09-13 00:00, so use < instead of <=.
Don't use BETWEEN here, for the same reason:

您不想包含2011-09-13 00:00，所以使用 <而不是<=。不要在这里使用，因为同样的原因:< p>
```
    
    
    
      WHERE mdate BETWEEN '2011-09-12 00:00' AND '2011-09-13 00:00' 
    
```
This would include 00:00 of the next day.

这包括第二天的00:00。

Also be aware that a column of type timestamp [without time zone] is interpreted according to your current time zone setting. The date part depends on that setting, which should generally work as expected. More details:
Ignoring timezones altogether in Rails and PostgreSQL

还要注意的是，一个类型为timestamp的列(没有时区)会根据当前时区设置进行解释。日期部分取决于该设置，该设置通常应按预期工作。更多细节:在Rails和PostgreSQL中完全忽略时区

#2

Just treat the timestamp as a date:

把时间戳当作一个日期:

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

The expression mdate::date will cast the timestamp to a date type which will remove the time part from the value.

日期将把时间戳转换为日期类型，该日期类型将从值中删除时间部分。

DATE '2011-09-13' is a (standard) DATE literal which is a bit more robust than simply writing '2011-09-13' as it isn't affected by any language settings.

日期“2011-09-13”是一个(标准的)日期文字，比简单地写“2011-09-13”更有信心，因为它不受任何语言设置的影响。

#1

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