Search code examples
postgresqldatedayofweek

How can I adjust the offset in my query using extract(dow from date) so that Monday is 0 - Postgres

Im using Postgres, and I would like to get the days of week number as follow:

(Monday: 0, Tuesday: 1 ,... Sunday:6)

But using following SQL in Postgres function

extract(dow from current_date)

Postgres gives me following back:

(Sunday: 0, Monday: 1 ,... Saturday:6)

How can I adjust the offset in my query using extract(dow from date) so that Monday is 0 and not Sunday?

And I wonder why does the following Postgres function:

date_trunc('week',(current_date)

Uses a logic different from extract dow.

(Monday: 0 -> Week Start, Tuesday: 1 ,... Sunday:6 -> Week End)
Solution

  • Use isodow instead of dow

    Quote from the manual

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

    Then simply subtract 1 from the result:

    extract(isodow from current_date) - 1