How to Extract Day of Week From Date Field in PostgreSQL?

In PostgreSQL, extracting the day of the week from a date field is a common and essential task when working with temporal data. Knowing the day of the week is crucial for various applications, including scheduling, reporting, and analytical purposes. PostgreSQL provides multiple methods to achieve this, offering flexibility based on specific requirements.

This article explores three common techniques: using the EXTRACT function, the TO_CHAR function, and the DATE_PART function, each catering to different scenarios for extracting day-of-week information from date fields in PostgreSQL.

How to Extract the Day of the Week from Date Fields Using PostgreSQL?

In PostgreSQL, you can extract the day of the week from a date field using various methods. Here are some common ways to achieve this :

• Using the EXTRACT Function
• Using the TO_CHAR Function
• Using the DATE_PART Function

To understand How to Extract day of week from date field in PostgreSQL we need a table on which we will perform various operations. So we create a table example_table.

Creating the table example_table

CREATE TABLE example_table (
    sample_dates date
);

Inserting values in example_table

INSERT INTO example_table (sample_dates) 
VALUES ('2024-02-07'), ('2024-02-08'), ('2024-02-09');

Using the Extract() Function

In PostgreSQL, using the EXTRACT function and the ‘DOW’ (day of the week) specifier, you may extract the day of the week from a date field. The outcome will be a number that indicates the day of the week, with Saturday being 6 and Sunday being 0.

Syntax:

SELECT EXTRACT(DOW FROM column_name) FROM table_name;

Here replace table_name with the name of the table and column_name with name of the column that has date data type.

Example: Extracting Day of Week from Date Field in PostgreSQL

SELECT sample_dates,EXTRACT(DOW FROM sample_dates) AS day_of_week
FROM example_table;

Output:

Output for Extract function

Explanation: The table below shows the representation of output numbers concerning corresponding days in a week.

Using the TO_CHAR() Function

In PostgreSQL, you can format a date or timestamp as a string by using the TO_CHAR function. It can be utilized to extract the day of the week in a textual representation.

Syntax:

SELECT TO_CHAR(column_name, Format Specifier) 
FROM table_name;

Here replace table_name with the name of the table and column_name with name of the column that has date data type.

Some Format Specifiers can be the following :

1. ‘Day’: Full name of the day of the week (e.g. “Tuesday”).

2. ‘day’: Full name of the day of the week in lowercase(e.g. “Tuesday”).

3. ‘DAY’: Full name of the day of the week in UPPERCASE(e.g. “TUESDAY”).

4. ‘Dy’: Three-letter abbreviation of the day of the week (e.g. “Tue”).

5. ‘dy‘: Three-letter abbreviation of the day of the week in lowercase (e.g. “true”).

6. ‘DY’: Three-letter abbreviation of the day of the week in UPPERCASE(e.g. “TUE”).

Example: Displaying Day of Week as Text from Date Field in PostgreSQL

SELECT sample_dates,TO_CHAR(sample_dates, 'Day') AS day_of_week
FROM example_table;

Output:

Output of TO_CHAR Function

Explanation: The provided SQL query creates a new column called day_of_week and pulls the sample_dates column from the example_table. The date data in sample_dates are formatted into their corresponding complete day names using the TO_CHAR function. The output shows a list of dates combined with the matching day of the week, presenting a legible picture of the weekdays that correspond to each date in the given data.

Using the Date_Part() Function

In PostgreSQL, A useful tool for removing particular elements from a date or timestamp is the DATE_PART function. Particularly helpful are the ‘dow‘ and ‘isodow‘ parameters for getting the day of the week.

1) Day Of Week (Sunday to Saturday, 0 to 6):

Syntax:

SELECT DATE_PART('dow', column_name) AS day_of_week
FROM your_table;

Replace your_table with the name of the actual table and column_name with the name of the actual column that contains the date field. The outcome will be an integer with Sunday being 0 and Saturday being 6, signifying the day of the week.

Example: Extracting Day of Week using DATE_PART in PostgreSQL

SELECT sample_dates,DATE_PART('dow', sample_dates) AS day_of_week
FROM example_table;

Output:

Output of DATE_PART Function with dow

Explanation: The given SQL statement creates a new column called day_of_week and pulls the sample_dates column from the example_table. For each date in the given table, the DATE_PART function is used to extract the day of the week as an integer (0 for Sunday, 1 for Monday, and so on). The outcome shows the original dates next to the day of the week represented by a number.

2) ISO Day Of Week (Monday to Sunday, 1 to 7):

Syntax:

SELECT DATE_PART('isodow', column_name) AS day_of_week
FROM your_table;

Replace your_table with the name of the actual table and column_name with the name of the actual column that contains the date field. The outcome will be an integer with Monday being 1 and Sunday being 7, signifying the day of the week.

Example: Retrieving the ISO Day of the Week Using DATE_PART Function in PostgreSQL

SELECT sample_dates,DATE_PART('isodow', sample_dates) AS day_of_week
FROM example_table;

Output:

Output of DATE_PART Function with isodow

Explanation: This SQL statement creates a new column called day_of_week by using the DATE_PART function with the ‘isodow’ argument. It also picks the sample_dates column from the example_table. It provides a numerical depiction of weekdays by extracting the ISO day of the week (which ranges from 1 for Monday to 7 for Sunday) for every date in the table.

Conclusion

In Conclusion, for procedures requiring numerical comparisons, the EXTRACT function is useful in extracting the numerical representation of the day of the week. In contrast, the TO_CHAR function offers options for retrieving the whole day name, three-letter abbreviations allowing for output customisation. The DATE_PART function in PostgreSQL facilitates efficient extraction of the day of the week, offering both numeric and ISO representations for diverse applications.