Convert a "timestamp with time zone" column field to the current timezone of the server

Question

In Oracle how do I convert a "timestamp with time zone" to the current time zone of the server? I need to dynamically figure out what the current time zone of the server is.

create table test_table1
(rec1 timestamp with time zone)

insert into test_table1(rec1) values (
to_timestamp_tz('1/1/1900 09:00:00 AM US/EASTERN','dd-mm-yyyy hh:mi:ss AM TZR')
)

SELECT NEW_TIME(rec1, TO_CHAR(rec1, 'TZR'), TO_CHAR(SYSTIMESTAMP, 'TZR'))
FROM test_table1

The example above creates a table with the time zone column. Then I insert a row into the table with the eastern timezone. The select statement doesn't work. What I am trying to achieve is to convert the rec_1 column to the timezone of the server and return that.

Answer 1

You can get the current system timezone [in some format or other] through :

select to_char(systimestamp,'TZD | TZH TZM [ TZR ]'), DBTIMEZONE
from dual;

The following may help with conversion. Not sure whether your session timezone is the same as your database timezone.

SELECT rec1, SYS_EXTRACT_UTC(rec1), cast(rec1 as timestamp with local time zone)
FROM test_table1

---

when I use systeimestamp I get null

TZD can return null (eg it knows what the offset it, but hasn't got an 'alias' for it). TZR shouldn't. Maybe something like...

SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00', to_char(systimestamp,'TZR')) 
   FROM DUAL;