如何将整天与datetime字段进行匹配？ [英] How do I match an entire day to a datetime field?

问题描述

我有一个比赛表。该表有一个名为 matchdate 的列，它是一个 datetime 字段。

如果我在2011-12-01有3场比赛：

• 2011-12- 01 12:00:00


• 2011-12-01 13:25:00


• 2011-12-01 16:00:00

如何查询？我如何查询1个单一日期的所有匹配？

我看过 date_trunc（）， to_char（）等

没有一些select * where datetime in date function ？

解决方案

转换你的 时间戳 值到 date 如果你想要简单的语法。像这样：

  SELECT * 
 FROM tbl 
 WHERE timestamp_col :: date ='2011-12-01'; - 日期文字
  

但是，使用大表格会更快：

  SELECT * 
 FROM tbl 
 WHERE timestamp_col> ='2011-12-01 0：0 ' - 时间戳文字
 AND timestamp_col< '2011-12-02 0：0' 
  

原因：第二个查询不需要转换表中的每个值，并可以使用简单的时间戳列上的索引。表达式是 sargable 。

注意排除上限（ <而不是 <= ），以获得正确的选择。

您可以通过在表达式索引>这样：

  CREATE INDEX tbl_ts_date_idx ON tbl（cast（timestamp_col AS date））; 
  

然后，查询的第一个版本将一样快。

I have a table for matches. The table has a column named matchdate, which is a datetime field.

If I have 3 matches on 2011-12-01:

• 2011-12-01 12:00:00
• 2011-12-01 13:25:00
• 2011-12-01 16:00:00

How do I query that? How do I query all matches on 1 single date?

I have looked at date_trunc(), to_char(), etc.
Isn't there some "select * where datetime in date" function?

解决方案

Cast your timestamp value to date if you want simple syntax. Like this:

SELECT *
FROM   tbl
WHERE  timestamp_col::date = '2011-12-01';  -- date literal

However, with big tables this will be faster:

SELECT *
FROM   tbl
WHERE  timestamp_col >= '2011-12-01 0:0'    -- timestamp literal
AND    timestamp_col <  '2011-12-02 0:0';

Reason: the second query does not have to transform every single value in the table and can utilize a simple index on the timestamp column. The expression is sargable.

Note excluded the upper bound (< instead of <=) for a correct selection.
You can make up for that by creating an index on an expression like this:

CREATE INDEX tbl_ts_date_idx ON tbl (cast(timestamp_col AS date));

Then the first version of the query will be as fast as it gets.

这篇关于如何将整天与datetime字段进行匹配？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！