Extract year from date within WHERE clause

Question

I need to include EXTRACT() function within WHERE clause as follow:

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

Here is my_table content (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.

So what's the question? Is it not working as you expected? Does this produce an error message? — Mureinik, Dec 12 '14 at 09:44
This query works fine on my machine. What version of postgres are you using? — Mureinik, Dec 12 '14 at 09:48
`extract` definitely existed in 9.2.2. Weird. Just for the fun of it, could you try with `date_part('year', date) = '2014'` instead of the `extract` expression you're currently using? — Mureinik, Dec 12 '14 at 09:54
Your solution doesn't work. I solved it by forcing the type of DATE column : `SELECT * FROM my_table WHERE EXTRACT(YEAR FROM date::TIMESTAMP) = '2014';`. Thanks for help! — wiltomap, Dec 12 '14 at 09:57
A normal index on field "date" doesn't help performance. Use the same EXTRACT() definition to create a functional index or use a BETWEEN-condition in your WHERE instead of the EXTRACT-condition. — Frank Heikens, Dec 12 '14 at 10:13
Note: `date` is a word used in the sql syntax. I don't know if it is a reserved word, but you should avoid using it as an identifier. — joop, Dec 12 '14 at 10:28

Erwin Brandstetter · Answer 1 · 2023-04-05T14:36:30.790

Your column is of data type text, while EXTRACT() only works for date / time types.

You should convert your column to the appropriate data type.

ALTER TABLE my_table ALTER COLUMN date TYPE date;

That's just 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 USING clause with a conversion expression. Example:

Alter character field to date

For your queries to be fast with a plain index on date you should use sargable predicates. Like:

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:

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', 'YYYYMMDD')

Either produces the same date '2014-01-01'.

Aside: date is a reserved word in standard SQL and a basic type name in Postgres. Don't use it as identifier.

score 3 · Answer 2 · answered Dec 12 '14 at 12:28

This happens because the column has a text or varchar type, as opposed to date or timestamp. This is easily reproducible:

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.

extract or is underlying function date_part does 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:

SELECT * FROM my_table WHERE left(date,4)='2014';

Extract year from date within WHERE clause

2 Answers2