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
- Interval Literals: http://docs.oracle.com/database/121/SQLRF/sql_elements003.htm#SQLRF00221
- NUMTODSINTERVAL: http://docs.oracle.com/database/121/SQLRF/functions129.htm#SQLRF00682
- TO_DSINTERVAL: http://docs.oracle.com/database/121/SQLRF/functions220.htm#SQLRF06133
- EXTRACT(datetime): http://docs.oracle.com/database/121/SQLRF/functions067.htm#SQLRF00639
- TO_CHAR(interval|datetime): http://docs.oracle.com/database/121/SQLRF/functions216.htm#SQLRF06129
- Format models: http://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00211