TIMESTAMP Datatypes

Once the database time zone is set (with ALTER DATABASE SET TIME_ZONE command) and you have defined single table with a TSWLTZ column in the database, you're locked to that time zone - Oracle won't let you change the database time zone because this will implicitly alter all TSWLTZ columns data - remember that they only hold the offset from the database time zone? If you will change the time zone, TSWLTZ will yeld different timestamps because it will add or substract the offset from new database time zone, not the one that was in effect when you created the row, and this will result in wrong data being returned. So if you attempt to alter the database time zone with at least one column of TSWLTZ type defined in the database, Oracle will throw this rather misguiding error.

Worse yet, TSWLTZ data type is not suited well for distributed systems where several databases around the world serve the application: each database will probably have its own time zone and TSWLTZ data in these databases will not be interchangeable between them. TSWTZ is free from this effect, because original time zone is always known. But then again, you certainly would want to present that data to users residing in different time zones and you would want to show what time it was in THEIR time zone, won't you? Oracle knew about that, and they extended the Datetime Expression syntax with AT clause to allow you to convert TSWTZ to different time zones easily:

For detail read please refer this link

--------------------------------------------------------------------

 select * from V$TIMEZONE_NAMES;

SELECT TO_CHAR(TO_TIMESTAMP_TZ('2003-01-01 10:00 US/Eastern','YYYY-MM-DD HH24:MI TZR'),'YYYY-MM-DD HH24:MI TZH:TZM') "US/Eastern",

TO_CHAR(TO_TIMESTAMP_TZ('2003-01-01 10:00 US/Eastern','YYYY-MM-DD HH24:MI TZR') AT TIME ZONE 'US/Pacific','YYYY-MM-DD HH24:MI TZH:TZM') "US/Pacific" FROM DUAL;

SELECT TO_CHAR(TO_TIMESTAMP_TZ('2003-01-01 10:00 Europe/Moscow','YYYY-MM-DD HH24:MI TZR'),'YYYY-MM-DD HH24:MI TZH:TZM')"Daylight" FROM SYS.DUAL;

select SYS_EXTRACT_UTC(TIMESTAMP '2000-03-28 11:30:00.00 -08:00') kl,SYS_EXTRACT_UTC(SYSTIMESTAMP) sy , CURRENT_TIMESTAMP,  DBTIMEZONE , localtimestamp ,extract(hour from systimestamp),systimestamp,to_timestamp_tz('10/07/2004', 'DD/MM/YYYY') now,systimestamp(2) ,SESSIONTIMEZONE from dual;

Some examples

.