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

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 zo开发者_如何学JAVAne 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.

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;

Oracle will convert time zones based on Standard and Daylight Saving Time if regions "TZR" are used instead of Hours and minutes "TZH:TZM" offset.

To qualify your region you can use (in 11g not sure of other versions)

SELECT SYSTIMESTAMP AT TIME ZONE 'US/Eastern' FROM DUAL;

The region used must be stored in the DB which you can check by

SELECT * FROM v$timezone_names WHERE tzname LIKE 'US%';

This query can be modified to identify any region.

The Oracle Globalization Support manual is also a good reference.