Back to cookbooks list Articles Cookbook

How to Get Day Names in PostgreSQL

  • to_char()
  • date
  • LC_TIME

Problem:

You want to extract the name of the day of the week from a date in PostgreSQL.

Solution 1:

To extract the day name from the date, you can use the to_char() function. The first argument is the date and the second is the desired output format. To extract the full day name, the format should be 'Day':

SELECT to_char(date '2022-01-01', 'Day');

The result is Saturday.

Discussion:

This is not the only format you can use to extract the day name. Here are some alternatives:

  • 'DAY' would return an uppercase name (SATURDAY)
  • 'day' would return a lowercase name (saturday)
  • 'DY' would return SAT
  • 'Dy' would return Sat
  • 'dy' would return sat
  • Discover the best interactive PostgreSQL courses

You can also extract the day name in other languages. Two adjustments are required to do this:

  1. Setting new locale for times and dates using SET lc_time;
  2. Using the 'TMDay' format to enable translation based on lc_time.

Let’s look at an example.

Solution 2:

Let’s extract the day name in German:

set lc_time = 'de_DE';
select to_char(date '2022-01-01', 'TMDay') ;

The result is Samstag.

Recommended courses:

Recommended articles:

See also: