PostgreSQL – Week Number To Date

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 ;)).