PostgreSQL - 过滤日期范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15817871/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL- Filter a date range
提问by Rob S
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.
我是一名 SQL 开发人员，大部分时间都花在 MSSQL 上。我正在寻找一种更好的方法来过滤 PostgreSQL 数据库中的“没有时区的时间戳”字段。
I'm using:
我正在使用：
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.
但鉴于我不是语法专家，我不得不认为有更好的方法。
Any Suggestions? Thanks.
有什么建议？谢谢。
回答by leonbloy
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'
... 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.
...期望（但失败）在查询中包含一整天的“2012-01-01”。使用后面的语法，意图更加明确，避免了这种混淆。
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
I wouldn't use to_date()here for similar reasons (potential datatype confusion), nor to_timestamp()(it returns a timestamptz).
to_date()由于类似的原因（潜在的数据类型混淆），我不会在这里使用，也不会to_timestamp()（它返回 a timestamptz）。
BTW, I've modified the case to comply with recommended practice (keywords in uppercase, identifiers in lowercase)
顺便说一句，我已经修改了案例以符合推荐的做法（大写的关键字，小写的标识符）
回答by Igor Romanchenko
For date intervals you can use something like:
对于日期间隔，您可以使用以下内容：
WHERE DateField BETWEEN to_date('2010-01-01','YYYY-MM-DD')
AND to_date('2010-01-02','YYYY-MM-DD')
It is shorter (you do not need to repeat DateField), and has explicit date format.
它更短（您不需要重复DateField），并且具有明确的日期格式。
For 1 hour/day/month/year you can use:
对于 1 小时/天/月/年，您可以使用：
WHERE date_trunc('day',DateField) = to_date('2010-01-01','YYYY-MM-DD')
回答by Phil Andrews
You can keep the query simple by using BETWEENas long as your column name is of type TIMESTAMPand your column name isn't "timestamp"...
BETWEEN只要您的列名是类型TIMESTAMP并且您的列名不是“时间戳”，您就可以通过使用来保持查询简单......
SELECT * FROM table WHERE column BETWEEN '2018-12-30 02:19:34' AND '2018-12-30 02:25:34'
SELECT * FROM table WHERE column BETWEEN '2018-12-30 02:19:34' AND '2018-12-30 02:25:34'
This works for dates '2018-12-30' and date-times '2018-12-30 02:19:34'.
这适用于日期“2018-12-30”和日期时间“2018-12-30 02:19:34”。
