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!
USING AT TIME ZONE- NOT WORKING
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
set SS_CREATED = CASE WHEN SS_CREATED IS NULL THEN NULL ELSE SS_CREATED + interval '3' hour END
but in SELECT, both works.
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
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.