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

Question

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

Answer 1

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=>