title: Oracle: epoch milleseconds to date/time with time zone included
body: <p>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.</p>
  
  <p>My input is r_msg.OriginationTime, which has a value like 1382552100277</p>
  
  <p>This </p>
  
  <pre><code>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);
  </code></pre>
  
  <p>gives something like</p>
  
  <pre><code>10/23/2013 18:15:00
  </code></pre>
  
  <p>which is just what I want except it's GMT.</p>
  
  <p>This</p>
  
  <pre><code>    TimeZoneOffset VARCHAR(7);
  
      . . . 
  
      TimeZoneOffset := tz_offset('America/New_York' );
  </code></pre>
  
  <p>gives</p>
  
  <pre><code>-04:00
  </code></pre>
  
  <p>So I just need to do something like</p>
  
  <pre><code>TMPorig24 := TMPorig24 + TimeZoneOffset;
  </code></pre>
  
  <p>but I get </p>
  
  <pre><code>ORA-06502: PL/SQL: numeric or value error: character to number conversion error
  </code></pre>
  
  <p>I've tried several variations but nothing works.</p>
  
  <p>Any help appreciated.</p>
  
  <hr>
  
  <p>Thanks but I'm having problems with the two solutions.</p>
  
  <p>The first solution prints the same time regardless of the time zone.
  For example, these print the same values.</p>
  
  <p>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');</p>
  
  <p>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');</p>
  
  <p>The second solution</p>
  
  <p>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');</p>
  
  <p>gives</p>
  
  <pre><code>PLS-00166: bad format for date, time, timestamp or interval literal
  </code></pre>
  
  <p>Moveover, '04:00' will be wrong when Daylight Savings Time ends. 
  I need an expression for the time difference between EST/EDT and GMT.</p>
  
  <p><em><strong></em>**<em>*</em>**<em>*</em>**<em>*</em></strong> WORKS PERFECT THANKS <strong><em>*</em>**<em>*</em>**<em>*</em>**<em>*</em>**<em>*</em>*</strong></p>
  
  <p>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');</p>
  
  <hr>