Oracle：包含时区的纪元毫秒到日期/时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19568635/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: epoch milleseconds to date/time with time zone included
提问by user2680126
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.
我是 PL/SQL 新手，需要将自 Unix 纪元以来的毫秒数转换为日期/时间。我可以转换为 GMT 日期/时间，但不知道如何调整时区。我很接近但不完全在那里。
My input is r_msg.OriginationTime, which has a value like 1382552100277
我的输入是 r_msg.OriginationTime，它的值类似于 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');
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');
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');
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.
另外，夏令时结束时，'04:00' 将是错误的。我需要一个表示 EST/EDT 和 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');
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');
回答by Przemyslaw Kruglej
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 DATEto TIMESTAMPand use the FROM_TZfunction to convert the TIMESTAMPinto TIMESTAMP WITH TIME ZONEdatatype. The timezone parameter can be in either format: America/New_Yorkor -04:00.
您必须强制转换DATE为TIMESTAMP并使用该FROM_TZ函数将转换TIMESTAMP为TIMESTAMP WITH 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;
That is if you want to have a TIMESTAMP WITH TIME ZONEvariable. If you want to add the offset from given time zone, then you can use:
也就是说，如果你想要一个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;
Output:
输出：
VAL ------------------- 10/23/2013 14:15:00
