Get day of week from date or Timestamp in Postgresql

In order to Get day of week from date or Timestamp in postgresql we use Extract() function . Within the Extract keyword we have to mention DOW as we are getting day of week from timestamp. To extract the day of the week from a date in PostgreSQL, you can use the DATE_PART() function with the ‘dow’ (day of the week) unit. where  0 Represents Sunday, 1 Represents Monday and so on

 

Let’s see how to

  • Extract get day of week from Timestamp in Postgresql
  • Create the column which extracts day of week from date or timestamp column

With an example for both

 

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 “dow” (day of the week). The output will be in numbers where  0 Represents Sunday, 1 Represents Monday and so on. Refer below table for more details

Day of Week Day Notation
0 Sunday
1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday

 

 

Extract Day of week from Date in PostgreSQL Simple with DATE_PART() function:

In order to Extract day of week from date in PostgreSQL we will be using  DATE_PART() function.

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

SELECT DATE_PART('dow', '2024-02-24'::timestamp) AS day_of_week_part;

Output:

Extract-day-of-week-from-date-in-PostgreSQL-1

 

Get Day of week from Current date in PostgreSQL :

This query uses the CURRENT_DATE function to get the current date and then extracts the day of the week using DATE_PART()

SELECT CURRENT_DATE AS current_date, DATE_PART('dow', CURRENT_DATE) AS day_of_week;

Output:

Extract-day-of-week-from-date-in-PostgreSQL-2

 

 

Extract day of week from Timestamp in Postgresql with extract() function

Within the Extract keyword we have to mention DOW as we are getting day of week from timestamp

SELECT EXTRACT (DOW FROM TIMESTAMP '2011-11-22 13:30:15')

So the resultant day of week will be

Extract Day of the week from timestamp in postgresql 2

 

 

 

 

EXTRACT day of week from Timestamp in column of Postgresql table:

We use table student_detail

Extract-day-of-week-from-date-in-PostgreSQL-3

 

Create the column which extracts DOW from timestamp column in Postgresql

We have created a column and stored Day of week from Birthdaytime column by using Extract() function, we have to mention DOW as we are getting day of week from timestamp

SELECT *,EXTRACT (DOW FROM Birthdaytime) AS DOW FROM Student_detail

So the resultant table will be

Extract Day of the week from timestamp in postgresql 3

 

 

 

Extract Day of week from Date in PostgreSQL table using DATE_PART() :

We will be using  Below Student_detail table for our example to depict on how to extract day of week from date in PostgreSQL

Student_detail:

Extract-day-of-week-from-date-in-PostgreSQL-3

 

In the above table we will be using  DATE_PART() Function, which will take “dow” (day of week)  and column named “birthdaytime” as argument. Which will extract day of the week from “birthdaytime” column and store in the new column of PostgreSQL table as shown below

select *, DATE_PART('dow', birthdaytime::timestamp) AS day_of_week from Student_detail

so the resultant table will have day_of_week column

Extract-day-of-week-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