CODEWITHSUNDEEP
sqlpostgresqldate
Eugenio
Eugenio

Reputation: 3435

Extract day of week from date field in PostgreSQL assuming weeks start on Monday

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)?

Upvotes: 106

Views: 213584

Answers (4)

Max Carroll
Max Carroll

Reputation: 4839

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
select To_Char("Date", 'D'), * from "MyTable";   -- 3 From Docs:    day of the week, Sunday (1) to Saturday (7)

Upvotes: 51

Bharti Mohane
Bharti Mohane

Reputation: 706

with a as (select extract(isodow from date '2020-02-28') - 1 a ),
b as(select CASE 
         WHEN a.a=0 THEN 'Sunday'
         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'
     ELSE 'other'
   END from a ) 
 select * from b;

Upvotes: 2

Mr. Bhosale
Mr. Bhosale

Reputation: 3096

Use date_part Function dow()

Here 0=Sunday, 1=Monday, 2=Tuesday, ... 6=Saturday 

   select extract(dow from date '2016-12-18'); /* sunday */

Output : 0

    select extract(isodow from date '2016-12-12'); /* Monday  */

Ouput : 1

Upvotes: 57

user330315
user330315

Reputation:

From the manual

isodow

    The day of the week as Monday (1) to Sunday (7)

So, you just need to subtract 1 from that result:

psql (9.6.1)
Type "help" for help.

postgres=> select extract(isodow from date '2016-12-12') - 1;
  ?column?
-----------
         0
(1 row)
postgres=>

Upvotes: 113

Related Questions