To extract the Month year from a date in PostgreSQL, you can use the DATE_PART() function with the ‘month’ and ‘year’ unit. To Extract Month year from date we will also be using Extract() Function
Syntax for DATE_PART() Function in PostgreSQL:
DATE_PART(unit, source)
unit: Specifies the part of the date or timestamp to extract (e.g., ‘year’, ‘month’, ‘day’, ‘hour’, ‘minute’, ‘second’, etc.).
source: The date or timestamp from which to extract the specified part.
In Our case. The values of the unit must be “month” and “year”.
Extract Month Year from Date in postgresql – DATE_PART() function :
In order to Extract Month Year from date in postgresql we will be using DATE_PART() function.
In the below Example we will be passing month & year as “unit” argument and date as “source” argument in order to extract month year from Date or datetime
SELECT concat(DATE_PART('month', '2024-02-24'::timestamp) ,'-', DATE_PART('year', '2024-02-24'::timestamp)) AS month_year;
Output:
Extract Month Year from Date in postgresql – Extract() function :
In order to Extract Month Year from date in postgresql we will also be using EXTRACT() function.
In the below Example we will be passing month & year as “unit” argument and date as “source” argument in order to extract month year from Date or datetime
SELECT concat(Extract (Month from '2024-02-24'::timestamp) ,'-', Extract (Year from '2024-02-24'::timestamp)) AS month_year;
Output:
Extract Month Year from Date in PostgreSQL table using DATE_PART(): Method 1
We will be using below Student_detail table for our example to depict on how to extract month year from timestamp in postgresql
Student_detail:
In the above table we will be using DATE_PART() Function, which will take “month” and column named “birthdaytime” as argument. Another Function, which will take “Year” and column named “birthdaytime” as argument. And concatenate both Which will extract Month Year column and store in the new column of postgresql table as shown below
select *,concat( DATE_PART('month', birthdaytime::timestamp),'-',DATE_PART('year', birthdaytime::timestamp)) AS month_year from Student_detail
so the resultant table will have month_year column
Extract Month Year from Datetime in postgresql table using EXTRACT() function: Method 2
We will be using below Student_detail table for our example to depict on how to extract month year from datetime in postgresql
Student_detail:
In the above table we will be using EXTRACT() Function, which will take “month” and column named “birthdaytime” as argument. Another Function, which will take “Year” and column named “birthdaytime” as argument. And concatenate both Which will extract Month Year column and store in the new column of postgresql table as shown below
SELECT * ,concat(Extract (Month from birthdaytime::timestamp) ,'-', Extract (Year from birthdaytime::timestamp)) AS month_year from student_detail
so the resultant table will have month_year column
