In order to Subtract months from 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 Subtract months from datetime column in PostgreSQL table.
Subtract months from datetime in PostgreSQL using INTERVAL data type:
We will be Using the INTERVAL data type to Subtract months from date in PostgreSQL. When we Subtract months from date using INTERVAL datatype the output will be in datetime(timestamp) format. In below example, we have Subtracted 3 months from date using Interval data type.
Example 1:
SELECT '2024-02-24'::date - INTERVAL '3 Months' as new_date
Output:
Subtract Months from datetime in PostgreSQL using make_interval() function:
We will be Using the make_interval() function to Subtract months from datetime in PostgreSQL. When we Subtract months from date using the make_interval() function, the output will be in datetime(timestamp) format. In below example, we have Subtracted 3 months from date
Example 1:
SELECT '2024-02-24'::date - make_interval(months => 3) as new_date
Output:
Subtract months from date in PostgreSQL using simple Subtraction (-) operator (roundabout way) :
As mentioned, it’s a roundabout way. In the below example we have used simple Subtraction to Subtract months from date in PostgreSQL. We have Subtracted 92 days (which is 3 months) from date in below example.
Example 1:
SELECT '2024-02-24'::date - 92 as new_date
Output:
Subtract months from datetime column in PostgreSQL table using Interval datatype:
We will be using below student_detail1 table for our example to depict on how to Subtract months from date column in postgresql table.
student_detail1:
We have Subtracted 3 Months from date column using INTERVAL datatype in postgresql and new column named new_date is being created as shown below.
select *, birthdaytime::timestamp - INTERVAL '3 MONTHS' as new_date from student_detail1
Output:
Subtract Months from datetime column in PostgreSQL table using make_interval() function:
We will have Subtracted 3 months from date column using make_interval() function in postgresql and new column named new_date is being created as shown below.
select *,birthdaytime - make_interval(months => 3) as new_date from student_detail1
Output:
Subtract Months from date column in PostgreSQL table using Subtraction (-) operator (roundabout way) :
Again it’s a roundabout method , We will have Subtracted 92 days (3 months) from date column using simple subtraction operator (-) in postgresql and new column named new_date is being created as shown below.
SELECT *, birthdaytime::date - 92 as new_date from student_detail1
Output:
