In PostgreSQL, the EXTRACT() function is used to get a day, month, year, etc., from a DATE, TIME, or TIMESTAMP. We can use this function to get/extract the day from the specified date or time stamp. However, it retrieves the specific part/field from the specified date as a number. If we have to get/extract the day names from a specific date, then we must use the TO_CHAR() function.

This post demonstrates a practical guide on how to get or extract the day of the week from a specific date.

How to Extract Day Names in Postgres?

To extract the day name from a specific date, you need to pass the date/timestamp as a first argument and a valid day format as the second argument to the “TO_CHAR()” function:

SELECT TO_CHAR(DATE  'specific_date', 'day-format');

Postgres supports the below-listed day formats:

• “DAY”: It retrieves the full day name in uppercase, for instance, “MONDAY”.
• “day”: It retrieves the full day name in lowercase, for instance, “monday”.
• “DY”: It retrieves the first three letters of the day's name in uppercase, for instance, “MON”.
• “dy”: It retrieves the first three letters of the day's name in lowercase, for instance, “mon”.

Example 1: How to Get the Day of Week in Uppercase?

Suppose we want to extract the day of the week from the current date; for this, we will execute the TO_CHAR() function as follows:

SELECT TO_CHAR(CURRENT_DATE, 'DAY');

The output demonstrates that the current day of the week is Monday.

Example 2: How to Get the Day of Week in Lowercase?

Suppose we want to get the day of the week(lowercase) from a specific date; for this, we will execute the TO_CHAR() function as follows:

SELECT TO_CHAR(DATE '2022-12-14', 'DAY');

The TO_CHAR() function retrieves the day of the week in lowercase.

Example 3: How to Get the Abbreviated Day of Week in Uppercase?

To get the first three letters of the day’s name in uppercase from a specific date, you need to execute the TO_CHAR() function as follows:

SELECT TO_CHAR(DATE '2022-12-14', 'DY');

The TO_CHAR() function retrieves the abbreviated day of the week in uppercase.

Example 4: How to Get the Abbreviated Day of Week in Lowercase?

To extract the abbreviated day of the week in lowercase, pass the “dy” format as the second argument to the TO_CHAR() function:

SELECT TO_CHAR(DATE '2022-12-14', 'dy');

The TO_CHAR() function retrieves the abbreviated day of the week in lowercase.

Example 5: How to Get the Day of Week From Table’s Data?

A sample table named “staff_info” has already been created with the following content:

SELECT * FROM staff_info;

Let’s execute the TO_CHAR() function to get the day names from the “joining_date” column:

SELECT staff_name, TO_CHAR(joining_date, ‘DAY’) AS joining_day
FROM staff_info;

This is how you can get/extract the name of the weekdays from a specific date or timestamp.

Conclusion

In PostgreSQL, the TO_CHAR() function is used to extract the day’s name from a specific date or timestamp. For this purpose, you must pass the date/timestamp as the first argument and a valid day format as the second argument to the “TO_CHAR()” function. Postgres offers various formats to get the day names, such as “DAY”, “DY”, “day”, and “dy”. This post demonstrates various examples of how to get day names in Postgres.