-1

I have a column with strings such as 2015-W02, meaning the second week of 2015. I would like to convert it to a date (e.g., corresponding to the Saturday of that week). It does not seem that CONVERT nor PARSE support this style. Any idea how to do that?

2
  • Yes. All are YYYY-Wxx Nov 2, 2020 at 9:42
  • PostgreSQL 10.14 (Ubuntu 10.14-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit Nov 2, 2020 at 9:51

2 Answers 2

1

If that week number identifies the ISO week (= week starts on Monday, first week belongs to the year where the "bigger" part falls into), you can use to_date()

select to_date('2015-W02', 'iyyy-"W"iw')

Note that this returns the Monday of that week, as this is how the ISO week is defined.

0
0

To deal with dates I would suggest a calendar table, you can find some scripts to generate your own calendar tables on google.

In this case I've used a CTE to generate a series of dates for 2015 and below sample data:

create table t (id int, dt text);
insert into t values (1, '2015-W02'), (2, '2015-W08'), (3, '2015-W21');
with days as
(
    select dt, 
           extract(dow from dt) dow, 
           extract(week from dt) wk,
           extract(year from dt) yr
    from  generate_series('2015-01-01'::timestamp, 
                          '2015-12-31'::timestamp, 
                          '1 day'::interval) dt
)
select
  *
from 
  t
join
  days 
  on substring(t.dt, 1, 4)::int = days.yr
  and substring(t.dt, 7, 2)::int = days.wk
where
  days.dow = 6; --saturdays only
id | dt       | dt                  | dow | wk | yr  
-: | :------- | :------------------ | :-- | :- | :---
 1 | 2015-W02 | 2015-01-10 00:00:00 | 6   | 2  | 2015
 2 | 2015-W08 | 2015-02-21 00:00:00 | 6   | 8  | 2015
 3 | 2015-W21 | 2015-05-23 00:00:00 | 6   | 21 | 2015

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.