如何在Oracle中处理leap秒 [英] How to handle leap seconds in Oracle

问题描述

今晚将有一个leap秒，并且一天的最后一小时的最后一分钟将有61秒.

Tonight there is going to be a leap second added to the clocks and there will be 61 seconds in the last minute of the last hour of the day.

2015-06-30 23:59:60

但是，Oracle一分钟最多只能支持60秒:

However, Oracle only supports up to 60 seconds in a minute:

TO_DATE( '2015-06-30 23:59:60', 'YYYY-MM-DD HH24:MI:SS' )    

错误:

ORA-01852: seconds must be between 0 and 59

和

SELECT TO_DATE( '2015-06-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS' ) + INTERVAL '1' SECOND AS Incr_Second_Before,
       TO_DATE( '2015-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' ) - INTERVAL '1' SECOND AS Decr_Second_After
FROM   DUAL

给出输出:

|     INCR_SECOND_BEFORE |      DECR_SECOND_AFTER |
|------------------------|------------------------|
| July, 01 2015 00:00:00 | June, 30 2015 23:59:59 |

在Oracle中有什么方法可以解决a秒问题?

Is there any way to handle a leap second in Oracle?

推荐答案

来自MOS-

将leap秒插入时间戳列会失败，并出现ORA-01852(文档 ID 1553906.1)

Insert leap seconds into a timestamp column fails with ORA-01852 (Doc ID 1553906.1)

适用于:

Oracle数据库-企业版-版本8.1.7.4及更高版本

Oracle Database - Enterprise Edition - Version 8.1.7.4 and later

Oracle数据库-标准版-版本8.1.7.4及更高版本

Oracle Database - Standard Edition - Version 8.1.7.4 and later

本文档中的信息适用于任何平台.

Information in this document applies to any platform.

症状:

尝试在时间戳列中插入column秒，但失败: ORA-01852:秒必须在0到59之间

An attempt to insert leap seconds into a timestamp column, fails with: ORA-01852: seconds must be between 0 and 59

原因

不能在日期或时间戳中存储> 59秒的值 数据类型

It is not possible to store >59 sec value in a date or timestamp datatype

解决方案

要解决此问题，可以将the秒记录存储在 而不是varchar2数据类型，例如

To workaround this issue, the leap second record can be stored in a varchar2 datatype instead e.g.

SQL> create table test (val number, t varchar2(30));

Table created.

SQL> insert into test values(123, '2012-06-30T23:59:60.000000Z');

1 row created.

不是最好的解决方案，而是唯一的解决方案.

Not the best solution, but the only solution.

这篇关于如何在Oracle中处理leap秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！