更新时间:2023-02-15 15:49:12
投射您的时间戳
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.