CODEWITHSUNDEEP
sqlpostgresqldate-arithmetic
Kevin Waterson
Kevin Waterson

Reputation: 847

How to get day name from weekday number in postgresql?

I have a table with the weekday stored as an integer, 0 to 6.

What function do I need to create the day names relative to these day numbers?

Eg: 0 -> Sun, 1->Mon, etc

Like:

SELECT magic(my_day_number) FROM my_table;
Mon

Upvotes: 4

Views: 8658

Answers (5)

vhauf
vhauf

Reputation: 1

Supports locale as well as built in day name formats and is the shortest...

to_char('9/9/2001'::date + day_number, 'Dy')

(9/9/2001 or any given Sunday)

Example:

SELECT to_char('9/9/2001'::date + my_day_number, 'Dy')
FROM generate_series(0,6) AS my_table(my_day_number);

Sun
Mon
Tue
Wed
Thu
Fri
Sat

Upvotes: 0

Vitaly
Vitaly

Reputation: 51

That's what I would use:

select ('{Sun,Mon,Tue,Wed,Thu,Fri,Sat}'::text[])[extract(dow from now()) + 1] as day_of_week;

Or if you prefer a function:

CREATE OR REPLACE FUNCTION dow_name(p_index integer)
RETURNS text LANGUAGE sql STRICT IMMUTABLE PARALLEL SAFE AS
$$
  SELECT (array['Sun','Mon','Tue','Wed','Thu','Fri','Sat'])[p_index + 1];
$$;

Both options with '{...}'::text[] and array[...] work 8% faster (on Postgres 12) comparing to using CASE block.

Upvotes: 5

Miles Elam
Miles Elam

Reputation: 1778

The other answers are good, but honestly I'd recommend doing this at the app layer, not in the database.

  1. This is globally constant information.
  2. The data is extremely small. Just 0 through 6.
  3. The day of the week is adequately modeled in the database with 0 through 6.
  4. The formatting can vary: M, Mo, Mon, Monday.
  5. A day of the week number is arguably easier to localize to other languages.
  6. The app layer scales more easily than a database.
  7. All of the above can even be deferred to client-side rendering/localization.

Formatting to a day name does not improve normalization or query speed, so arguably does not belong in the database. But if you insist on using the database layer for this, an immutable function is probably the way to go.

CREATE OR REPLACE FUNCTION day_name(day_index integer)
RETURNS text LANGUAGE sql STRICT IMMUTABLE PARALLEL SAFE AS $$
  SELECT CASE day_index
    WHEN 0 THEN 'Sun'
    WHEN 1 THEN 'Mon'
    WHEN 2 THEN 'Tue'
    WHEN 3 THEN 'Wed'
    WHEN 4 THEN 'Thu'
    WHEN 5 THEN 'Fri'
    WHEN 6 THEN 'Sat'
    END;
$$;

I called the function day_name to be more descriptive, and hopefully the way it's implemented does not appear to be magic. By making the function IMMUTABLE, once the database calculates it once, it can use a cached result, dramatically improving speed over an ad hoc single-use query.

Upvotes: 1

JGH
JGH

Reputation: 17906

Postgres can return this information for dates.

You can transform these days into dates, then extract the day name. It has the benefit of allowing changing language.

The year 2017 is used because January 1st is a Sunday (day 0) and the format IDDD considers day 1 as the 1st Monday of the year.

WITH myTable(my_day_number) as (values (0),(1),(2),(3),(4),(5),(6))
SELECT to_char(to_date(my_day_number||'-2017','IDDD-IYYY'), 'Day')
FROM mytable;

  to_char
-----------
 Sunday
 Monday
 Tuesday
 Wednesday
 Thursday
 Friday
 Saturday

Upvotes: 2

GMB
GMB

Reputation: 222702

A simple and efficient method uses case instead of date manipulation routines:

select case my_day_number
    when 0 then 'Sun'
    when 1 then 'Mon'
    when 2 then 'Tue'
    ...
    when 6 then 'Sat'
end my_day_name from mytable

Upvotes: 2

Related Questions