10

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?

MT0
  • 143,790
  • 11
  • 59
  • 117
  • What exactly do you want to handle? A date/timestamp can't exist in that 61st second; so are you kind of asking what sysdate/systimestamp will show if the operating system clock does allow that? Or are you getting external data that has the 61st second and want to interpret/store it? – Alex Poole Jun 30 '15 at 10:59
  • If I get data from an external source that is `2015-06-30 23:59:60` it will error and can not be stored. If I do `SELECT EXTRACT( SECOND FROM TIMESTAMP '2015-07-01 00:00:00.000' - TIMESTAMP '2015-06-30 23:59:59.000' ) FROM DUAL` the answer should be `2` but Oracle returns `1`. I assume there is nothing that can be done but if there is something somewhere that can be updated with a list of leap seconds so Oracle takes this into account then it would be useful to know. – MT0 Jun 30 '15 at 11:04
  • OK, then look at MOS note 2019397.2; and 730795.1. Doesn't look like anything can be done though. Other than a hack like replacing `:60` with `:59` I suppose... – Alex Poole Jun 30 '15 at 11:11
  • 1
    @MT0 actually, that is only correct in the time zones where the time is equal to UTC. Leap seconds are added to all time zones simultaneously. So in fact, Oracle's error message is in this specific case indeed correct in the major part of the world, since no time zone is chosen. In all time zones however, the sum of the number of seconds in June 30 and July 1 should be 172801 and not 172800. – pvoosten Jul 12 '15 at 19:59
  • If you are really concerned about leap seconds, then maybe better store the time as [Julian Date](https://en.wikipedia.org/wiki/Julian_day) values. – Wernfried Domscheit Apr 08 '21 at 09:10
  • @WernfriedDomscheit The `J` format model for `TO_DATE` and `TO_CHAR` does not support fractions of days and that would, again, come down to writing a custom solution for handling dates and performing date arithmetic. Also, how would you store `2015-06-30 23:59:60`? If you are you talking about storing it as the decimal value of `2457113 + 86400 / 86401` then you would still need to know which days have leap seconds and all the decimal parts from those days would be slightly off from the decimal parts of the same time instant on other non-leap second days. – MT0 Apr 08 '21 at 19:05
  • I think, if you really need such precision (e.g. for astronomic observation) then your source system handles it properly. In this case you should stay in the time scale / time format given by the source system and not try to convert it into an Oracle `DATE` or `TIMESTAMP` value. If you convert such a value into an Oracle `DATE/TIMESTAMP`, then you know it is just an approximation. But I fully agree, it is a dilemma when you get `2015-06-30 23:59:60` from external source and you need to run date arithmetic on it. – Wernfried Domscheit Apr 08 '21 at 20:38

1 Answers1

2

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.

Community
  • 1
  • 1
Anjan Biswas
  • 7,746
  • 5
  • 47
  • 77
  • 4
    That's a dreadful solution, and contradicts every best practice for handling dates in Oracle. If this was a critical issue then I'd rather find a way of using a modified timestamp arithmetic to correctly express the difference in seconds across one or more leap seconds, and I'm surprised that Oracle Corp hasn't done that. – David Aldridge Jul 15 '15 at 22:14
  • @DavidAldridge I agree. Although, they have other critical issues, not specifically RDBMS related, that they have released several patches for, and those Leap second issues list from 100% CPU utilization to straight up server unavailability risks. So I just think that they had bigger fish to fry. – Anjan Biswas Jul 15 '15 at 22:17
  • 3
    While this is a "solution" to storing leap seconds (and I'll agree it goes against all best practice); it doesn't solve the additional issue of date arithmetic across leap seconds - you would then have to create your own functions to handle arithmetic and have a look-up table of leap seconds to apply an adjustment to calculations. Looks like Oracle are suggesting you would have to re-implement date/time handling from scratch to solve this. – MT0 Jul 15 '15 at 23:44