# About DAY TO SECOND INTERVAL arithmetic

This is a short collection of 30 SQL examples that deal with intervals, specifically the DAY TO SECOND interval. In some cases the result differs from what one suspects.

I’ve hidden the results, so you can read it like a quiz. First think about the expected result, then see if it matches your expectation.

There is no special explanation given. If you want more details check the documentation links at the end of the article or post a comment.

tested on a 11.2.0.4 database (using German date language settings)

## Creating an interval

Example 1

`select interval '90' second from dual;`
Click to see result

+00 00:01:30.000000

Example 2

`select interval '90' second(2) from dual;`
Click to see result

+00 00:01:30.000000

Example 3

`select interval '90' second(1) from dual;`
Click to see result

+00 00:01:30.000000

Example 4

`select interval '90' day(1) from dual;`
Click to see result

ORA-01873: the leading precision of the interval is too small
*Cause: The leading precision of the interval is too small to store the
specified interval.
*Action: Increase the leading precision of the interval or specify an
interval with a smaller leading precision.

Example 5

`select interval '1:30' minute to second from dual;`
Click to see result

+00 00:01:30.000000

Example 6

`select interval '0:90' minute to second from dual;`
Click to see result

ORA-01852: seconds must be between 0 and 59

Example 7

`select numtodsinterval(90, 'second') from dual;`
Click to see result

+00 00:01:30.000000

Example 8

`select numtodsinterval(1.5, 'minute') from dual;`
Click to see result

+00 00:01:30.000000

Example 9

`select to_dsinterval('90') from dual;`
Click to see result

ORA-01867: the interval is invalid
*Cause: The character string you specified is not a valid interval.
*Action: Please specify a valid interval.

Example 10

`select to_dsinterval('+0 00:01:30') from dual;`
Click to see result

+00 00:01:30.000000

Example 11

`select to_dsinterval('PT0H1M30S') from dual;`
Click to see result

+00 00:01:30.000000

Example 12

`select to_dsinterval('PT90S') from dual;`
Click to see result

+00 00:01:30.000000

## Interval-like data

Example 13

`select to_timestamp('30','SS') from dual;`
Click to see result

01.01.16 00:00:30,000000000

Example 14

`select to_timestamp('90','SS') from dual;`
Click to see result

ORA-01852: seconds must be between 0 and 59

Example 15

`select to_timestamp('90','SSSSS') from dual;`
Click to see result

01.01.16 00:01:30,000000000

Example 16

`select to_timestamp('1:30','MI:SS') from dual;`
Click to see result

01.01.16 00:01:30,000000000

## Using and printing intervals

Example 17

`select extract(second from interval '90' second) from dual;`
Click to see result

30

Example 18

`select extract(second from systimestamp) from dual;`
Click to see result

57,131241

Example 19

`select extract(second from sysdate) from dual;`
Click to see result

ORA-30076: invalid extract field for extract source
*Cause: The extract source does not contain the specified extract field.
*Action:

Example 20

`select extract(day from sysdate) from dual;`
Click to see result

20

Example 21

`select extract(second from numtodsinterval(90, 'second')) from dual;`
Click to see result

30

Example 22

`select extract(second from to_timestamp('1:30','MI:SS')) from dual;`
Click to see result

30

Example 23

`select to_char(interval '90' second,'SS') from dual;`
Click to see result

+00 00:01:30.000000
I think this is a bug!
The formatting seems to be ignored for interval datatypes (Oracle 11.2.0.4)

Example 24

`select to_char(interval '90' second,'SSSSS') from dual;`
Click to see result

+00 00:01:30.000000

Example 25

`select to_char(trunc(sysdate) + interval '90' second,'SS') from dual;`
Click to see result

30

Example 26

`select to_char(trunc(sysdate) + interval '90' second,'SSSSS') from dual;`
Click to see result

00090

Example 27

`select to_char(trunc(sysdate) + interval '90' second,'FMSSSSS') from dual;`
Click to see result

90

Example 28

`select sysdate - (sysdate - interval '90' second) from dual;`
Click to see result

0,001041666666666666666666666666666666666667

Example 29

`select sysdate - sysdate + interval '90' second from dual;`
Click to see result

ORA-30081: invalid data type for datetime/interval arithmetic

Example 30

`select numtodsinterval(sysdate - (sysdate - interval '90' second),'DAY') from dual;`
Click to see result

+00 00:01:30.000000

## Quirks

• The precision for seconds seems to be ignored for interval literals (example 2).
• TO_CHAR does not consider the formatting or intervals have no extra formatting options
• EXTRACT uses ANSI date. ANSI date has no time component. That’s why one can extract the day from it but not the seconds (example 19/20)
• extract on a timestamp also returns fractional seconds