Postgres where子句比较时间戳 [英] Postgres where clause compare timestamp
问题描述
我有一个表,其中列的数据类型为 timestamp
I have a table where column is of datatype timestamp
其中包含一天的多个记录的记录
我想选择与日期对应的所有行
Which contains records multiple records for a day I want to select all rows corresponding to day
我该怎么做?
推荐答案
假设您实际上是指时间戳
,因为Postgres中没有 datetime
Assuming you actually mean timestamp
because there is no datetime
in Postgres
将timestamp列设置为日期,这将删除时间部分:
Cast the timestamp column to a date, that will remove the time part:
select *
from the_table
where the_timestamp_column::date = date '2015-07-15';
这将返回从7月15日开始的所有行。
This will return all rows from July, 15th.
请注意,以上内容将不使用 the_timestamp_column
上的索引。如果性能至关重要,则需要在该表达式上创建索引或使用范围条件:
Note that the above will not use an index on the_timestamp_column
. If performance is critical, you need to either create an index on that expression or use a range condition:
select *
from the_table
where the_timestamp_column >= timestamp '2015-07-15 00:00:00'
and the_timestamp_column < timestamp '2015-07-16 00:00:00';
这篇关于Postgres where子句比较时间戳的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!