Multiply an INTERVAL by a numeric factor to scale its duration.
INTERVAL_MULTIPLY(interval, factor)## Overview Multiplies an INTERVAL by a numeric factor and returns the scaled INTERVAL. Each component (months, days, microseconds) is multiplied by the factor and then normalized where the result would otherwise be fractional (for example scaling '1 month' by 0.5 produces '15 days' internally, though engines vary). Use INTERVAL_MULTIPLY for scalable durations like exponential backoff, tier-based SLA windows, and any computation where an interval must be driven by a numeric column. ## Behavior - Returns an INTERVAL. - Returns NULL if either argument is NULL. - Supports INTEGER, DECIMAL, and floating-point factors. - Negative factors produce negated intervals. - Fractional results for month/day components are redistributed into lower-precision components (fractional months become fractional days become fractional seconds). ## Compatibility - Matches the INTERVAL scaling convention. The infix form 'interval * factor' is equivalent in dialects that support operator-level multiplication.
| Name | Type | Description |
|---|---|---|
interval | Specifies the input interval to multiply. | |
factor | Specifies the numeric multiplier. Accepts INTEGER, DECIMAL, and floating-point values. Negative factors produce negative intervals. |
-- Double a 1-hour interval
SELECT INTERVAL_MULTIPLY(INTERVAL '1 hour', 2) AS result;-- Scale by a fractional factor
SELECT INTERVAL_MULTIPLY(INTERVAL '1 day', 1.5) AS result;-- Multi-component interval scaled by 3
SELECT INTERVAL_MULTIPLY(INTERVAL '2 hours 30 minutes', 3) AS result;-- Negative factor produces a negative interval
SELECT INTERVAL_MULTIPLY(INTERVAL '1 week', -1) AS result;-- Retry backoff based on attempt count
SELECT retry_id, INTERVAL_MULTIPLY(INTERVAL '5 seconds', attempt_number) AS backoff
FROM ops.scheduler.retries;-- Compute duration-scaled SLA windows per tier
SELECT tier_name, INTERVAL_MULTIPLY(base_sla_interval, sla_multiplier) AS tier_sla
FROM support.config.sla_tiers;