Dates in any programming language are tricky to work with, and PostgreSQL is no exception. Dates manipulation is an essential skill for any developer, whether it is extracting the year from a database, date of birth field, or dealing with timestamp data in a server log.

In Postgres, extracting the year from a date value is a common task when working with dates. It can be used to group data by year, create date ranges, or simply display the year.

Several approaches are available in Postgres to extract the year from a date, and in this tutorial, we'll show you the most convenient and efficient ones. For this purpose, the following topics will be covered in this blog:

• How to Extract a Year From DATE in Postgres?
• How to Extract a Year From Current Date Using EXTRACT() Function?
• How to Extract a Year From Current Date Using DATE_PART() Function?
• How to Extract a Year From a Specific TIMESTAMP Via EXTRACT() Function?
• How to Extract a Year From a Specific TIMESTAMP Via DATE_PART() Function?
• How to Extract a Year From a Specific INTERVAL Using EXTRACT() Function?
• How to Extract a Year From a Specific INTERVAL Using DATE_PART() Function?
• How to Group Table Records by Year in Postgres?

So, let’s get started!

How to Extract a Year From DATE in Postgres?

To extract a year from a date, the built-in EXTRACT() and DATE_PART() functions are used in Postgres. For instance, to extract the year from a date in Postgres via the EXTRACT() function, you can use the following syntax:

SELECT EXTRACT('dateField' FROM TIMESTAMP | INTERVAL );

Similarly, to extract the year from a date in Postgres via the DATE_PART() function, users need to follow the below syntax:

SELECT DATE_PART('dateField', TIMESTAMP | INTERVAL );

In place of dateField, you must specify the “Year” to extract the year from the given timestamp or interval.

How to Extract a Year From Current Date Using EXTRACT() Function?

In Postgres, the NOW() and CURRENT_DATE functions are used to get the current date. So, we will pass one of these functions and a field to be extracted (i.e., “Year” in our case) to the EXTRACT() function to extract the desired date field from the current date:

SELECT EXTRACT('Year' FROM CURRENT_DATE);

The output shows that the current year is “2022”.

How to Extract a Year From Current Date Using DATE_PART() Function?

To extract the desired date field from the current date, let’s pass a field to be extracted (i.e., “Year” in our case) as a first argument and a NOW() function as the second argument to the DATE_PART() function:

SELECT DATE_PART('Year', NOW());

The output verifies the working of the DATE_PART() function as it retrieves the appropriate result.

How to Extract a Year From a Specific TIMESTAMP Via EXTRACT() Function?

Specify the timestamp and the field of your choice to the EXTRACT() function to extract a year from the specified timestamp:

SELECT EXTRACT('Year' FROM TIMESTAMP '2010/12/12');

The year has been successfully extracted from the specified date via Postgres’ EXTRACT() function.

How to Extract a Year From a Specific TIMESTAMP Via DATE_PART() Function?

To extract a particular year from a specific TIMESTAMP, you need to pass the “Year" as the first argument and the timestamp as the second argument to the DATE_PART() function:

SELECT DATE_PART('Year', TIMESTAMP '2015-12-12');

The output snippet clarifies that the year has been successfully extracted from the specified timestamp via the DATE_PART function.

How to Extract a Year From a Specific INTERVAL Using EXTRACT() Function?

To extract a year from a specific interval, you can use the EXTRACT() function as follows:

SELECT EXTRACT(YEAR FROM INTERVAL '16 years 10 months 3 days');

The output snippet shows that the specified “year” has been successfully extracted from the given interval successfully.

How to Extract a Year From a Specific INTERVAL Using DATE_PART() Function?

Let’s pass a “YEAR” as a first argument and a specific interval as a second argument to the DATE_PART() function:

SELECT DATE_PART('YEAR', INTERVAL '10 years 7 months 3 days');

From the above snippet, you can notice that the year has been successfully extracted from the specified interval.

How to Group Table Records by Year in Postgres?

Postgres' DATE_PART() and EXTRACT() functions can be used to group data by year. For instance, in the below snippet, we will use the DATE_PART() function to extract a year from the specified table’s column and then group these records by year:

SELECT DATE_PART('YEAR', published_date) AS published_year,
COUNT(article_id) AS count
FROM article_details
GROUP BY DATE_PART('YEAR', published_date);

Similarly, you can use the EXTRACT() function instead of the DATE_PART() function to extract a year from the specified column.

That’s all from this Postgres guide!

Conclusion

To extract a year from a date, the built-in EXTRACT() and DATE_PART() functions are used in Postgres. To do so, you need to pass the “YEAR” as the first argument and the timestamp/interval as the second argument to any of these functions. Consequently, the year from the specified date fields will be extracted. This Postgres blog presented a detailed guide on extracting a year from a date in Postgres via practical examples.