SAND CDBMS SQL Reference Guide
Date/Time Arithmetic

 

x
Chapter Index
Next Topic:
Labeled Durations

 

Intervals

Subtracting one temporal type from another of the same type results in an interval. There are three types of intervals: date, time, and timestamp. A date interval is characterized by an integral number of days, ranging between -99,999,999 and 99,999,999 inclusive. A time interval is characterized by an integral number of seconds, ranging between -999,999 and 999,999 inclusive. And a timestamp interval is represented by a decimal number of seconds, anywhere between +/-99,999,999,999,999.999999 inclusive. Note that a negative interval is produced if the value being subtracted is greater than the value from which it is being subtracted. "Greater than", in the case of temporal intervals, means "more recent".

Intervals can be involved in arithmetic expressions involving other intervals or labeled durations, date/time data types, or numeric expressions. If required, a date/time interval can be converted to an integer value using the INTEGER casting function.

The characteristics of temporal intervals are summarized in the following table:

Operation Interval Type Size Unit
<DATE> – <DATE> date interval DEC (8,0) days
<TIME> – <TIME> time interval DEC (6,0) seconds
<TIMESTAMP> – <TIMESTAMP> timestamp interval DEC (20,6) seconds


Examples

Expression Result
'01.01.1954' – '01.01.1953'
365 (days)
'1602-12-31' – '2001-01-01'
-145368 (days)
'00:00:00' – '10:10:10'
-36610 (seconds)
'23.59.59' – '22.59.00'
3659 (seconds)
'1954-01-01-00.00.00' – '1953-01-01-00.00.00'
31536000.000000 (seconds)
'2003-12-16-09.51.16.073000' – '2003-12-19-09.00.00.000000'
-256123.927000 (seconds)