PostgreSQL-过滤日期范围 [英] PostgreSQL- Filter a date range
问题描述
我是一名SQL开发人员,大部分时间都花在MSSQL上。
我正在寻找一种更好的方法来过滤PostgreSQL数据库中的无时区的时间戳字段。
I'm a SQL developer and spend most of my time in MSSQL. I'm looking for a better way to filter a "Timestamp without timezone" field in a PostgreSQL DB.
我正在使用:
Where
DateField >= '2010-01-01' and
DateField < '2012-01-01'
但是考虑到我不是语法专家认为有更好的方法。
But given that I'm not an expert at the syntax I have to think there's a better way.
有什么建议吗?谢谢。
推荐答案
您的解决方案很好。如果日期是文字,那么我还是希望:
Your solution is fine. If the dates are literals, I'd prefer, though:
WHERE datefield >= '2010-01-01 00:00:00'
AND datefield < '2012-01-01 00:00:00'
此功能完全相同,但更多可维护的,因为它明确了每个文字日期的要点是时间戳,而不是日期。例如,假设某个时候有人将您的查询更改为以下内容
This performs exactly the same, but is more maintenable, because it makes clear the point of each literal "date" being a timestamp, not a date. For example, suppose sometime someone changes your query to the following
AND datefield <= '2012-01-01'
...期望(且失败)在查询中包括整天 2012-01-01 。使用更高版本的语法,目的更加明确,并且可以避免这种混淆。
... expecting (and failing) to include the full day "2012-01-01" in the query. With the later syntax, the intention is more clear and this confusion is prevented.
要使其更加清晰(也许太冗长),可以执行显式强制转换:
To make it even more clear (perhaps too verbose), you can do the explicit cast:
WHERE datefield >= '2010-01-01 00:00:00'::timestamp
AND datefield < '2012-01-01 00:00:00'::timestamp
我不会使用 to_date()
出于类似原因(潜在的数据类型混乱),也没有 to_timestamp()
(它返回 timestamptz
)。
I wouldn't use to_date()
here for similar reasons (potential datatype confusion), nor to_timestamp()
(it returns a timestamptz
).
顺便说一句,我已经修改了大小写,以符合建议的做法(关键字大写,标识符小写)
BTW, I've modified the case to comply with recommended practice (keywords in uppercase, identifiers in lowercase)
这篇关于PostgreSQL-过滤日期范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!