1. Home
  2. Docs
  3. Oracle to PostgreSQL Migration
  4. PLSQL vs plpgsql
  5. Migrate Oracle standard.new_time to Postgres

Migrate Oracle standard.new_time to Postgres

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 likeNEW_TIME.

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;
OraclePostgreSQL
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;

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;

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;

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;

select NEW_TIME(’08-07-2017 01:30:45′::timestamp, ‘est’, ‘ast’);

select NEW_TIME(’08-07-2017 14:30:45′::timestamp, ‘est’, ‘PST’);

select NEW_TIME(’08-07-2017 01:30:45′::timestamp, ‘AST’, ‘GMT’);

select NEW_TIME(’08-07-2017 01:30:45′::timestamp, ‘EST’, ‘GMT’);

Was this article helpful to you? Yes No

How can we help?