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