Convert Date with Time Zone

On occasion giving a date time with a time zone, we want to get the date time under another time zone, how to do it? Here gives a solution:

FUNCTION fun_exchange_timestamp_tz(pv_timestamp TIMESTAMP,
                                     pv_from_tz   VARCHAR2,
                                     pv_to_tz     VARCHAR2) RETURN TIMESTAMP
    WITH TIME ZONE AS
  BEGIN
    IF pv_timestamp IS NULL
    THEN
      RETURN NULL;
    END IF;
    RETURN from_tz(pv_timestamp, pv_from_tz) at TIME ZONE pv_to_tz;
  END fun_exchange_timestamp_tz;

  /*exchange the date from one time zone to other time zone.*/
  FUNCTION fun_exchange_date_tz(pv_date    DATE,
                                pv_from_tz VARCHAR2,
                                pv_to_tz   VARCHAR2) RETURN DATE AS
  BEGIN
    RETURN fun_exchange_timestamp_tz(CAST(pv_date AS TIMESTAMP),
                                     pv_from_tz,
                                     pv_to_tz);
  END;

We can use those functions as below:

select fun_exchange_timestamp_tz(sysdate,'Asia/Shanghai','GMT') from dual;

select fun_exchange_date_tz(sysdate,'Asia/Shanghai','GMT') from dual; 

These sqls convert sysdate from time zone 'Asia/Shanghai' time zone ‘GMT’.