Get Month from Date or Timestamp in Postgresql

In order to Get Month from Date or Timestamp in postgresql we use Extract() function . Within the Extract() function we have to mention MONTH as we are getting month from timestamp. To extract the month from a date in PostgreSQL, you can also use the DATE_PART() function with the ‘month’ unit.

Let’s see how to

  • Extract month from Timestamp in Postgresql
  • Create the column which extracts month from timestamp column

With an example for both

 

Extract Month from Date or Timestamp in Postgresql:

Within the Extract keyword we have to mention MONTH as we are getting month from timestamp

SELECT EXTRACT (MONTH FROM TIMESTAMP '2010-11-22 13:30:15')

So the resultant month will be

Get Month from timestamp 2

 

 

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”.

 

Extract Month from Date in postgresql Simple Example:

In order to Extract month from date in postgresql we will be using  DATE_PART() function.

In the below Example we will be passing month as “unit” argument and date as “source” argument in order to extract month from Date or datetime


SELECT DATE_PART('month', '2024-02-24'::timestamp) AS month_part;

Output:

Extract-month-from-date-in-PostgreSQL-1

 

 

Extract Month from Current date in postgresql :

This query uses the CURRENT_DATE function to get the current date and then extracts the month from current date using DATE_PART()

SELECT CURRENT_DATE AS current_date, DATE_PART('month', CURRENT_DATE) AS month_part;

Output:

Extract-month-from-date-in-PostgreSQL-2

 

 

 

Extract Month part from Date in postgresql table:

We will be using  Below Student_detail table for our example to depict on how to extract month part from date in postgresql

Student_detail:

Extract-month-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. Which will extract month part from “birthdaytime” column and store in the new column of postgresql table as shown below

select *, DATE_PART('month', birthdaytime::timestamp) AS month_part from Student_detail

so the resultant table will have month_part column

Extract-month-from-date-in-PostgreSQL-4

 

 

Extract Month part from Date in postgresql table using Extract():

We will be using Below Student_detail table for our example to depict on how to extract Month part from date in postgresql

Student_detail:

Extract-month-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. Which will extract Month from “birthdaytime” column and store in the new column of postgresql table as shown below

Select *, extract(MONTH FROM birthdaytime) as month_part from student_detail

So the resultant table will have month_part column

Extract-month-from-date-in-PostgreSQL-4

 

                                                                                                           

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