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');

---

Solution

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 

Comments

[dldnh]

this is working great for me - do you happen to have some code that goes the other way, from a string to seconds after epoch?

[Przemyslaw Kruglej]

@dldnh Could you provide a sample data and what output are you expecting?