Datetime operands and durations

Datetime values can be incremented, decremented, and subtracted. These operations can involve decimal numbers called durations. A duration is a positive or negative number representing an interval of time.

Labeled durations

The form a labeled duration is as follows:

                         (1)                       
>>-+-function-invocation-----+--+-YEAR---------+---------------><
   +-(expression)------------+  +-YEARS--------+   
   +-constant----------------+  +-MONTH--------+   
   +-column-name-------------+  +-MONTHS-------+   
   '-variable----------------'  +-DAY----------+   
                                +-DAYS---------+   
                                +-HOUR---------+   
                                +-HOURS--------+   
                                +-MINUTE-------+   
                                +-MINUTES------+   
                                +-SECOND-------+   
                                +-SECONDS------+   
                                +-MICROSECOND--+   
                                '-MICROSECONDS-'   

Notes:

1. Must be a scalar function.

A labeled duration represents a specific unit of time as expressed by a number (which can be the result of an expression) followed by one of the seven duration keywords.¹ The number specified is converted as if it were assigned to a DECIMAL(15,0) number, except for SECONDS, which uses DECIMAL(27,12) to allow 0 to 12 fractional second digits to be included.

A labeled duration can only be used as an operand of an arithmetic operator in which the other operand is a value of the data type of date, time, or timestamp. Thus, the expression HIREDATE + 2 MONTHS + 14 DAYS is valid, whereas the expression HIREDATE + (2 MONTHS + 14 DAYS) is not. In both of these expressions, the labeled durations are 2 MONTHS and 14 DAYS.

Date duration

A date duration represents a number of years, months, and days expressed as a DECIMAL(8,0) number. To be properly interpreted, the number must have the format yyyymmdd, where yyyy represents the number of years, mm the number of months, and dd the number of days. The result of subtracting one DATE value from another, as in the expression HIREDATE - BIRTHDATE, is a date duration.

Time duration

A time duration represents a number of hours, minutes, and seconds expressed as a DECIMAL(6,0) number. To be properly interpreted, the number must have the format hhmmss, where hh represents the number of hours, mm the number of minutes, and ss the number of seconds. The result of subtracting one TIME value from another is a time duration.

Timestamp duration

A timestamp duration represents a number of years, months, days, hours, minutes, seconds, and fractional seconds expressed as a DECIMAL(14+s,s) number, where s is the number of fractional seconds in the range from 0 to 12. To be interpreted properly, the number must have the format yyyyxxddhhmmss.zzzzzzzzzzzz, where yyyy, xx, dd, hh, mm, ss, and zzzzzzzzzzzz represent, respectively, the number of years, months, days, hours, minutes, seconds, and fractional seconds. The result of subtracting one timestamp value from another is a timestamp duration with a scale that matches the maximum timestamp precision of the timestamp operands.