PostgreSQL: How to Extract Year from Date

Often in PostgreSQL you may want to extract a year from a date column.

You can use the following basic syntax to do so:

SELECT extract(year FROM start_date) AS year
FROM emp;

This particular example will extract the year from the date in the start_date column of the table named emp and save the results to a new column named year.

The following example shows how to use this syntax in practice.

Example: How to Extract Year from Date in PostgreSQL

Suppose that we create the following table named emp that contains information about various employees at some company including their starting date and their total sales made:

-- create table named 'emp'
CREATE TABLE emp (
  empID INT PRIMARY KEY,
  start_date DATE,
  sales INT
);

-- add rows to the table 
INSERT INTO emp VALUES (0001, '2021-04-01', 134);
INSERT INTO emp VALUES (0002, '2022-07-01', 201);
INSERT INTO emp VALUES (0003, '2022-08-15', 155);
INSERT INTO emp VALUES (0004, '2022-09-01', 98);
INSERT INTO emp VALUES (0005, '2023-11-23', 65);
INSERT INTO emp VALUES (0006, '2024-02-20', 52);
INSERT INTO emp VALUES (0007, '2024-05-15', 50);

-- view all rows in table
SELECT * FROM emp;

 empid | start_date | sales 
-------+------------+-------
     1 | 2021-04-01 |   134
     2 | 2022-07-01 |   201
     3 | 2022-08-15 |   155
     4 | 2022-09-01 |    98
     5 | 2023-11-23 |    65
     6 | 2024-02-20 |    52
     7 | 2024-05-15 |    50

Suppose that we would like to extract the year from each date in the start_date column.

We can use the following syntax to do so:

SELECT empID,
       start_date,
       extract(year FROM start_date) AS year
FROM emp;

 empid | start_date | year 
-------+------------+------
     1 | 2021-04-01 | 2021
     2 | 2022-07-01 | 2022
     3 | 2022-08-15 | 2022
     4 | 2022-09-01 | 2022
     5 | 2023-11-23 | 2023
     6 | 2024-02-20 | 2024
     7 | 2024-05-15 | 2024

The new column named year contains only the year from the date in the start_date column.

For example, we can see:

• Employee 1 started in 2021.
• Employee 2 started in 2022.
• Employee 3 started in 2022.

And so on.

Feel free to use similar syntax to extract the year from a date column in your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Select Rows in Date Range
PostgreSQL: How to Select the Last Row per Group
PostgreSQL: How to Calculate Difference Between Rows