How to Extract Day of Week From Date Field in PostgreSQL

1. Extract Day of the Week From the Date Field Using EXTRACT in PostgreSQL
2. Use the To_CHAR() Function to Get the Day From a String in PostgreSQL
3. Use CASE to Define Days Division for TIMESTAMPS in PostgreSQL

We will learn in this tutorial how to find the day of the week from a DATEFIELD given in a PostgreSQL table. We’ll also learn the different operations that you can use alongside this for manipulating and using data inside a DATEFIELD.

We use the EXTRACT function to perform such manipulations, which retrieves the sub-fields from such a STRING having a DATE. Let’s see how we can use it.

Extract Day of the Week From the Date Field Using EXTRACT in PostgreSQL

To extract the Day of the Week, we can use either DOW or ISODOW. DOW starts the days from a count of 0 using SUNDAY as the starting day, but ISODOW, however, uses MONDAY and lists SUNDAY as 7.

Query:

select extract (isodow from timestamp '2022-03-25');

This uses the TIMESTAMP to get the day of the week and returns.

Output:

So on 25th of March, 2022, the day was Friday if you start numbering from SUNDAY as 0, you will see that FRIDAY is 5.

Now if let’s suppose we pick SUNDAY as our day. In that case, DOW will return 0, and ISODOW will return 7 as each has a different pattern.

You can test the values out yourself as well:

select extract (isodow from timestamp '2022-03-28') as iso_dow, extract (dow from timestamp '2022-03-28') as d_ow;

Suppose you want MONDAY to have the value 0. In such a problem, you can use the following statement:

select extract (isodow from timestamp '2022-03-21') - 1;

In the case of TUESDAY, you would subtract 2 and so on.

Use the To_CHAR() Function to Get the Day From a String in PostgreSQL

Another function to return the day name is To_CHAR().

Query:

select to_char(timestamp '2022-03-25', 'DAY');

Output:

Query:

select to_char(timestamp '2022-03-25', 'DY');

Using the query above will return FRI instead of FRIDAY. You can read up on the possible keywords used in the second parameter.

To get the DAY, only use the following code.

Query:

select to_char(timestamp '2022-03-25', 'D');

An extract from the PostgreSQL documentation reads:

to_char(..., 'ID')'s day of the week numbering matches the extract(isodow from ...) function, but to_char(..., 'D')'s does not match extract(dow from ...)'s day numbering.

Meaning that calling D as the second parameter does not match the DOW syntax even though the patterns match.

Use CASE to Define Days Division for TIMESTAMPS in PostgreSQL

The following code utilizes the extended version of the EXTRACT method.

Query:

with a as (select extract(dow from date '2022-02-21') a ),
b as(select CASE
         WHEN a.a = 0 THEN 'Sunday'
         WHEN a.a = 1 THEN 'Monday'
         WHEN a.a = 2 THEN 'Tuesday'
         WHEN a.a = 3 THEN 'Wednesday'
         WHEN a.a = 4 THEN 'Thursday'
         WHEN a.a = 5 THEN 'Friday'
         WHEN a.a = 6 THEN 'Saturday'
   END from a )
 select * from a, b;

It gets the value a and then for b checks if a matches the CASE statements given. The value is copied into b if it does to any.

This will return the output as the one given below.

Output:

We hope that you now understand the various approaches that can be used to extract the day of the week from any given STRING.