How to handle leap seconds in Oracle

Question

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

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

Errors with:

ORA-01852: seconds must be between 0 and 59

and

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

Gives the output:

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

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

Answer 1

From MOS-

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

APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.1.7.4 and later

Oracle Database - Standard Edition - Version 8.1.7.4 and later

Information in this document applies to any platform.

SYMPTOMS:

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

CAUSE

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

SOLUTION

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.