postgresql

Get the first date of an ISO 8601 year and week

PostgreSQL provides the extract function to get a date's year and week number according to the ISO 8601 standard, which has the first week of every year containing January 4th.

These can be extracted as follows (using today as the date):

select extract(isoyear from current_date);
select extract(week from current_date);

But there seems to be no inverse of this function. What I'm looking for is a way to get the first date in an ISO 8601 year and week. Any ideas?

Solution

  • to_date() supports the ISO year and ISO week.

    So you first need to get the "start of the year" (using date_trunc()) and then convert that to the proper "week" (using to_char()) and convert that back into a date (using to_date()):

    to_date(to_char(date_trunc('year', current_date), 'iyyy-iw'), 'iyyy-iw')

    This statement: 

    select date_trunc('year', current_date), 
       to_char(date_trunc('year', current_date), 'iyyy-iw'),
       to_date(to_char(date_trunc('year', current_date), 'iyyy-iw'), 'iyyy-iw');

    When run on 2015-12-07 this returns: 

    date_trunc          | to_char | to_date   
--------------------+---------+-----------
2015-01-01 00:00:00 | 2015-01 | 2014-12-29