select extract(dow from datefield)
extract a number from 0 to 6, where 0 is Sunday; is there a way to get the day of the week in SQL assuming that weeks start on Monday (so 0 will be Monday)?
If you want the text version of the weekday then you can use the to_char(date, format)
function supplying a date and the format that you want.
According to https://www.postgresql.org/docs/current/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE we have the following format options we can use for date. I have shown some examples for output. According to the documentation the abbreviated day values are 3 characters long in English, other locales may vary.
select To_Char("Date", 'DAY'), * from "MyTable"; -- TUESDAY
select To_Char("Date", 'Day'), * from "MyTable"; -- Tuesday
select To_Char("Date", 'day'), * from "MyTable"; -- tuesday
select To_Char("Date", 'dy'), * from "MyTable"; -- tue
select To_Char("Date", 'Dy'), * from "MyTable"; -- Tue
select To_Char("Date", 'DY'), * from "MyTable"; -- TUE
with a as (select extract(isodow from date '2020-02-28') - 1 a ),
b as(select CASE
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'
WHEN a.a=7 THEN 'Sunday'
ELSE 'other'
END from a )
select * from b;