postgresql 从 WHERE 子句中的日期中提取年份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27440764/
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
Extract year from date within WHERE clause
提问by wiltomap
I need to include EXTRACT()function within WHEREclause as follow:
我需要EXTRACT()在WHERE子句中包含函数，如下所示：
SELECT * FROM my_table WHERE EXTRACT(YEAR FROM date) = '2014';
I get a message like this:
我收到这样的消息：
pg_catalog.date_part(unknown, text) doesn't exist** SQL State 42883
pg_catalog.date_part(unknown, text) doesn't exist** SQL State 42883
Here is my_tablecontent (gid INTEGER, date DATE):
这是my_table内容（gid INTEGER, date DATE）：
gid | date
-------+-------------
1 | 2014-12-12
2 | 2014-12-08
3 | 2013-17-15
I have to do it this way because the query is sent from a form on a website that includes a 'Year' field where users enter the year on a 4-digits basis.
我必须这样做，因为查询是从网站上的表单发送的，其中包含一个“年份”字段，用户可以在其中输入 4 位数字的年份。
回答by Erwin Brandstetter
The problem is that your column is of data typetext, while EXTRACT()only works for date/ timetypes.
问题是您的列是数据类型text，而EXTRACT()仅适用于date/time类型。
You should convert your column to the appropriate data type.
您应该将列转换为适当的数据类型。
ALTER TABLE my_table ALTER COLUMN date TYPE date;
That's smaller (4 bytes instead of 11 for the text), faster and cleaner (disallows illegal dates and most typos).
If you have non-standard format add a USINGclause with a conversion expression. Example:
它更小（文本为 4 个字节而不是 11 个字节）、更快、更清晰（不允许非法日期和大多数拼写错误）。
如果您有非标准格式，请添加USING带有转换表达式的子句。例子：
Also, for your queries to be fast with a plain indexon dateyou should rather use sargablepredicates. Like:
此外，为了让您的查询在普通索引上快速查询，date您应该使用sargable谓词。喜欢：
SELECT * FROM my_table
WHERE date >= '2014-01-01'
AND date < '2015-01-01';
Or, to go with your 4-digit input for the year:
或者，使用您的 4 位数输入来表示年份：
SELECT * FROM my_table
WHERE date >= to_date('2014', 'YYYY')
AND date < to_date('2015', 'YYYY');
You could also be more explicit:
你也可以更明确：
to_date('2014' || '0101', 'YYYYMMNDD')
Both produce the same date '2014-01-01'.
两者产生相同的日期'2014-01-01'。
Aside: dateis a reserved wordin standard SQL and a basic type name in Postgres. Don't use it as identifier.
Aside:date是标准 SQL 中的保留字，也是 Postgres 中的基本类型名称。不要将其用作标识符。
回答by Daniel Vérité
This happens because the column has a text or varchar type, as opposed to dateor timestamp. This is easily reproducible:
发生这种情况是因为该列具有 text 或 varchar 类型，而不是dateor timestamp。这很容易重现：
SELECT 1 WHERE extract(year from '2014-01-01'::text)='2014';
yields this error:
产生这个错误：
ERROR: function pg_catalog.date_part(unknown, text) does not exist
LINE 1: SELECT 1 WHERE extract(year from '2014-01-01'::text)='2014';
^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
错误：函数 pg_catalog.date_part(unknown, text) 不存在
LINE 1: SELECT 1 WHERE extract(year from '2014-01-01'::text)='2014';
^ 提示：没有函数匹配给定的名称和参数类型。您可能需要添加显式类型转换。
extractor is underlying function date_partdoes not exist for text-like datatypes, but they're not needed anyway. Extracting the year from this date format is equivalent to getting the 4 first characters, so your query would be:
extract或者date_part对于类似文本的数据类型不存在底层函数，但无论如何都不需要它们。从此日期格式中提取年份相当于获取前 4 个字符，因此您的查询将是：
SELECT * FROM my_table WHERE left(date,4)='2014';
