Sreeni Survi <sreenisurvi@gmail.com> writes:
> Below code has caused my data to be wiped off a table as my where clause
> depends on the below returned value.
> *select to_char(current_date - interval '5 weeks','IYYYWW') ;*
> *201953*
(For the archives, current_date - interval '5 weeks' is currently
'2018-12-31 00:00:00')
There's nothing wrong with to_char; it did what you told it to.
The problem here is that you're using ISO year numbering along with
non-ISO week numbering. You should have written 'IYYYIW', which
would give consistent results:
regression=# select to_char(current_date - interval '4 weeks','IYYYIW') ;
to_char
---------
201902
(1 row)
regression=# select to_char(current_date - interval '5 weeks','IYYYIW') ;
to_char
---------
201901
(1 row)
regression=# select to_char(current_date - interval '6 weeks','IYYYIW') ;
to_char
---------
201852
(1 row)
regards, tom lane