Problem extracting year and week number from string in PSQL

Question

Let's say that I have a range of SQL tables called name_YYYY_WW , where YYYY = year and WW = week number. If I call a function that leads a user-defined date to the desired table.

If the entered date is "20110101" :

SELECT EXTRACT (WEEK FROM DATE '20110101') returns 52 and

SELECT EXTRACT (YEAR FROM DATE '20110101') returns 2011.

So far there is nothing wrong with these results, I want "20110101" point to the table name_2010_52 or name_2011_01 , and not name_2011_52 , as it happens now when I combine the results to form a query for the table.

Any elegant solutions to this problem?

Answer 1

The to_char () function allows you to format the date or timestamp to display the correct iso and iso year weeks.

 SELECT to_char('2011-01-01'::date, 'IYYY_IW') as iso_year_week; 

will produce:

  iso_year_week --------------- 2010_52 (1 row) 

Answer 2

You can use CASE:

 WITH sub(field) AS ( SELECT CAST('20110101' AS date) -- just to test ) SELECT CASE WHEN EXTRACT (WEEK FROM field ) > 1 AND EXTRACT (MONTH FROM field) = 1 AND EXTRACT (DAY FROM field) < 3 THEN 1 ELSE EXTRACT (WEEK FROM field) END FROM sub;