How do I return a date value in postgres sql?

Question

For example:

year
-----
1999
2001
2004
2006

How do i return years that are greater than 2000?

Year is stored as date format.

OMG Ponies · Accepted Answer · 2011-02-06 07:10:11Z

3

Using PostgreSQL specific DATE_PART:

SELECT DATE_PART('year', t.date_column)
  FROM YOUR_TABLE t
 WHERE t.date_column > DATE '2000-01-01'

Using EXTRACT (ANSI):

SELECT EXTRACT(YEAR FROM t.date_column)
  FROM YOUR_TABLE t
 WHERE t.date_column > DATE '2000-01-01'

You could use the function in the WHERE clause to filter out the years/etc, but doing so renders an index on the column useless.

answered Feb 6, 2011 at 6:58

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

When using the EXTRACT in the WHERE clause, a function based index on extract(year from date_column) would be usable.

@a_horse_with_no_name: True, didn't know PostgreSQL supported them -- do you happen to know what the first version was to support them?

The manual of 6.5 already mentions the usage of functions in the CREATE INDEX syntax. So I'd say: "always"