Week number giving 1 week less number
cp00634326
Vertica Customer
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)
Best Answers
-
Options
Bryan_H
Vertica Employee Administrator
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);5 -
Optionscp00634326
Vertica Customer
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 .
0