How to convert the time zone correctly in Oracle 9i

Before Oracle9i, although there was a NEW_TIME function that could change the timestamp of DATE, there was no data type specifically used to store time zone information. In Oracle9i, we can use the DBTIMEZONE pseudo field to query the time zone of the database, and use the SESSIONTIMEZONE pseudo field to query the time zone of the session.

However, for most databases, these values are offset values such as-, so it is useless for the NEW_TIME function. We recommend that you use FROM_TZ to replace Oracle9i's NEW_TIME documents, but this may be misleading. FROM_TZ only applies one time zone to one timestamp; it does not convert one time zone to another.

In fact, it may be a bit difficult to get this method from the document ). First, in order to do this, a timestamp with zone data type is required in the correct time ZONE. Then, if you apply the keyword at time zone to that value, it will be automatically adjusted to the new time zone and date.

select (timestamp '2003-04-06 01:59:59' at time zone 'PDT') at time zone 'GMT'from dual;06-APR-03 08.59.59.00000000 AM GMT

This statement constructs a timestamp with time zone for the Pacific daytime TIME, that is, the TIME before it is switched to PST, and then converts it to GMT. The at time zone keyword also accepts the default Offset Value Syntax:

select (timestamp '2003-04-06 02:00:00' at time zone '-07:00') at time zone'00:00' from dual;06-APR-03 09.00.00.000000000 AM +00:00

You can also use pseudo fields to automatically adjust the time zone of the current session:

selectcurrent_timestamp at time zone dbtimezone from dual;

The above expression returns the local time data type of the current session as the time zone), re-adjust the time zone of the database, the adjusted time zone will be equal to the result of SYSTIMESTAMP.

With the above information, we can construct a better NEW_TIME function:

create or replace function my_new_time(p_dwtz timestamp with time zone,p_tz varchar2) return dateisbeginreturn cast(p_dwtz at time zone p_tz as date);end my_new_time;/show errors;select my_new_time(sysdate,'+08:00') from dual;

Even if the first parameter is marked as a timestamp with time zone, you can still pass in a TIMESTAMP and DATE, because of the Automatic Transformation Operation of Oracle, the obtained time is the current time of the session in the local time zone. This function accepts any time zone that can be recognized by TIMESTAMP, including the offset value, and then adjusts the accepted time zone to the correct value.