Extract Month Year from date in PostgreSQL

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-1

 

 

Get 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-1

 

 

 

Get 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 date in postgresql

Student_detail:

Extract-Month-Year-from-date-in-postgresql-3

 

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 *, DATE_PART('decade', birthdaytime::timestamp) AS decade_part from Student_detail

so the resultant table will have month_year column

Extract-Month-Year-from-date-in-postgresql-2

 

 

 

Get Month Year from Date 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 monthyear from date in postgresql

Student_detail:

Extract-Month-Year-from-date-in-postgresql-3

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

Extract-Month-Year-from-date-in-postgresql-2

 

 

 

Author

  • Sridhar Venkatachalam

    With close to 10 years on Experience in data science and machine learning Have extensively worked on programming languages like R, Python (Pandas), SAS, Pyspark.

    https://www.linkedin.com/in/sridhar-venkatachalam-74414252/ karaimadai@sg2plcpnl0204.prod.sin2.secureserver.net Venkatachalam Sridhar