logo标签列表

Oracle如何将Unix纪元时间转换为日期？

oracledatetimeunixdate-formatepoch
21

背景是我们的产品中有一个现有应用程序，它会生成并发送EPOCH数字给现有的Oracle过程，反之亦然。在该过程中使用类似于以下内容的内容实现：

SELECT UTC_TO_DATE (1463533832) FROM DUAL
SELECT date_to_utc(creation_date) FROM mytable

当我尝试这些查询时，它对我也适用于Oracle 10g服务器（以及如果有影响的话，oracle sql developer 4.x）。

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

底线是，如何使用这些函数（或任何其他函数）在Oracle查询中将EPOCH转换为那个级别的时间？此外，我所说的这些函数是否可以在某处自定义或具体化，因为我没有看到任何文档或参考资料。

这些不是标准函数，它们是你的应用程序的一部分。你需要找到它们的源代码（在源代码控制中；或者在user_source或all_source中，如果前者不存在）。但是你想修改它们吗？想要包含时间的新版本，还是独立的转换？你还需要检查你传递/获取的日期是否也是UTC或代表不同的区域 - 你的函数可能正在调整它们。 - Alex Poole
@AlexPoole 感谢您的建议。我确实想到它们可能是自定义的，但必须承认，我没有意识到它们可以在我的代码库中自定义。所以是的，我在源代码控制中找到了它们的定义，并且它们的内部工作方式与tbone下面分享的几乎相同。 - Subhash Dike
他们会将日期更改为不同的时区，还是假设日期也表示UTC？鉴于你对tbone回答的评论，你确定他们是否已经设置了时间组件，只是你没有看到吗？ - Alex Poole
@AlexPoole，这是他们提供的代码 - RETURN to_date('01.01.70','dd.mm.rr') + (utc_in/(606024))。所以我认为他们实际上并没有改变为本地时间，他们也想将其存储为UTC时间..而我也是如此。是的，一旦我在客户端更改了格式，我也可以看到时间以他们的自定义函数形式进入。 - Subhash Dike
6个回答
39

将毫秒数从纪元开始（假设纪元为1970年1月1日）进行转换：

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

2011年11月30日上午5:00:00

将该日期转换回毫秒：

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;

希望这有所帮助。

4
请注意，只有当您的本地时区也是协调世界时（UTC）时，才能得到正确的结果。 - Wernfried Domscheit
1
@SubhashDike - 这只是您的客户端显示日期的方式；更改您的NLS_DATE_FORMAT或（最好）使用to_char()指定格式，使用类似于“YYYY-MM-DD HH24:MI:SS”的掩码。 - Alex Poole
@AlexPoole 好建议。我调整了我的SQL开发者设置，现在看到的是完美的！- TO_DATE（'19700101'，'YYYYMMDDH - Subhash Dike
from dual;``` 这段代码没有格式化为小时分钟秒。 - DarylF
1
嗨@DarylF，看看上面Alex Poole的评论，你的客户端默认日期格式掩码省略了小时/分钟/秒部分。 - tbone
显示剩余2条评论
13

另一个选项是使用间隔类型：

SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0'
                   ,'YYYY-MM-DD HH24:MI:SS.FF'
       ) + NUMTODSINTERVAL(1493963084212/1000, 'SECOND')
FROM dual;

它的优点在于毫秒不会被截断。

一个时代通常处于UTC时区。您可以使用TO_TIMESTAMP_TZ并将UTC添加到字符串中，或者更简单地使用时间戳字面量TIMESTAMP '1970-01-01 00:00:00 UTC'，而不是TO_TIMESTAMP。 - MT0
4
如果您的时代时间以整数形式存储... 并且您希望将其转换为Oracle日期格式。
步骤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**

第二步-->将其转换为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

第三步--> 现在开始时间戳转换。
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

步骤四 --> 现在需要时区，使用协调世界时（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

步骤5--> 如果您的时区需求是太平洋标准时间

 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

步骤 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

第七步-->> 最后，如果您的列是日期数据类型
   Add to_DATE to the whole above Select.
将字符串转换为日期，然后再转换为字符，最后再转换为时间戳似乎是毫无意义的。 - Wernfried Domscheit
2
这是针对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;
1
我想有人会对这个的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;
/

我有一堆需要日期的记录，所以我使用以下代码更新了我的表格：

update bobfirst set entered=unix_to_date(1500000000+a);

其中a是1到10,000,000之间的数字。

0
一种将时间戳转换为纳秒的更短方法。
SELECT (EXTRACT(DAY FROM (
    SYSTIMESTAMP --Replace line with desired timestamp --Maximum value: TIMESTAMP '3871-04-29 10:39:59.999999999 UTC'
- TIMESTAMP '1970-01-01 00:00:00 UTC') * 24 * 60) * 60 + EXTRACT(SECOND FROM
    SYSTIMESTAMP --Replace line with desired timestamp
)) *  1000000000 AS NANOS FROM DUAL;

NANOS
1598434427263027000

将纳秒转换为时间戳的方法。
SELECT TIMESTAMP '1970-01-01 00:00:00 UTC' + numtodsinterval(
    1598434427263027000 --Replace line with desired nanoseconds
/ 1000000000, 'SECOND') AS TIMESTAMP FROM dual;

TIMESTAMP
26/08/20 09:33:47,263027000 UTC

正如预期的那样，上述方法的结果不受时区影响。

将时间间隔转换为纳秒的一种更短的方法。

SELECT (EXTRACT(DAY FROM (
    INTERVAL '+18500 09:33:47.263027' DAY(5) TO SECOND --Replace line with desired interval --Maximum value: INTERVAL '+694444 10:39:59.999999999' DAY(6) TO SECOND(9) or up to 3871 year
) * 24 * 60) * 60 + EXTRACT(SECOND FROM (
    INTERVAL '+18500 09:33:47.263027' DAY(5) TO SECOND --Replace line with desired interval
))) * 1000000000 AS NANOS FROM DUAL;

NANOS
1598434427263027000

将纳秒转换为时间间隔的方法。

SELECT numtodsinterval(
    1598434427263027000 --Replace line with desired nanoseconds
/ 1000000000, 'SECOND') AS INTERVAL FROM dual;

INTERVAL
+18500 09:33:47.263027

如预期的那样，毫秒、微秒和纳秒都可以转换和还原，尽管SYSTIMESTAMP没有纳秒信息。

例如，如果您想使用毫秒而不是纳秒，请将1000000000替换为1000。

我尝试了一些发布的方法，但几乎所有方法都受到时区的影响或在还原后导致数据丢失，因此我决定发布对我有效的方法。

5
如果您认为这个答案回答了您刚刚发布的其他10个问题，正确的做法不是发布11个答案，而是发布一个答案，并将其他10个问题标记为此问题的重复。 - David Buck
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接