oracle转换unix时代到目前为止 [英] oracle convert unix epoch time to date

问题描述

上下文是在我们的产品中存在一个现有的应用程序，它生成并发送EPOCH号码到现有的oracle程序&反之亦然。它的工作原理是使用这样的方式

  SELECT UTC_TO_DATE（1463533832）FROM DUAL 
 SELECT date_to_utc（creation_date）FROM mytable 
  

当我尝试这些查询时，它对我来说也适用于Oracle 10g服务器（和oracle sql开发人员4.x如果重要）。

在现有的过程中，要求是将值保存为日期本身（时间组件不相关），但是在新的需求中，我必须将unix EPOCH值转换为datetime （以小时/分钟/秒的水平，或更好的特定格式，如dd-MMM-yyyy hh：mm：ss）在oracle查询。奇怪的是，我无法找到关于UTC_TO_DATE和DATE_TO_UTC功能的任何文档。我在Stackoverflow中查看了所有不同的问题，但大多数都是专门针对诸如php，java等编程语言。

底线，如何将EPOCH转换为在Oracle查询中使用这些功能（或任何其他功能）的时间级别？另外，我指的是那些可以定制或具体的功能，因为我没有看到任何文档或参考。

解决方案

p>要从时期的毫秒转换（假设epoch是1970年1月1日）：

  select to_date（'19700101'，'YYYYMMDD '）+（1/24/60/60/1000）* 1322629200000 
 from dual; 
  

11/30/2011 5:00:00 AM

将该日期转换为毫秒：

  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

如果它的秒而不是毫秒，只需省略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; 
  

希望有所帮助。

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

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

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.

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.

解决方案

To convert from milliseconds from epoch (assume epoch is Jan 1st 1970):

select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 / 1000) * 1322629200000
from dual;

11/30/2011 5:00:00 AM

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

If its seconds instead of milliseconds, just omit the 1000 part of the equation:

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.

这篇关于oracle转换unix时代到目前为止的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！