oracle 将 unix 纪元时间转换为日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37305135/
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 convert unix epoch time to date
提问by Subhash Dike
The context is that there is an existing application in our product which generates and sends the EPOCH number to an existing oracle procedure & vice versa. It works in that procedure using something like this
上下文是我们的产品中有一个现有的应用程序，它生成 EPOCH 编号并将其发送到现有的 oracle 程序，反之亦然。它使用这样的方法在该程序中工作
SELECT UTC_TO_DATE (1463533832) FROM DUAL
SELECT date_to_utc(creation_date) FROM mytable
When I tried these queries it does work for me as well with Oracle 10g server (and oracle sql developer 4.x if that matters).
当我尝试这些查询时，它也适用于 Oracle 10g 服务器（以及 oracle sql developer 4.x，如果这很重要）。
In the existing procedure the requirement was to save the value as date itself (time component was irrelevant), however in the new requirement I have to convert unix EPOCH value to datetime (at the hours/mins/seconds level, or better in a specific format such as dd-MMM-yyyy hh:mm:ss) in an oracle query. Strangely I am unable to find any documentation around the UTC_TO_DATE and DATE_TO_UTC functions with Google. I have looked around at all different questions on stackoverflow, but most of them are specific to programming languages such as php, java etc.
在现有程序中，要求将值保存为日期本身（时间组件无关紧要），但是在新要求中，我必须将 unix EPOCH 值转换为日期时间（在小时/分钟/秒级别，或者在特定的情况下更好） oracle 查询中的格式，例如 dd-MMM-yyyy hh:mm:ss)。奇怪的是，我无法通过 Google 找到有关 UTC_TO_DATE 和 DATE_TO_UTC 函数的任何文档。我已经查看了有关 stackoverflow 的所有不同问题，但其中大多数是针对 php、java 等编程语言的。
Bottom line, how to convert EPOCH to that level of time using these functions (or any other functions) in Oracle query? Additionally are those functions I am referring could be custom or specific somewhere, as I don't see any documentation or reference to this.
最重要的是，如何在 Oracle 查询中使用这些函数（或任何其他函数）将 EPOCH 转换为该时间级别？此外，我所指的那些功能可能是自定义的或特定于某处的，因为我没有看到任何文档或对此的参考。
回答by tbone
To convert from milliseconds from epoch (assume epoch is Jan 1st 1970):
从纪元的毫秒转换（假设纪元是 1970 年 1 月 1 日）：
select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 / 1000) * 1322629200000
from dual;
11/30/2011 5:00:00 AM
11/30/2011 5:00:00 上午
To convert that date back to milliseconds:
将该日期转换回毫秒：
select (to_date('11/30/2011 05:00:00', 'MM/DD/YYYY HH24:MI:SS') - to_date('19700101', 'YYYYMMDD')) * 24 * 60 * 60 * 1000
from dual;
1322629200000
1322629200000
If its seconds instead of milliseconds, just omit the 1000 part of the equation:
如果它是秒而不是毫秒，只需省略等式的 1000 部分：
select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 ) * 1322629200
from dual;
select (to_date('11/30/2011 05:00:00', 'MM/DD/YYYY HH24:MI:SS') - to_date('19700101', 'YYYYMMDD')) * 24 * 60 * 60
from dual;
Hope that helps.
希望有帮助。
回答by kpater87
Another option is to use an interval type:
另一种选择是使用间隔类型：
SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0'
,'YYYY-MM-DD HH24:MI:SS.FF'
) + NUMTODSINTERVAL(1493963084212/1000, 'SECOND')
FROM dual;
It has this advantage that milliseconds won't be cut.
它具有不会减少毫秒的优点。
回答by Bashobi
If your epoch time is stored as an integer..... And you desire the conversion to Oracle date format.
如果您的纪元时间存储为整数..... 并且您希望转换为 Oracle 日期格式。
Step 1--> Add your epoch date (1462086000) to standard 01-jan-1970. 86400 is seconds in a 24 hour period.
步骤 1--> 将您的纪元日期 (1462086000) 添加到标准 01-jan-1970。86400 是 24 小时内的秒数。
*Select TO_DATE('01-jan-1970', 'dd-mon-yyyy') + 1462086000/86400 from dual*
**output is 5/1/2016 7:00:00 AM**
Step 2--> Convert it to a CHAR . This is needed for formatting before additional functions can be applied.
步骤 2--> 将其转换为 CHAR 。这是在应用附加功能之前进行格式化所必需的。
*Select TO_CHAR(TO_DATE('01-jan-1970', 'dd-mon-yyyy') + 1462086000/86400 ,'yyyy-mm-dd hh24:mi:ss') from dual*
output is 2016-05-01 07:00:00
Step 3--> Now onto Timestamp conversion
步骤 3--> 现在进行时间戳转换
Select to_timestamp(TO_CHAR(TO_DATE('01-jan-1970', 'dd-mon-yyyy') + 1462086000/86400 ,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') from dual
output is 5/1/2016 7:00:00.000000000 AM
Step 4--> Now need the TimeZone, usage of UTC
步骤 4--> 现在需要时区，UTC 的用法
Select from_tz(to_timestamp(TO_CHAR(TO_DATE('01-jan-1970', 'dd-mon-yyyy') + 1462086000/86400 ,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),'UTC') from dual
output is 5/1/2016 7:00:00.000000000 AM +00:00
Step 5--> If your timezone need is PST
步骤 5--> 如果您的时区需要是 PST
Select from_tz(to_timestamp(TO_CHAR(TO_DATE('01-jan-1970', 'dd-mon-yyyy') + 1462086000/86400 ,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),'UTC') at time zone 'America/Los_Angeles' TZ from dual
output is 5/1/2016 12:00:00.000000000 AM -07:00
Step 6--> Format the PST Timezone timestamp.
步骤 6--> 格式化 PST 时区时间戳。
Select to_Char(from_tz(to_timestamp(TO_CHAR(TO_DATE('01-jan-1970', 'dd-mon-yyyy') + 1462086000/86400 ,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),'UTC') at time zone 'America/Los_Angeles' ,'DD-MON-YYYY HH24:MI:SS') TZ from dual
output is 01-MAY-2016 00:00:00
Step 7--> And finally, if your column is date datatype
步骤 7--> 最后，如果您的列是日期数据类型
Add to_DATE to the whole above Select.
回答by user1683793
I thought somebody would be interested in seeing an Oracle function version of this:
我以为有人会对看到这个的 Oracle 函数版本感兴趣：
CREATE OR REPLACE FUNCTION unix_to_date(unix_sec NUMBER)
RETURN date
IS
ret_date DATE;
BEGIN
ret_date:=TO_DATE('19700101','YYYYMMDD')+( 1/ 24/ 60/ 60)*unix_sec;
RETURN ret_date;
END;
/
I had a bunch of records I needed dates for so I updated my table with:
我有一堆需要日期的记录，所以我更新了我的表格：
update bobfirst set entered=unix_to_date(1500000000+a);
where a is a number between 1 and 10,000,000.
其中 a 是 1 到 10,000,000 之间的数字。
回答by Paul
Here it is for both UTC/GMT and EST;
这里是 UTC/GMT 和 EST；
GMT select (to_date('1970-01-01 00','yyyy-mm-dd hh24') +
(1519232926891)/1000/60/60/24) from dual;
EST select new_time(to_date('1970-01-01 00','yyyy-mm-dd hh24') +
(1519232926891)/1000/60/60/24, 'GMT', 'EST') from dual;
