Yesterday, I was asked if a function exists in PostgreSQL that will accept the year and the week number and will return a day in that week. Unfortunately, I was not able to find one, but perhaps one natively exists (HINT: let me know if one exists by commenting ;)). Therefore, I wrote the following function:

CREATE OR REPLACE FUNCTION week_to_date(
  IN in_year INTEGER,
  IN in_week INTEGER,
  IN in_dow INTEGER DEFAULT NULL
)
RETURNS DATE AS $$
/*******************************************************************************
Function Name: week_to_date
In-coming Params:
  - in_year INTEGER
  - in_week INTEGER
  - in_dow INTEGER
Description:
  Takes the day of the week (0 to 6 with 0 being Sunday), week of the year, and
  year.  Returns the corresponding date.

Created On: 2011-12-21
Revised On: 2013-02-01 (by ElDiablo)
Author: Chris West
 ******************************************************************************/
BEGIN
  RETURN to_timestamp('1 ' || in_year, 'IW IYYY')::DATE + (COALESCE(in_dow, 1) + 6) % 7 + 7 * in_week - 7;
END;
$$ LANGUAGE plpgsql;

The following are examples of how to use this function:

-- Get the first day (Monday[1]) of week 21 of year 2011: 2011-01-03
SELECT week_to_date(2011, 21);

-- Get the first day (Monday[1]) of week 21 of year 2011: 2011-01-03
SELECT week_to_date(2011, 21, 1);

-- Get the last day (Sunday[0]) of week 21 of year 2011: 2011-01-09
SELECT week_to_date(2011, 21, 0);

This function may seem weird since passing 0 returns the last day while passing in 1 returns the first day of the week. The reason I set it up this way is because I wanted it to directly correspond with the EXTRACT function. The following shows how the values of the EXTRACT function and this function directly correspond:

SELECT in_dow,
  EXTRACT(dow FROM week_to_date(in_year, in_week, in_dow)) AS extracted_dow,
  in_week,
  EXTRACT(week FROM week_to_date(in_year, in_week, in_dow)) AS extracted_week,
  in_year,
  week_to_date(in_year, in_week, in_dow) AS "date"
FROM (
  SELECT NULL AS in_dow, 10 AS in_week, 2011 AS in_year
  UNION SELECT 1 AS in_dow, 10 AS in_week, 2011 AS in_year
  UNION SELECT 0 AS in_dow, 10 AS in_week, 2011 AS in_year
) AS t
ORDER BY COALESCE(in_dow, -1);

Extensive Testing

A reader of my blog named “ElDiablo” alerted me to the fact that my previous version of this function didn’t work for the year 2014. At first I rewrote the code and made it work for all years, but then I decided to just use his code because it ended up being one less line and more straight-forward. The following tests the code out for all dates in the years 2000 to 2100:

SELECT *, EXTRACT(dow FROM a_date) AS dow, EXTRACT(week FROM a_date) AS week, EXTRACT(isoyear FROM a_date)
FROM (
    SELECT to_timestamp('1 ' || in_year, 'IW IYYY')::DATE + (COALESCE(in_dow, 1) + 6) % 7 + 7 * in_week - 7 AS a_date, in_year, in_dow, in_week
    FROM (
        SELECT (in_year || '-06-01')::DATE AS a_date, in_year, in_dow, in_week
        FROM (SELECT generate_series(2000, 2100) AS in_year, generate_series(0, 6) AS in_dow, generate_series(1,53) AS in_week) AS t
    ) AS t
) AS t
WHERE (in_dow != EXTRACT(dow FROM a_date) OR in_week != EXTRACT(week FROM a_date))
AND in_year = EXTRACT(isoyear FROM a_date)

Since the above query returns no results, I know that the updated function should always return the correct date (at least between the years 2000 to 2100 ;)).

Categories: BlogSQL

6 Comments

ElDiablo · February 1, 2013 at 5:31 AM

Warning.
It works perfectly for 2011, 2012 and 2013, but is broken for 2014.

Test case :

SELECT in_dow,
  EXTRACT(dow FROM week_to_date(in_year, in_week, in_dow)) AS extracted_dow,
  in_week,
  EXTRACT(week FROM week_to_date(in_year, in_week, in_dow)) AS extracted_week,
  in_year,
  week_to_date(in_year, in_week, in_dow) AS "date"
FROM (
  SELECT NULL AS in_dow, 1 AS in_week, 2014 AS in_year
  UNION SELECT 1 AS in_dow, 1 AS in_week, 2014 AS in_year
  UNION SELECT 0 AS in_dow, 1 AS in_week, 2014 AS in_year
) AS t
ORDER BY COALESCE(in_dow, -1);

    ElDiablo · February 1, 2013 at 5:47 AM

    Excellent idea, but unfortunately, it doesn’t work for year 2014.
    Here’s a corrected version, which works perfectly :

    CREATE OR REPLACE FUNCTION week_to_date(in_year integer, in_week integer, in_dow integer) RETURNS date
        LANGUAGE plpgsql IMMUTABLE
        AS $$
    /*******************************************************************************
    Function Name: week_to_date
    In-coming Params:
      - in_year INTEGER
      - in_week INTEGER
      - in_dow INTEGER
    Description:
      Takes the day of the week (0 to 6 with 0 being Sunday), week of the year, and
      year.  Returns the corresponding date.
    
    Created On: 2011-12-21
    Revised On: 2013-01-02
    Author: Chris West
     ******************************************************************************/
    DECLARE
      BEGIN
        RETURN to_timestamp('1 ' || in_year,'IW IYYY')::date + (COALESCE(in_dow, 1) + 6 ) % 7 + 7 * (in_week - 1);
      END;
    $$;
    

      Chris West · February 1, 2013 at 1:07 PM

      Thanks for the update. At first I wrote the following revised version but then ended up using yours because it is better šŸ˜€ :

      CREATE OR REPLACE FUNCTION week_to_date(
        IN in_year INTEGER,
        IN in_week INTEGER,
        IN in_dow INTEGER DEFAULT NULL
      )
      RETURNS DATE AS $$
      /*******************************************************************************
      Function Name: week_to_date
      In-coming Params:
        - in_year INTEGER
        - in_week INTEGER
        - in_dow INTEGER
      Description:
        Takes the day of the week (0 to 6 with 0 being Sunday), week of the year, and
        year.  Returns the corresponding date.
      
      Created On: 2011-12-21
      Revised On: 2013-02-01
      Author: Chris West
       ******************************************************************************/
      DECLARE
        a_date DATE;
      BEGIN
        a_date := (in_year || '-06-01')::DATE;
        RETURN (in_week - EXTRACT(week FROM in_dow - EXTRACT(dow FROM a_date)::INT
               + a_date)::INT) * 7 + a_date + in_dow - EXTRACT(dow FROM a_date)::INT;
      END;
      $$ LANGUAGE plpgsql;
      

Tim A. · February 12, 2013 at 2:14 PM

Hey Chris, I was googling the internet for a way to do this and lo and behold I come across your site. This is Tim aka 99% from the old U btw. You have found a way to help me even when you are not around. Thank you!

    Chris West · February 13, 2013 at 8:38 AM

    I am glad that I can still help you guys out. To tell you the truth, I believe this post was from a discussion that you, Paul and I had.

Joe Friday · March 6, 2017 at 11:48 PM

Given ISO year 2017 and ISO week 09, concatenated as ‘2017-09’, then:

select to_date('2017-09', 'iyyy-iw');

produces output:

‘2017-02-27’

This is the native inverse you were looking for.

Leave a Reply to ElDiablo Cancel reply

Your email address will not be published. Required fields are marked *