从时间戳列中选择一个日期范围 [英] Select a date range from a timestamp column

问题描述

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

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'.

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

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将提供介于'2011-09-13 11：03：44.537'和'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'.

但是如果我要使用：

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)?

即使该列具有带时间戳的日期，我也只需要带日期进行搜索（忽略时间戳或将小时，分钟和秒设为 0 ，例如'2011-09-13 00：00：00.0'）。

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').

推荐答案

如果要将字段的时间戳的值强制转换为 date ，则在另一个答案中建议，由于列中的每个单个值都必须进行转换以进行比较，并且无法使用简单索引，因此性能会降低。您必须在表达式上创建一个特殊的索引，像这样：

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

但是，据我所知，您的问题很简单您的查询中的thinko：

您切换了大写字母&下界。尝试：

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';

• 无需拼写秒和小数均为0。



• 您不希望包含 2011-09-13 00:00 ，因此请使用< 而不是< = 。
  
  不要使用 BETWEEN 在这里，出于同样的原因：

  • There is no need to spell out seconds and fractions that are 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:

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

    这将包括下一个00:00

    This would include 00:00 of the next day.

    还要注意，类型为 timestamp [无时区] 会根据您当前的时区设置进行解释。日期部分取决于该设置，通常应该可以按预期工作。更多详细信息：
    
    完全忽略时区Rails和PostgreSQL

    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

    这篇关于从时间戳列中选择一个日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！