INTERVAL DAY
INTERVAL DAY represents a duration of time that can be measured in days, hours, minutes, seconds, and fractions of seconds. INTERVAL can specify individual time units (for example, days only), pairs of time units (for example, days and hours), or mapping of units (for example, days to seconds). All INTERVAL DAY expressions are compatible with all other INTERVAL DAY expressions.
Syntax
INTERVAL 'dd hh:mm:ss.ff' DAY TO SECOND
INTERVAL 'dd hh:mm' DAY TO MINUTE
INTERVAL 'dd hh' DAY TO HOUR
INTERVAL 'dd' DAY
INTERVAL 'hh' HOUR
INTERVAL 'mm' MINUTE
INTERVAL 'ss.ff' SECOND
Remarks
• In the format of date and time content:
— A space separates the day value from the hour value.
— A colon separates hour values from minute values, and minute values from seconds values.
— A decimal point separates fractional seconds from seconds.
• For all time units, the default leading precision is 2. For example, the following pairs of expressions are equivalent:
INTERVAL '3' DAY
INTERVAL '3' DAY(2)
INTERVAL '3' MONTH
INTERVAL '3' MONTH(2)
• For all time units, the maximum leading precision is 9 digits. An error is thrown if the number of digits to the left of the decimal point exceeds the leading precision.
• For seconds:
— If only one precision value is specified, it designates fractional precision, which sets the maximum number of decimal places to the right of the decimal point.
— If the fractional precision is exceeded, the extra digits are automatically truncated.
— The default fractional precision for seconds is 6, so the following two expressions are equivalent:
INTERVAL '3' MINUTE(3) TO SECOND
INTERVAL '3' MINUTE(3) to SECOND(6)
— The maximum fractional precision is 9 digits.
— To specify leading precision as well as fractional precision, enclose both in parentheses, separated by a comma:
INTERVAL '3.99' SECOND(2,6)
— Zero (0) is a valid fractional precision. For example, the following expression truncates fractional seconds to whole seconds:
INTERVAL '9:59' minutes to second(0)
• For details on using INTERVAL DAY in arithmetic operations and functions, see: