The Oracle PL/SQL NEW_TIME function converts a date and time from one timezone to a date in another timezone.

Syntax:

NEW_TIME( date, timezone1, timezone2 )

Examples:

The following example returns an Atlantic Standard time, given the Pacific Standard time equivalent:

ALTER SESSION SET NLS_DATE_FORMAT =
   'DD-MON-YYYY HH24:MI:SS';

SELECT NEW_TIME(TO_DATE(
   '11-10-99 01:23:45', 'MM-DD-YY HH24:MI:SS'),
   'AST', 'PST') "New Date and Time" FROM DUAL;

New Date and Time
--------------------
09-NOV-1999 21:23:45

NEW_TIME function takes as input only a limited number of time zones. You can have access to a much greater number of time zones by combining the FROM_TZ function and the datetime expression. The acceptable arguments for timezone1 and timezone2 can be found in Oracle NEW_TIME documentation.

PostgreSQL does not have any similar function like NEW_TIME. Hence, let’s create a wrapper function in PostgreSQL.

Introducing NEW_TIME wrapper function

CREATE OR REPLACE FUNCTION NEW_TIME(
IN i_date TIMESTAMP WITHOUT TIME ZONE,
IN i_src_tz CHARACTER VARYING,
IN i_dest_tz CHARACTER VARYING)
RETURNS TIMESTAMP WITHOUT TIME ZONE
AS
$BODY$
DECLARE
v_tm TIMESTAMP WITHOUT TIME ZONE;
BEGIN
v_tm := ( ( (i_date::TIMESTAMP WITHOUT TIME ZONE) AT TIME ZONE i_src_tz) AT TIME ZONE i_dest_tz );
Return v_tm;
END;
$BODY$ LANGUAGE plpgsql;

Testing the above wrapper function

Oracle:

SELECT
 '08-07-2017 01:30:45' tm1, 
 to_char(NEW_TIME( TO_DATE( '08-07-2017 01:30:45', 'MM-DD-YYYY HH24:MI:SS' ),  'est', 'ast'), 'MM-DD-YYYY HH24:MI:SS') tm2
from dual;

TM1		    TM2
------------------- -------------------
08-07-2017 01:30:45 08-07-2017 02:30:45

SELECT
 '08-07-2017 14:30:45' tm1, 
 to_char(NEW_TIME( TO_DATE( '08-07-2017 14:30:45', 'MM-DD-YYYY HH24:MI:SS' ),  'est', 'PST'), 'MM-DD-YYYY HH24:MI:SS') tm2
from dual;

TM1		    TM2
------------------- -------------------
08-07-2017 14:30:45 08-07-2017 11:30:45

SELECT
 '08-07-2017 01:30:45' tm1, 
 to_char(NEW_TIME( TO_DATE( '08-07-2017 01:30:45', 'MM-DD-YYYY HH24:MI:SS' ),  'AST', 'GMT'), 'MM-DD-YYYY HH24:MI:SS') tm2
from dual;

TM1		    TM2
------------------- -------------------
08-07-2017 01:30:45 08-07-2017 05:30:45

SELECT
 '08-07-2017 01:30:45' tm1, 
 to_char(NEW_TIME( TO_DATE( '08-07-2017 01:30:45', 'MM-DD-YYYY HH24:MI:SS' ),  'EST', 'GMT'), 'MM-DD-YYYY HH24:MI:SS') tm2
from dual;

TM1		    TM2
------------------- -------------------
08-07-2017 01:30:45 08-07-2017 06:30:45

PostgreSQL:

postgres=# select NEW_TIME('08-07-2017 01:30:45'::timestamp, 'est', 'ast') tm2;
         tm2         
---------------------
 2017-08-07 02:30:45
(1 row)

postgres=# select NEW_TIME('08-07-2017 14:30:45'::timestamp, 'est', 'PST') tm2;
         tm2         
---------------------
 2017-08-07 11:30:45
(1 row)

postgres=# select NEW_TIME('08-07-2017 01:30:45'::timestamp, 'AST', 'GMT') tm2;
         tm2         
---------------------
 2017-08-07 05:30:45
(1 row)

postgres=# select NEW_TIME('08-07-2017 01:30:45'::timestamp, 'EST', 'GMT') tm2;
         tm2         
---------------------
 2017-08-07 06:30:45
(1 row)