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

Question

我是一个PL/SQL新手，需要将unix时代以来的千秒转换为日期/时间。我可以转换为GMT日期/时间，但不知道如何调整时区。我很接近，但不太接近。

我的输入是r\u msg。OriginationTime，其值类似于138255210277

这个

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：=至\u CHAR（从\u TZ（演员阵容（日期‘1970-01-01’（1/24/60/60/1000）*r\u消息。“起始时间（作为时间戳）”，“美国/纽约”），“MM/DD/YYYY HH24:MI:SS”）；

TMPorig2：=至\u CHAR（自\u TZ（演员阵容（日期‘1970-01-01’（1/24/60/60/1000）*r\u消息。“起始时间（作为时间戳）”，“太平洋/帕果岛”），“MM/DD/YYYY HH24:MI:SS”）；

第二种解决方案

TMPorig：=TO_CHAR（DATE'1970-01-01'（1/24/60/60/1000)*r_msg。发起时间间隔'-04:00'小时到分钟，'MM/DD/YYYY HH24： MI： SS'）；

给予

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

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

*********很好用谢谢**************

TMPorig2：=至\u CHAR（自\u TZ（演员阵容（日期‘1970-01-01’（1/24/60/60/1000）*r\u消息。“美国/纽约”、“YYYY年/月/日HH24:MI:SS”时区的“起始时间”（时间戳）、“UTC”）；

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 

您必须将DATE转换为TIMESTAMP，并使用FROM_TZ函数将TIMESTAMP转换为TIMESTAMP AND TIME ZONE数据类型。时区参数可以是以下两种格式之一：America/New_York或-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;

也就是说，如果您想要有一个带有时区变量的时间戳。如果要添加与给定时区的偏移，则可以使用：

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;

输出：

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