to_char function returning wrong data

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

to_char function returning wrong data

Sreeni Survi
Hi,

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

If the number of weeks is 4 or 6 or 3 it returns correct value.

select to_char(current_date - interval '4 weeks','IYYYWW') ;   
201901

select to_char(current_date - interval '6 weeks','IYYYWW') ;   
201852

This issue can have a serious effect.

Thanks,
Sreeni

Reply | Threaded
Open this post in threaded view
|

Re: to_char function returning wrong data

Andrew Gierth
>>>>> "Sreeni" == Sreeni Survi <[hidden email]> writes:

 Sreeni> Hi,
 Sreeni> Below code has caused my data to be wiped off a table as my
 Sreeni> where clause depends on the below returned value.

 Sreeni> *select to_char(current_date - interval '5 weeks','IYYYWW') ;*
 Sreeni> *201953*

That's because IYYY is intended for use with IW, not with WW. The output
is correct, it's the query that's wrong.

There are several different definitions of "week number", and IW is the
ISO week, with IYYY being the corresponding year. So 5 weeks ago, which
was Dec 31st 2018, was ISO week 1 of 2019, but in some other week
numbering systems, it was week 53 of 2018.

--
Andrew (irc:RhodiumToad)

Reply | Threaded
Open this post in threaded view
|

Re: to_char function returning wrong data

Tom Lane-2
In reply to this post by Sreeni Survi
Sreeni Survi <[hidden email]> 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