Datetime operands and durations
Datetime values can be incremented, decremented, and subtracted. These operations may involve decimal numbers called durations. A duration is a positive or negative number representing an interval of time.
There are four types of durations:
- Labeled durations
- The form a labeled duration is as follows:
labeled-duration:
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: YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS, or MICROSECONDS1. 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 digits of fractional seconds to be included. The MICROSECONDS duration returns 6 digits of fractional seconds.
A labeled duration can only be used as an operand of an arithmetic operator in which the other operand is a value of data type 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 - BRTHDATE, 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 digits of fractional seconds ranging from 0 to 12. To be properly interpreted, the number must have the format yyyymmddhhmmss.zzzzzzzzzzzz, where yyyy, mm, 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 scale that matches the maximum timestamp precision of the timestamp operands.
