Oracle: epoch milleseconds to date/time with time zone included

Question

I'm a PL/SQL newbie who needs to convert milleseconds since unix epoch to a date/time. I can convert to GMT date/time but don't know how to adjust for the time zone. I'm close but not quite there.

My input is r_msg.OriginationTime, which has a value like 1382552100277

This

MpD NUMBER        := (1/24/60/60/1000);        -- Milleseconds per Day

DFmt24 VARCHAR2(21) := 'MM/DD/YYYY HH24:MI:SS';    -- Date format

TMPorig24        VARCHAR2(20);

. . .

TMPorig24 := TO_CHAR( DATE '1970-01-01' + MpD * r_msg.OriginationTime, DFmt24);

gives something like

10/23/2013 18:15:00

which is just what I want except it's GMT.

This

    TimeZoneOffset VARCHAR(7);

    . . . 

    TimeZoneOffset := tz_offset('America/New_York' );

gives

-04:00

So I just need to do something like

TMPorig24 := TMPorig24 + TimeZoneOffset;

but I get

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

I've tried several variations but nothing works.

Any help appreciated.

Thanks but I'm having problems with the two solutions.

The first solution prints the same time regardless of the time zone. For example, these print the same values.

TMPorig := TO_CHAR( FROM_TZ( CAST(DATE '1970-01-01' + (1/24/60/60/1000) * r_msg.OriginationTime AS TIMESTAMP), 'America/New_York'), 'MM/DD/YYYY HH24:MI:SS');

TMPorig2 := TO_CHAR( FROM_TZ( CAST(DATE '1970-01-01' + (1/24/60/60/1000) * r_msg.OriginationTime AS TIMESTAMP), 'Pacific/Pago_Pago'), 'MM/DD/YYYY HH24:MI:SS');

The second solution

TMPorig := TO_CHAR( DATE '1970-01-01' + (1/24/60/60/1000) * r_msg.OriginationTime + INTERVAL '-04:00' HOUR TO MINUTE, 'MM/DD/YYYY HH24:MI:SS');

gives

PLS-00166: bad format for date, time, timestamp or interval literal

Moveover, '04:00' will be wrong when Daylight Savings Time ends. I need an expression for the time difference between EST/EDT and GMT.

********* WORKS PERFECT THANKS **************

TMPorig2 := TO_CHAR ( FROM_TZ ( CAST (DATE '1970-01-01' + (1/24/60/60/1000) * r_msg.OriginationTime AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York', 'MM/DD/YYYY HH24:MI:SS');

Przemyslaw Kruglej Przemyslaw Kruglej · Accepted Answer · 2013-10-24T15:05:58

Edit: Ok, try this solution instead:

SELECT
  TO_CHAR (
    FROM_TZ (
      CAST (DATE '1970-01-01' + (1/24/60/60/1000) * 1382552100277 AS TIMESTAMP),
      'UTC')
    AT TIME ZONE 'America/New_York',
  'MM/DD/YYYY HH24:MI:SS') val
FROM dual;

Output:

VAL               
-------------------
10/23/2013 14:15:00

You have to cast the DATE to TIMESTAMP and use the FROM_TZ function to convert the TIMESTAMP into TIMESTAMP WITH TIME ZONE datatype. The timezone parameter can be in either format: America/New_York or -04:00.

SELECT
  TO_CHAR(
    FROM_TZ(
      CAST(DATE '1970-01-01' + (1/24/60/60/1000) * 1382552100277 AS TIMESTAMP),
      'America/New_York'),
    'MM/DD/YYYY HH24:MI:SS')
FROM dual;

That is if you want to have a TIMESTAMP WITH TIME ZONE variable. If you want to add the offset from given time zone, then you can use:

SELECT
  TO_CHAR(
      DATE '1970-01-01' + (1/24/60/60/1000) * 1382552100277 + INTERVAL '-04:00' HOUR TO MINUTE,
    'MM/DD/YYYY HH24:MI:SS') AS val
FROM dual;

Output:

VAL               
-------------------
10/23/2013 14:15:00

Oracle: epoch milleseconds to date/time with time zone included

1 Answers