Every second counts!

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

Documentation links

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s