Difference between EXTRACT(year from timestamp) function and date_part('year', timestamp) in PostgreSQL

Question

I am using PostreSQl server and have to get data grouped by date part of timestamp (ie. group by month or year or week) so I am using the Extract(year from timestamp) function but I saw in the Postgresql documentation that there is a function date_part('year',timestamp) too which can do the same. Here I confused which function I have to use and which function will be efficient. Also please tell me if there is another good way to get data groped by date part.

Answer 1

They are both the same.

In fact extract() gets re-written to date_part() - check the execution plan and you will see.

extract() complies with the SQL standard, date_part() is a Postgres specific query. As one gets converted to the other, there is absolutely no performance difference. If you prefer to write standard SQL, stick to extract()

Update 2021-12-16

Starting with Postgres 14 this is no longer the case, the implementation is now different:

The release notes state:

Change EXTRACT() to return type numeric instead of float8 (Peter Eisentraut).

This avoids loss-of-precision issues in some usages. The old behavior can still be obtained by using the old underlying function date_part().

Answer 2

I quote the answer from Postgresql.org

Blockquote The EXTRACT syntax ends up as a call to the internal date_part(...) function. If SQL-portability is not a concern, calling date_part() directly should be a bit quicker.

Reference Link