oracle 将 unix 纪元时间转换为日期 [英] oracle convert unix epoch time to date

问题描述

上下文是我们的产品中有一个现有的应用程序，该应用程序生成 EPOCH 编号并将其发送到现有的 oracle 程序 &反之亦然.它使用类似这样的东西在该过程中工作

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

当我尝试这些查询时，它也适用于 Oracle 10g 服务器(以及 oracle sql developer 4.x，如果重要的话).

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

在现有程序中，要求将值保存为日期本身(时间部分无关紧要)，但是在新要求中，我必须将 unix EPOCH 值转换为日期时间(在小时/分钟/秒级别，或更好)oracle 查询中的特定格式，例如 dd-MMM-yyyy hh:mm:ss).奇怪的是，我无法通过 Google 找到有关 UTC_TO_DATE 和 DATE_TO_UTC 函数的任何文档.我已经查看了有关 stackoverflow 的所有不同问题，但其中大多数是针对 php、java 等编程语言的.

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.

底线，如何在 Oracle 查询中使用这些函数(或任何其他函数)将 EPOCH 转换为该时间级别?此外，我所指的那些功能可能是自定义的或特定于某处的，因为我没有看到任何文档或对此的参考.

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.

推荐答案

从纪元的毫秒转换(假设纪元是 1970 年 1 月 1 日):

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 上午

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

1322629200000

如果是秒而不是毫秒，只需省略等式的 1000 部分:

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;

希望有所帮助.

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