In PostgreSQL, built-in functions like EXTRACT() and DATE_PART() are used to extract a DOW(an acronym for Day of Week) from a date or timestamp. Using these two functions, you can extract any part from a date or timestamp, such as a day, month, year, minute, etc. However, in this post, we will only focus on extracting the day of the week from the given date or timestamp.
This tutorial will show you how to use PostgreSQL's built-in date functions to extract the day of the week (DOW) from a date field. So, let’s start!
How to Extract DOW in Postgres Using the EXTRACT() Function?
Use the following syntax to extract the DOW from a date field:
EXTRACT(DOW FROM source);
In place of the “source” parameter, specify the date field from which you want to extract the day of the week.
Note: Extracting the day of the week using the EXTRACT() function will retrieve a value between 0-6. Here, the number 0 means Sunday, 1 means Monday, and so on.
Example 1: How to Extract a DOW From the Current Date Using EXTRACT() Function?
Let’s pass “DOW” as the first argument and the “CURRENT_DATE” function as the second argument to the EXTRACT() function:
SELECT EXTRACT('DOW' FROM CURRENT_DATE);
The EXTRACT() function retrieves “3”, indicating that the current day of the week is “Wednesday”.
Example 2: How to Extract a DOW From the Date Field Using EXTRACT() Function?
In this example, we will use the EXTRACT() function to pull out the DOW from a specific date field:
SELECT EXTRACT('DOW' FROM DATE '2022-12-12');
The output snippet shows that the day of the week in the specified date field is “Monday”.
How to Extract DOW in Postgres Using the DATE_PART() Function?
Postgres offers another convenient date function named DATE_PART() that is used to extract the day of the week from the given date field:
DATE_PART(DOW, source);
In place of the “source” parameter, specify the date field from which you want to extract the day of the week.
Example 1: How to Extract a DOW From the Current Date Using DATE_PART() Function?
In this example, we will pass “DOW” as the first argument and the “NOW()” function as the second argument to the DATE_PART() function:
SELECT DATE_PART('DOW', NOW());
The output shows “3”, which means the current day of the week is “Wednesday”.
Example 2: How to Extract a DOW From the Date Field Using DATE_PART() Function?
Executing the below line of code will extract the DOW from a specific date field:
SELECT DATE_PART('DOW', DATE '2022-01-15');
The output snippet shows that the DATE_PART() function retrieves “6”, indicating that the day of the week in the given date field is “Saturday”.
Example 3: How to Extract a DOW From the Table’s Data?
We have already created a table named “article_information”, whose date is shown in the following snippet:
SELECT * FROM article_information;
In this example, we will use the EXTRACT() and DATE_PART() functions side-by-side to extract the day of the week from the “p_date” column:
SELECT p_date, EXTRACT(DOW FROM p_date), DATE_PART('DOW', p_date)
FROM article_information;
The output authenticates the working of the EXTRACT() and DATE_PART() functions.
Conclusion
In PostgreSQL, the EXTRACT() and DATE_PART() functions are used to extract a DOW(an acronym for Day of Week) from a date or timestamp. Both these functions accept two arguments: a “unit” argument like a month, day, year, etc., and a “date field” from which you want to extract the day of the week. Through examples, this write-up demonstrated how to extract DOW via Postgres’ EXTRACT() and DATE_PART() functions.
