Skip to main content
Question

Week number giving 1 week less number

  • May 19, 2020
  • 2 replies
  • 6 views

cp00634326
Forum|alt.badge.img+1

Hi team,
I am getting 1 week less when running the query to check.Below is my test.Current week no is 21 but it is showing 20.

vertdba=> SELECT CURRENT_TIMESTAMP
vertdba-> ;

?column?

2020-05-19 03:43:39.792345-05
(1 row)
vertdba=> select TO_CHAR( CURRENT_TIMESTAMP AT TIMEZONE 'UTC'::DATE - INTERVAL '0 WEEKS','YYYYWW')::INT;

TO_CHAR

202020
(1 row)
vertdba=> select week(current_timestamp);

week

21
(1 row)

But what wrong in the below query which is giving week 20.
vertdba=> SELECT TO_CHAR( CURRENT_TIMESTAMP AT TIMEZONE 'UTC'::DATE - (0::varchar(6) || ' WEEKS')::interval,'YYYYWW')::INT;

TO_CHAR

202020
(1 row)

2 replies

Bryan_H
Forum|alt.badge.img+2
  • Participating Frequently
  • May 19, 2020

I'm not sure quite why this is, but Postgres 9.2 does the same thing, and we emulate PGSQL fairly faithfully. The format string 'YYYYIW' returns ISO standard and should always return what you want.
Would it be possible to use alternate methods to write the character string, like select year(current_timestamp) || week_iso(current_timestamp);


cp00634326
Forum|alt.badge.img+1
  • Author
  • Participating Frequently
  • May 19, 2020

Thanks Brayan ,it worked..The other method you ahve suggested i have tried but it is not subtracting any value.My requirement is to subtract no of weeks from current week. However string 'YYYYIW' is working properly for Weekly .