In PostgreSQL, you can use the to_char() function to get the short day name from a date.

By “short day name” I mean the abbreviated day name, for example Fri, Mon, etc.

The to_char() function accepts two parameters; the date and the format string with which to format that date.

Example

Here’s an example to demonstrate.

SELECT to_char(current_timestamp, 'dy');

Result:

sat

As you might expect, I ran this query on a Saturday.

Specifying the Case

You can specify whether or not the short day name should be in uppercase, lowercase, or title case.

To do this, simply use the desired case in the format string.

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  
-----+-----+-----
 sat | Sat | SAT

Longer Format String

You can include the short day name as part of a longer date output.

For example, the following.

SELECT to_char(current_timestamp, 'Dy, DD Mon YYYY');

Result:

Sat, 06 Jun 2020

Get the Long Day Name

You can use Day, DAY, or day to get the long day name.