uqa_sql/expr/time/
interval.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! Interval arithmetic as `PostgreSQL` 18's `timestamp.c` performs it on finite intervals: negation (`interval_um_internal`), field-wise addition and subtraction (`finite_interval_pl`, `finite_interval_mi`), and scaling by a double precision factor (`interval_mul`, `interval_div`). Every function reports overflow as `interval out of range` (SQLSTATE 22008), including a result that lands on the field values reserved for `-infinity` and `infinity`.
8
9use uqa_core::TemporalValue;
10
11use super::super::{datetime_out_of_range, division_by_zero};
12use crate::error::Result;
13
14/// The months, days and microseconds of an interval.
15#[derive(Clone, Copy, Debug, PartialEq, Eq)]
16pub struct IntervalFields {
17    pub months: i32,
18    pub days: i32,
19    pub micros: i64,
20}
21
22const DAYS_PER_MONTH_INT: i32 = 30;
23const DAYS_PER_MONTH: f64 = 30.0;
24const SECS_PER_DAY: f64 = 86_400.0;
25const USECS_PER_SEC: f64 = 1_000_000.0;
26const USECS_PER_DAY: i64 = 86_400_000_000;
27
28impl IntervalFields {
29    pub const ZERO: Self = Self {
30        months: 0,
31        days: 0,
32        micros: 0,
33    };
34
35    /// The fields of an interval value, or `None` for any other temporal value.
36    pub fn of(value: &TemporalValue) -> Option<Self> {
37        match *value {
38            TemporalValue::Interval {
39                months,
40                days,
41                micros,
42            } => Some(Self {
43                months,
44                days,
45                micros,
46            }),
47            _ => None,
48        }
49    }
50
51    pub fn value(self) -> TemporalValue {
52        TemporalValue::Interval {
53            months: self.months,
54            days: self.days,
55            micros: self.micros,
56        }
57    }
58
59    /// `INTERVAL_NOT_FINITE`: the field values `interval '-infinity'` and `interval 'infinity'` are stored as.
60    fn is_reserved(self) -> bool {
61        (self.months == i32::MIN && self.days == i32::MIN && self.micros == i64::MIN)
62            || (self.months == i32::MAX && self.days == i32::MAX && self.micros == i64::MAX)
63    }
64
65    fn finite(self) -> Result<Self> {
66        if self.is_reserved() {
67            Err(datetime_out_of_range("interval"))
68        } else {
69            Ok(self)
70        }
71    }
72
73    /// `interval_um_internal`.
74    pub fn negate(self) -> Result<Self> {
75        match (
76            self.months.checked_neg(),
77            self.days.checked_neg(),
78            self.micros.checked_neg(),
79        ) {
80            (Some(months), Some(days), Some(micros)) => Self {
81                months,
82                days,
83                micros,
84            }
85            .finite(),
86            _ => Err(datetime_out_of_range("interval")),
87        }
88    }
89
90    /// `finite_interval_pl`.
91    pub fn plus(self, other: Self) -> Result<Self> {
92        match (
93            self.months.checked_add(other.months),
94            self.days.checked_add(other.days),
95            self.micros.checked_add(other.micros),
96        ) {
97            (Some(months), Some(days), Some(micros)) => Self {
98                months,
99                days,
100                micros,
101            }
102            .finite(),
103            _ => Err(datetime_out_of_range("interval")),
104        }
105    }
106
107    /// `finite_interval_mi`.
108    pub fn minus(self, other: Self) -> Result<Self> {
109        match (
110            self.months.checked_sub(other.months),
111            self.days.checked_sub(other.days),
112            self.micros.checked_sub(other.micros),
113        ) {
114            (Some(months), Some(days), Some(micros)) => Self {
115                months,
116                days,
117                micros,
118            }
119            .finite(),
120            _ => Err(datetime_out_of_range("interval")),
121        }
122    }
123
124    /// `interval_mul` of a finite interval.
125    pub fn multiply(self, factor: f64) -> Result<Self> {
126        if factor.is_nan() || factor.is_infinite() {
127            return Err(datetime_out_of_range("interval"));
128        }
129        self.scale(|field| field * factor)
130    }
131
132    /// `interval_div` of a finite interval.
133    pub fn divide(self, factor: f64) -> Result<Self> {
134        if factor == 0.0 {
135            return Err(division_by_zero());
136        }
137        if factor.is_nan() {
138            return Err(datetime_out_of_range("interval"));
139        }
140        self.scale(|field| field / factor)
141    }
142
143    /// `interval_justify_hours`: whole days of the time field move into the day field, then a day and a time of opposite signs trade one day so that both take one sign.
144    pub fn justify_hours(self) -> Result<Self> {
145        let whole_days = self.micros / USECS_PER_DAY;
146        let days = self
147            .days
148            .checked_add(whole_days as i32)
149            .ok_or_else(|| datetime_out_of_range("interval"))?;
150        let (days, micros) = align_time_with_days(days, self.micros - whole_days * USECS_PER_DAY);
151        Ok(Self {
152            months: self.months,
153            days,
154            micros,
155        })
156    }
157
158    /// `interval_justify_days`: whole 30-day months of the day field move into the month field, then a month and a day of opposite signs trade one month.
159    pub fn justify_days(self) -> Result<Self> {
160        let (months, days) = carry_whole_months(self.months, self.days)?;
161        let (months, days) = if months > 0 && days < 0 {
162            (months - 1, days + DAYS_PER_MONTH_INT)
163        } else if months < 0 && days > 0 {
164            (months + 1, days - DAYS_PER_MONTH_INT)
165        } else {
166            (months, days)
167        };
168        Ok(Self {
169            months,
170            days,
171            micros: self.micros,
172        })
173    }
174
175    /// `interval_justify_interval`: `justify_hours` and `justify_days` together, so that all three fields take one sign. Days are carried into months first when day and time share a sign, which keeps the day field from overflowing.
176    pub fn justify_interval(self) -> Result<Self> {
177        let (mut months, mut days) = (self.months, self.days);
178        if (days > 0 && self.micros > 0) || (days < 0 && self.micros < 0) {
179            (months, days) = carry_whole_months(months, days)?;
180        }
181        let whole_days = self.micros / USECS_PER_DAY;
182        let mut micros = self.micros - whole_days * USECS_PER_DAY;
183        // Either the pre-carry left fewer than 30 days or day and time differ in sign, so this cannot overflow.
184        days = i32::try_from(i64::from(days) + whole_days)
185            .map_err(|_| datetime_out_of_range("interval"))?;
186        (months, days) = carry_whole_months(months, days)?;
187        if months > 0 && (days < 0 || (days == 0 && micros < 0)) {
188            days += DAYS_PER_MONTH_INT;
189            months -= 1;
190        } else if months < 0 && (days > 0 || (days == 0 && micros > 0)) {
191            days -= DAYS_PER_MONTH_INT;
192            months += 1;
193        }
194        (days, micros) = align_time_with_days(days, micros);
195        Ok(Self {
196            months,
197            days,
198            micros,
199        })
200    }
201
202    /// The body `interval_mul` and `interval_div` share: each field is scaled on its own and truncated, the fractional month cascades into days at 30 days a month and the fractional day into seconds at 86400 seconds a day, and nothing cascades upward. `TSROUND` keeps the cascaded fractions from drifting off whole microseconds.
203    fn scale(self, scale: impl Fn(f64) -> f64) -> Result<Self> {
204        let months = truncate_to_i32(scale(f64::from(self.months)))?;
205        let days = truncate_to_i32(scale(f64::from(self.days)))?;
206        let month_remainder_days =
207            timestamp_round((scale(f64::from(self.months)) - f64::from(months)) * DAYS_PER_MONTH);
208        let mut sec_remainder = timestamp_round(
209            (scale(f64::from(self.days)) - f64::from(days) + month_remainder_days
210                - f64::from(month_remainder_days as i32))
211                * SECS_PER_DAY,
212        );
213        let mut days = days;
214        // Rounding can leave a whole day of seconds, and the cascades together can exceed one.
215        if sec_remainder.abs() >= SECS_PER_DAY {
216            let whole_days = (sec_remainder / SECS_PER_DAY) as i32;
217            days = days
218                .checked_add(whole_days)
219                .ok_or_else(|| datetime_out_of_range("interval"))?;
220            sec_remainder -= f64::from(whole_days) * SECS_PER_DAY;
221        }
222        let days = days
223            .checked_add(month_remainder_days as i32)
224            .ok_or_else(|| datetime_out_of_range("interval"))?;
225        let micros = (scale(self.micros as f64) + sec_remainder * USECS_PER_SEC).round_ties_even();
226        if micros.is_nan() || !(micros >= i64::MIN as f64 && micros < -(i64::MIN as f64)) {
227            return Err(datetime_out_of_range("interval"));
228        }
229        Self {
230            months,
231            days,
232            micros: micros as i64,
233        }
234        .finite()
235    }
236}
237
238/// Move the whole 30-day months of `days` into `months`, the day count's sign kept as C's truncating division keeps it.
239fn carry_whole_months(months: i32, days: i32) -> Result<(i32, i32)> {
240    let whole_months = days / DAYS_PER_MONTH_INT;
241    let months = months
242        .checked_add(whole_months)
243        .ok_or_else(|| datetime_out_of_range("interval"))?;
244    Ok((months, days - whole_months * DAYS_PER_MONTH_INT))
245}
246
247/// Trade one day between a day count and a time of opposite signs, as the `justify` functions finish.
248fn align_time_with_days(days: i32, micros: i64) -> (i32, i64) {
249    if days > 0 && micros < 0 {
250        (days - 1, micros + USECS_PER_DAY)
251    } else if days < 0 && micros > 0 {
252        (days + 1, micros - USECS_PER_DAY)
253    } else {
254        (days, micros)
255    }
256}
257
258/// `FLOAT8_FITS_IN_INT32` followed by C's truncating conversion.
259fn truncate_to_i32(value: f64) -> Result<i32> {
260    if value.is_nan() || !(value >= f64::from(i32::MIN) && value < -f64::from(i32::MIN)) {
261        return Err(datetime_out_of_range("interval"));
262    }
263    Ok(value as i32)
264}
265
266/// `TSROUND`: round to the microsecond precision of a timestamp's seconds, halves to even as `rint` rounds.
267fn timestamp_round(value: f64) -> f64 {
268    (value * USECS_PER_SEC).round_ties_even() / USECS_PER_SEC
269}
270
271#[cfg(test)]
272mod tests;
uqa_sql/expr/time/interval.rs

uqa_sql/expr/time/
interval.rs
