如何将一整天与日期时间字段匹配? [英] How do I match an entire day to a datetime field?

问题描述

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

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

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

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

我如何查询?如何查询 1 个日期的所有匹配项?

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

我看过date_trunc()、to_char()等
不是有一些"select * where datetime in date" 函数吗?

推荐答案

投射您的时间戳 value 到 date 如果你想要简单的语法.像这样:

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

原因:第二个查询不必转换表中的每个值，并且可以在时间戳列上使用一个简单的索引.表达式是 sargable.

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.

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