SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Known limitation for max. Seconds on INTERVAL DAY TO SECOND Datatype?

WernfriedFeb 20 2015 — edited Feb 20 2015

Based on this question Convert LDAP Date in SQL I tried following query:

SELECT LEVEL AS YEAR, LEVEL*365.25*24*60*60 AS seconds,
    TIMESTAMP '1601-01-01 00:00:00' + NUMTODSINTERVAL(LEVEL*365.25*24*60*60, 'SECOND') AS TS_BY_SECOND
FROM dual
CONNECT BY LEVEL < 100;

And the result is this:

YEAR SECONDS TS_BY_SECOND

1 31557600 01.01.1602 06:00:00.000000000

2 63115200 01.01.1603 12:00:00.000000000

3 94672800 01.01.1604 18:00:00.000000000

4 126230400 01.01.1605 00:00:00.000000000

5 157788000 01.01.1606 06:00:00.000000000

6 189345600 01.01.1607 12:00:00.000000000

7 220903200 01.01.1608 18:00:00.000000000

8 252460800 01.01.1609 00:00:00.000000000

9 284018400 01.01.1610 06:00:00.000000000

10 315576000 01.01.1611 12:00:00.000000000

11 347133600 01.01.1612 18:00:00.000000000

12 378691200 01.01.1613 00:00:00.000000000

13 410248800 01.01.1614 06:00:00.000000000

14 441806400 01.01.1615 12:00:00.000000000

15 473364000 01.01.1616 18:00:00.000000000

16 504921600 01.01.1617 00:00:00.000000000

17 536479200 01.01.1618 06:00:00.000000000

18 568036800 01.01.1619 12:00:00.000000000

19 599594400 01.01.1620 18:00:00.000000000

20 631152000 01.01.1621 00:00:00.000000000

21 662709600 01.01.1622 06:00:00.000000000

22 694267200 01.01.1623 12:00:00.000000000

23 725824800 01.01.1624 18:00:00.000000000

24 757382400 01.01.1625 00:00:00.000000000

25 788940000 01.01.1626 06:00:00.000000000

26 820497600 01.01.1627 12:00:00.000000000

27 852055200 01.01.1628 18:00:00.000000000

28 883612800 01.01.1629 00:00:00.000000000

29 915170400 01.01.1630 06:00:00.000000000

30 946728000 01.01.1631 12:00:00.000000000

31 978285600 01.01.1632 18:00:00.000000000

32 1009843200 01.01.1633 00:00:00.000000000

33 1041400800 01.01.1634 06:00:00.000000000

34 1072958400 01.01.1635 12:00:00.000000000

35 1104516000 01.01.1636 18:00:00.000000000

36 1136073600 01.01.1637 00:00:00.000000000

37 1167631200 01.01.1638 06:00:00.000000000

38 1199188800 01.01.1639 12:00:00.000000000

39 1230746400 01.01.1640 18:00:00.000000000

40 1262304000 01.01.1641 00:00:00.000000000

41 1293861600 01.01.1642 06:00:00.000000000

42 1325419200 01.01.1643 12:00:00.000000000

43 1356976800 01.01.1644 18:00:00.000000000

44 1388534400 01.01.1645 00:00:00.000000000

45 1420092000 01.01.1646 06:00:00.000000000

46 1451649600 01.01.1647 12:00:00.000000000

47 1483207200 01.01.1648 18:00:00.000000000

48 1514764800 01.01.1649 00:00:00.000000000

49 1546322400 01.01.1650 06:00:00.000000000

50 1577880000 01.01.1651 12:00:00.000000000

51 1609437600 01.01.1652 18:00:00.000000000

52 1640995200 01.01.1653 00:00:00.000000000

53 1672552800 01.01.1654 06:00:00.000000000

54 1704110400 01.01.1655 12:00:00.000000000

55 1735668000 01.01.1656 18:00:00.000000000

56 1767225600 01.01.1657 00:00:00.000000000

57 1798783200 01.01.1658 06:00:00.000000000

58 1830340800 01.01.1659 12:00:00.000000000

59 1861898400 01.01.1660 18:00:00.000000000

60 1893456000 01.01.1661 00:00:00.000000000

61 1925013600 01.01.1662 06:00:00.000000000

62 1956571200 01.01.1663 12:00:00.000000000

63 1988128800 01.01.1664 18:00:00.000000000

64 2019686400 01.01.1665 00:00:00.000000000

65 2051244000 01.01.1666 06:00:00.000000000

66 2082801600 01.01.1667 12:00:00.000000000

67 2114359200 01.01.1668 18:00:00.000000000

68 2145916800 01.01.1669 00:00:00.000000000

69 2177474400 19.01.1669 03:14:09.147483647

70 2209032000 19.01.1669 03:14:09.147483647

71 2240589600 19.01.1669 03:14:09.147483647

72 2272147200 19.01.1669 03:14:09.147483647

73 2303704800 19.01.1669 03:14:09.147483647

74 2335262400 19.01.1669 03:14:09.147483647

75 2366820000 19.01.1669 03:14:09.147483647

76 2398377600 19.01.1669 03:14:09.147483647

77 2429935200 19.01.1669 03:14:09.147483647

78 2461492800 19.01.1669 03:14:09.147483647

79 2493050400 19.01.1669 03:14:09.147483647

80 2524608000 19.01.1669 03:14:09.147483647

81 2556165600 19.01.1669 03:14:09.147483647

82 2587723200 19.01.1669 03:14:09.147483647

83 2619280800 19.01.1669 03:14:09.147483647

84 2650838400 19.01.1669 03:14:09.147483647

85 2682396000 19.01.1669 03:14:09.147483647

86 2713953600 19.01.1669 03:14:09.147483647

87 2745511200 19.01.1669 03:14:09.147483647

88 2777068800 19.01.1669 03:14:09.147483647

89 2808626400 19.01.1669 03:14:09.147483647

90 2840184000 19.01.1669 03:14:09.147483647

91 2871741600 19.01.1669 03:14:09.147483647

92 2903299200 19.01.1669 03:14:09.147483647

93 2934856800 19.01.1669 03:14:09.147483647

94 2966414400 19.01.1669 03:14:09.147483647

95 2997972000 19.01.1669 03:14:09.147483647

96 3029529600 19.01.1669 03:14:09.147483647

97 3061087200 19.01.1669 03:14:09.147483647

98 3092644800 19.01.1669 03:14:09.147483647

99 3124202400 19.01.1669 03:14:09.147483647

Looks like when an interval DAY TO SECOND is given in seconds, then it must not bigger than around 68 years, otherwise the calculation is wrong.

Is this a know (i.e. documented) limitation or is it a bug in Oracle?

Kind Regards

Wernfried

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 20 2015

Added on Feb 20 2015

9 comments

2,080 views