Oracle:包含时区的纪元毫秒到日期/时间

Question

我是一个 PL/SQL 新手，需要将自 unix epoch 以来的毫秒数转换为
日期/时间。我可以转换为 GMT 日期/时间，但不知道如何调整
对于时区。我很接近但不完全在那里。

我的输入是 r_msg.OriginationTime，它的值类似于 1382552100277

这

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

给出类似的东西

10/23/2013 18:15:00

这正是我想要的，除了格林威治标准时间。

这

    TimeZoneOffset VARCHAR(7);

    . . . 

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

给

-04:00

所以我只需要做类似的事情

TMPorig24 := TMPorig24 + TimeZoneOffset;

但我明白了

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

我尝试了几种变体，但没有任何效果。

任何帮助表示赞赏。

谢谢，但我在使用这两种解决方案时遇到了问题。

无论时区如何，第一个解决方案都会打印相同的时间。
例如，这些打印相同的值。

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

第二种解决方案

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

给

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

另外，夏令时结束时，'04:00' 将是错误的。
我需要一个表示 EST/EDT 和 GMT 之间时差的表达式。

********* 作品完美感谢 **************

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

Answer 1

编辑 :好的，试试这个解决方案:

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;

输出:

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;

也就是说，如果你想拥有一个 TIMESTAMP WITH TIME ZONE变量。如果要添加给定时区的偏移量，则可以使用:

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;

输出:

值(value)
-------------------
10/23/2013 14:15:00