Search code examples
sqlpostgresqldatedatetimedayofweek

Postgres - Get the monday of week X

In Postgres, given week X in year Y, how to get the Monday date of that week/year?

Example:

monday_of(1, 2020) -> '2020-01-30'
monday_of(33, 2020) -> '2020-08-10'
monday_of(53, 2020) -> '2020-12-28'
monday_of(54, 2020) -> '' (year does not have 54 weeks)
Solution

  • You can use date arithmetics. Assuming that yr and wk are the year and week parameters:

    date_trunc('week', make_date(yr, 1, 1)) + (wk- 1) * interval '1 week'

    Note, however, that this does not detect out of range weeks (but you can easily add conditional logic to handle that).

    Demo on DB Fiddle:

    select yr, wk, date_trunc('week', make_date(yr, 1, 1)) + (wk- 1) * interval '1 week' res
from (values (1, 2020), (33, 2020), (53, 2020), (54, 2020)) as t(wk, yr)

      yr | wk | res                   
---: | -: | :---------------------
2020 |  1 | 2019-12-30 00:00:00+00
2020 | 33 | 2020-08-10 00:00:00+01
2020 | 53 | 2020-12-28 00:00:00+00
2020 | 54 | 2021-01-04 00:00:00+00