SQL Postgresql - 选择日期=“01/01/11”的内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5875712/
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 - select something where date = "01/01/11"
提问by Abramodj
I have a datetime field in my Postgresql, named "dt". I'd like to do something like
我的 Postgresql 中有一个日期时间字段，名为“dt”。我想做类似的事情
SELECT * FROM myTable WHERE extract (date from dt) = '01/01/11'
What is the right syntax to do that?
这样做的正确语法是什么？
Thanks!
谢谢！
回答by mu is too short
I think you want to cast your dtto a dateand fix the format of your date literal:
我认为您想将您的内容转换dt为 adate并修复日期文字的格式：
SELECT *
FROM table
WHERE dt::date = '2011-01-01' -- This should be ISO-8601 format, YYYY-MM-DD
Or the standard version:
或标准版：
SELECT *
FROM table
WHERE CAST(dt AS DATE) = '2011-01-01' -- This should be ISO-8601 format, YYYY-MM-DD
The extractfunctiondoesn't understand "date" and it returns a number.
该extract函数不理解“日期”，它返回一个数字。
回答by Hugh
With PostgreSQL there are a number of date/time functions available, see here.
PostgreSQL 有许多可用的日期/时间函数，请参见此处。
In your example, you could use:
在您的示例中，您可以使用：
SELECT * FROM myTable WHERE date_trunc('day', dt) = 'YYYY-MM-DD';
If you are running this query regularly, it is possible to create an index using the date_truncfunction as well:
如果您定期运行此查询，也可以使用该date_trunc函数创建索引：
CREATE INDEX date_trunc_dt_idx ON myTable ( date_trunc('day', dt) );
One advantage of this is there is some more flexibility with timezones if required, for example:
这样做的一个优点是，如果需要，时区具有更大的灵活性，例如：
CREATE INDEX date_trunc_dt_idx ON myTable ( date_trunc('day', dt at time zone 'Australia/Sydney') );
SELECT * FROM myTable WHERE date_trunc('day', dt at time zone 'Australia/Sydney') = 'YYYY-MM-DD';
