In PostgreSQL, you can get the day name from a date by using the to_char() function. This function returns a string based on the timestamp and the template pattern you provide as arguments..

Example

Here’s a quick example.

SELECT to_char(timestamp '2020-12-16 10:41:35', 'Day') AS "Day";

Result:

    Day    
-----------
 Wednesday

In this case, I specified a template pattern of 'Day', so this caused the day name to be returned from the timestamp value.

Lowercase, Uppercase, and Capitalisation

You can return the day in lowercase, uppercase, or capitalised. You determine which one is returned by the letter case that you use in the template pattern (second argument).

Here’s an example to demonstrate what I mean.

SELECT 
  to_char(current_timestamp, 'day') AS "day",
  to_char(current_timestamp, 'Day') AS "Day",
  to_char(current_timestamp, 'DAY') AS "DAY";

Result:

    day    |    Day    |    DAY    
-----------+-----------+-----------
 thursday  | Thursday  | THURSDAY 

All results are blank-padded to 9 characters, regardless of the case used.

Short Day Name Format

You can also specify that the result is formatted using the short day name.

SELECT 
  to_char(current_timestamp, 'dy') AS "dy",
  to_char(current_timestamp, 'Dy') AS "Dy",
  to_char(current_timestamp, 'DY') AS "DY";

Result:

 dy  | Dy  | DY  
-----+-----+-----
 thu | Thu | THU