'at time zone' not working in UPDATE/INSERTstatement

Database
N/A

'at time zone' not working in UPDATE/INSERTstatement

Hi-

Iam trying to convert timestamp column from PST to EST. In order to achieve that, I used at time zone '+3:00' to add 3 hours. But it is not helping out. The same at time zone works perfectly in a select statement but its not working with update/insert statement.

Any help would be much appreciated!

SQLs-

USING AT TIME ZONE- NOT WORKING

update

EDW_qa_WORK_DB.TEST

set SS_CREATED = CASE WHEN SS_CREATED IS NULL THEN NULL ELSE SS_CREATED at time zone '+3:00' END

USING INTERVAL - WORKING LIKE A CHARM

update

EDW_qa_WORK_DB.TEST

set SS_CREATED = CASE WHEN SS_CREATED IS NULL THEN NULL ELSE SS_CREATED + interval '3' hour END

but in SELECT, both works.

select

SS_CREATED,

CASE WHEN SS_CREATED IS NULL THEN NULL ELSE SS_CREATED at time zone '+3:00' END,

CASE WHEN SS_CREATED IS NULL THEN NULL ELSE SS_CREATED + interval '3' hour end

from EDW_qa_WORK_DB.TEST

Thanks,

Yasha

1 REPLY
Teradata Employee

Re: 'at time zone' not working in UPDATE/INSERTstatement

AT time zone offset '+3:00' only appears to work in the SELECT because your session time zone offset is +00:00 and you are ignoring the time zone of the result.

Internally, timestamps are procesed as UTC values. Timestamps also have an associated (external) time zone displacement, either explicitly stored or implicitly assumed based on session / user / system defaults.

Suppose MY_TS is a TIMESTAMP WITH TIME ZONE having a value of 2013-12-11 12:34:56 +00:00.

Then MY_TS at time zone '+3:00' would be adjusted to 2013-12-11 15:34:56 +03:00 - same as the original instant in time, just shifted to display relative to a different time zone. If you don't explicitly store the time zone, there is no actual change as far as the database is concerned.

But MY_TS + interval '3' hour would be 2013-12-11 15:34:56 +00:00 - actually three hours later than the original time.

Note that if your original value was truly PST 2013-12-11 12:34:56 -08:00 then MY_TS at time zone '+3:00' = 2013-12-11 23:34:56 +03:00.

Warning: Changing time zone related settings, especially after initial system setup, is a complex undertaking requiring careful planning - and may require application changes as well as database changes.