In order to add years to date in PostgreSQL we will using two approach one by using the + operator and other using the Interval. datatype. Here are examples of both approaches. Also, we will look at how to Add years to datetime column in PostgreSQL table.
Add years to date in PostgreSQL using INTERVAL data type:
We will be Using the INTERVAL data type to add years to datetime in PostgreSQL. When we add years to datetime using INTERVAL datatype the output will be in datetime(timestamp) format. In below example, we have added 1 year to date using Interval data type.
Example 1:
SELECT '2024-02-24'::date + INTERVAL '1 Years' as new_date
Output:
Add Years to datetime in PostgreSQL using make_interval() function:
We will be Using the make_interval() function to add years to date in PostgreSQL. When we add years to date using the make_interval() function, the output will be in datetime(timestamp) format. In below example, we have added 1 year to date
SELECT '2024-02-24'::date + make_interval(years => 1) as new_date
Example 1:
Output:
Add years to date in PostgreSQL using simple addition (+) operator (roundabout way) :
As mentioned, it’s a roundabout way. In the below example we have used simple addition to Add years to date in PostgreSQL. We have added 366 days (2024 is leap year) (which is 1 year) to date in below example.
Example 1:
SELECT '2024-02-24'::date + 366 as new_date
Output:
Add years to datetime column in PostgreSQL table using Interval datatype:
We will be using below student_detail1 table for our example to depict on how to add years to datetime(timestamp) column in postgresql table.
student_detail1:
We have added 2 Years to datetime column using INTERVAL datatype in postgresql and new column named new_date is being created as shown below.
select *, birthdaytime::timestamp  + INTERVAL '2 YEARS' as new_date from student_detail1
Output:
Add years to datetime column in PostgreSQL table using make_interval() function:
We have added 2 years to datetime column using make_interval() function in postgresql and new column named new_date is being created as shown below.
select *,birthdaytime + make_interval(years => 2) as new_date from student_detail1
Output:
Add Years to date column in PostgreSQL table using Additional (+) operator (roundabout way) :
Again it’s a roundabout method , We will have added 731 days (2 years) to date column using simple addition operator (+) in postgresql and new column named new_date is being created as shown below.
SELECT *, birthdaytime::date + 731 as new_date from student_detail1
Output:
