0

I have a TIMESTAMP WITH TIME ZONE column in my table. I want to update all values with systimestamp at their respective timezones.

I have tried many ways, but i could not dynamically get timezone information from the value and update the same in a single query.

I will provide the necessary structure here.

create table TIMEZONE_TEST
( COLUMN_ONE timestamp with time zone
);

insert into TIMEZONE_TEST values (systimestamp at time zone 'US/Pacific');
insert into TIMEZONE_TEST values (systimestamp at time zone 'Asia/Tokyo');
insert into TIMEZONE_TEST values (systimestamp at time zone 'Asia/Kuala_Lumpur');
insert into TIMEZONE_TEST values (systimestamp at time zone 'Asia/Singapore');
commit;

I need to update all values with systimestamp of particular timezones.

something like

update TIMEZONE_TEST
set COLUMN_ONE = systimestamp at time zone '<TIMEZONE_NAME of the value>';

Thanks for the kind help in advance.

Varun Rao
  • 781
  • 1
  • 10
  • 31

2 Answers2

0

I am not sure whether I got your question properly but I assume it should be this one:

UPDATE TIMEZONE_TEST SET 
COLUMN_ONE = FROM_TZ(CAST(SYSTIMESTAMP AS TIMESTAMP), EXTRACT(TIMEZONE_REGION FROM COLUMN_ONE));
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • the query that is provided inserts systimestamp at server timezone and also the timezone information from the previous value. however I need SYSTIMESTAMP AT that previous value' s TIMEZONE to be inserted. i.e. during insert, the HH:MI values are different, However after running your query, all HH:MI are all the same. – Varun Rao Feb 05 '15 at 08:35
  • `UPDATE TIMEZONE_TEST SET COLUMN_ONE = SYSTIMESTAMP at time zone EXTRACT(TIMEZONE_REGION FROM COLUMN_ONE);` works just fine for my scenario. and is the answer. Thanks @Wernfried – Varun Rao Feb 05 '15 at 08:43
0

The required answer was

UPDATE TIMEZONE_TEST
SET COLUMN_ONE = SYSTIMESTAMP at time zone EXTRACT(TIMEZONE_REGION FROM COLUMN_ONE);

I modified the answer given by @Wernfried.

Varun Rao
  • 781
  • 1
  • 10
  • 31