vibesql_types/temporal/
interval.rs

1//! SQL INTERVAL type implementation
2//!
3//! Implements SQL:1999 interval semantics following PostgreSQL's approach:
4//! - Intervals are internally stored as (months, days, microseconds)
5//! - Comparison uses linear representation with approximations (1 month = 30 days)
6//! - Supports year-month intervals and day-time intervals
7
8use std::{cmp::Ordering, fmt, str::FromStr};
9
10/// SQL INTERVAL type - represents a duration
11///
12/// Formats vary: '5 YEAR', '1-6 YEAR TO MONTH', '5 12:30:45 DAY TO SECOND', etc.
13///
14/// Internal representation follows PostgreSQL:
15/// - months: Total months (years * 12 + months)
16/// - days: Total days
17/// - microseconds: Total time in microseconds (hours, minutes, seconds, fractions)
18///
19/// Note: Equality and ordering are based on the internal representation (months, days,
20/// microseconds), not the string value. This means "1 YEAR" equals "12 MONTH" even though the
21/// strings differ.
22#[derive(Debug, Clone)]
23pub struct Interval {
24    /// The original interval string (e.g., "5 YEAR", "1-6 YEAR TO MONTH")
25    pub value: String,
26    /// Total months (used for comparison)
27    months: i32,
28    /// Total days (used for comparison)
29    days: i32,
30    /// Total microseconds (used for comparison)
31    microseconds: i64,
32}
33
34impl Interval {
35    /// Create a new Interval
36    pub fn new(value: String) -> Self {
37        // Parse the interval string to extract internal representation
38        let (months, days, microseconds) = Self::parse_interval(&value);
39        Interval { value, months, days, microseconds }
40    }
41
42    /// Parse an interval string into (months, days, microseconds)
43    ///
44    /// This is a simplified parser that handles common interval formats.
45    /// For more complex parsing, this should be enhanced.
46    fn parse_interval(s: &str) -> (i32, i32, i64) {
47        let mut months = 0;
48        let mut days = 0;
49        let mut microseconds = 0i64;
50
51        // Split into value and unit parts
52        // Format examples: "5 YEAR", "1-6 YEAR TO MONTH", "30 DAY", "12:30:45 HOUR TO SECOND"
53        let parts: Vec<&str> = s.split_whitespace().collect();
54
55        if parts.is_empty() {
56            return (0, 0, 0);
57        }
58
59        // Check if it's a compound interval (contains "TO")
60        if let Some(to_pos) = parts.iter().position(|&p| p.eq_ignore_ascii_case("TO")) {
61            // Compound interval like "1-6 YEAR TO MONTH" or "5 12:30:45 DAY TO SECOND"
62            if to_pos >= 2 {
63                let value_part = parts[0];
64                let from_unit = parts[to_pos - 1];
65                let to_unit = parts[to_pos + 1];
66
67                // Handle YEAR TO MONTH (format: "Y-M" or "Y")
68                if from_unit.eq_ignore_ascii_case("YEAR") && to_unit.eq_ignore_ascii_case("MONTH") {
69                    if let Some(dash_pos) = value_part.find('-') {
70                        let years: i32 = value_part[..dash_pos].parse().unwrap_or(0);
71                        let month_part: i32 = value_part[dash_pos + 1..].parse().unwrap_or(0);
72                        months = years * 12 + month_part;
73                    } else {
74                        let years: i32 = value_part.parse().unwrap_or(0);
75                        months = years * 12;
76                    }
77                }
78                // Handle DAY TO HOUR, DAY TO SECOND, etc.
79                else if from_unit.eq_ignore_ascii_case("DAY") {
80                    if let Some(space_pos) = value_part.find(' ') {
81                        days = value_part[..space_pos].parse().unwrap_or(0);
82                        // Parse time component
83                        let time_part = value_part[space_pos + 1..].trim();
84                        microseconds = Self::parse_time_to_microseconds(time_part);
85                    } else {
86                        days = value_part.parse().unwrap_or(0);
87                    }
88                }
89                // Handle HOUR TO SECOND, etc.
90                else if from_unit.eq_ignore_ascii_case("HOUR")
91                    || from_unit.eq_ignore_ascii_case("MINUTE")
92                    || from_unit.eq_ignore_ascii_case("SECOND")
93                {
94                    microseconds = Self::parse_time_to_microseconds(value_part);
95                }
96            }
97        } else {
98            // Simple interval like "5 YEAR", "30 DAY", "24 HOUR"
99            if parts.len() >= 2 {
100                let value_part = parts[0];
101                let unit = parts[1];
102
103                match unit.to_uppercase().as_str() {
104                    "YEAR" | "YEARS" => {
105                        let years: i32 = value_part.parse().unwrap_or(0);
106                        months = years * 12;
107                    }
108                    "MONTH" | "MONTHS" => {
109                        months = value_part.parse().unwrap_or(0);
110                    }
111                    "DAY" | "DAYS" => {
112                        days = value_part.parse().unwrap_or(0);
113                    }
114                    "HOUR" | "HOURS" => {
115                        let hours: i64 = value_part.parse().unwrap_or(0);
116                        microseconds = hours * 3600 * 1_000_000;
117                    }
118                    "MINUTE" | "MINUTES" => {
119                        let minutes: i64 = value_part.parse().unwrap_or(0);
120                        microseconds = minutes * 60 * 1_000_000;
121                    }
122                    "SECOND" | "SECONDS" => {
123                        microseconds = Self::parse_seconds_to_microseconds(value_part);
124                    }
125                    _ => {}
126                }
127            }
128        }
129
130        (months, days, microseconds)
131    }
132
133    /// Parse a time string (HH:MM:SS or HH:MM:SS.ffffff) to microseconds
134    fn parse_time_to_microseconds(s: &str) -> i64 {
135        let parts: Vec<&str> = s.split(':').collect();
136        let mut total_microseconds = 0i64;
137
138        if !parts.is_empty() {
139            // Hours
140            if let Ok(hours) = parts[0].parse::<i64>() {
141                total_microseconds += hours * 3600 * 1_000_000;
142            }
143        }
144
145        if parts.len() > 1 {
146            // Minutes
147            if let Ok(minutes) = parts[1].parse::<i64>() {
148                total_microseconds += minutes * 60 * 1_000_000;
149            }
150        }
151
152        if parts.len() > 2 {
153            // Seconds (may have fractional part)
154            total_microseconds += Self::parse_seconds_to_microseconds(parts[2]);
155        }
156
157        total_microseconds
158    }
159
160    /// Parse seconds string (possibly with fractional part) to microseconds
161    fn parse_seconds_to_microseconds(s: &str) -> i64 {
162        if let Some(dot_pos) = s.find('.') {
163            let whole: i64 = s[..dot_pos].parse().unwrap_or(0);
164            let frac_str = &s[dot_pos + 1..];
165            // Pad or truncate to 6 digits for microseconds
166            let frac_str_padded = format!("{:0<6}", frac_str);
167            let frac: i64 = frac_str_padded[..6].parse().unwrap_or(0);
168            whole * 1_000_000 + frac
169        } else {
170            s.parse::<i64>().unwrap_or(0) * 1_000_000
171        }
172    }
173
174    /// Compute a linear comparison value for this interval
175    ///
176    /// Following PostgreSQL's approach:
177    /// - Convert months to days (1 month = 30 days approximation)
178    /// - Add days
179    /// - Convert to microseconds (1 day = 24 hours = 86400 seconds)
180    /// - Add interval's microseconds
181    ///
182    /// Note: This uses approximations and may not be accurate for all intervals,
183    /// particularly when comparing year-month intervals with day-time intervals.
184    fn cmp_value(&self) -> i128 {
185        // Convert months to days (approximation: 1 month = 30 days)
186        let total_days = (self.months as i64) * 30 + (self.days as i64);
187
188        // Convert days to microseconds (1 day = 86400 seconds = 86400000000 microseconds)
189        let days_in_microseconds = total_days as i128 * 86_400_000_000i128;
190
191        // Add the time component
192        days_in_microseconds + (self.microseconds as i128)
193    }
194}
195
196impl FromStr for Interval {
197    type Err = String;
198
199    fn from_str(s: &str) -> Result<Self, Self::Err> {
200        // For now, just store the string representation
201        // Can be enhanced later to parse and validate interval format
202        Ok(Interval::new(s.to_string()))
203    }
204}
205
206impl fmt::Display for Interval {
207    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
208        write!(f, "{}", self.value)
209    }
210}
211
212impl PartialOrd for Interval {
213    fn partial_cmp(&self, other: &Self) -> Option<Ordering> {
214        Some(self.cmp(other))
215    }
216}
217
218impl Ord for Interval {
219    fn cmp(&self, other: &Self) -> Ordering {
220        self.cmp_value().cmp(&other.cmp_value())
221    }
222}
223
224impl PartialEq for Interval {
225    fn eq(&self, other: &Self) -> bool {
226        // Compare based on internal representation, not string value
227        // This allows "1 YEAR" to equal "12 MONTH"
228        self.months == other.months
229            && self.days == other.days
230            && self.microseconds == other.microseconds
231    }
232}
233
234impl Eq for Interval {}
235
236impl std::hash::Hash for Interval {
237    fn hash<H: std::hash::Hasher>(&self, state: &mut H) {
238        // Only hash the fields used in equality comparison
239        // This ensures that if a == b, then hash(a) == hash(b)
240        self.months.hash(state);
241        self.days.hash(state);
242        self.microseconds.hash(state);
243    }
244}
245
246#[cfg(test)]
247mod tests {
248    use super::*;
249
250    #[test]
251    fn test_interval_comparison_simple_years() {
252        let i1 = Interval::new("1 YEAR".to_string());
253        let i2 = Interval::new("2 YEAR".to_string());
254        assert!(i1 < i2, "1 YEAR should be less than 2 YEAR");
255    }
256
257    #[test]
258    fn test_interval_comparison_simple_months() {
259        let i1 = Interval::new("1 MONTH".to_string());
260        let i2 = Interval::new("6 MONTH".to_string());
261        assert!(i1 < i2, "1 MONTH should be less than 6 MONTH");
262    }
263
264    #[test]
265    fn test_interval_comparison_simple_days() {
266        let i1 = Interval::new("1 DAY".to_string());
267        let i2 = Interval::new("30 DAY".to_string());
268        assert!(i1 < i2, "1 DAY should be less than 30 DAY");
269    }
270
271    #[test]
272    fn test_interval_comparison_month_vs_days() {
273        // 1 month is approximated as 30 days in comparison (ordering)
274        // Note: They are NOT equal (different internal representation)
275        // but they have the same comparison value
276        let i1 = Interval::new("1 MONTH".to_string());
277        let i2 = Interval::new("30 DAY".to_string());
278        assert_ne!(i1, i2, "1 MONTH and 30 DAY have different representations");
279        assert_eq!(
280            i1.cmp(&i2),
281            Ordering::Equal,
282            "1 MONTH should compare equal to 30 DAY (approximation)"
283        );
284    }
285
286    #[test]
287    fn test_interval_comparison_year_vs_months() {
288        // 1 year = 12 months
289        let i1 = Interval::new("1 YEAR".to_string());
290        let i2 = Interval::new("12 MONTH".to_string());
291        assert_eq!(i1, i2, "1 YEAR should equal 12 MONTH");
292    }
293
294    #[test]
295    fn test_interval_comparison_year_vs_days() {
296        // 1 year = 12 months = 360 days (approximation for ordering)
297        // Note: They are NOT equal (different internal representation)
298        // but they have the same comparison value
299        let i1 = Interval::new("1 YEAR".to_string());
300        let i2 = Interval::new("360 DAY".to_string());
301        assert_ne!(i1, i2, "1 YEAR and 360 DAY have different representations");
302        assert_eq!(
303            i1.cmp(&i2),
304            Ordering::Equal,
305            "1 YEAR should compare equal to 360 DAY (approximation)"
306        );
307    }
308
309    #[test]
310    fn test_interval_comparison_hours() {
311        let i1 = Interval::new("1 HOUR".to_string());
312        let i2 = Interval::new("2 HOUR".to_string());
313        assert!(i1 < i2, "1 HOUR should be less than 2 HOUR");
314    }
315
316    #[test]
317    fn test_interval_comparison_minutes() {
318        let i1 = Interval::new("30 MINUTE".to_string());
319        let i2 = Interval::new("90 MINUTE".to_string());
320        assert!(i1 < i2, "30 MINUTE should be less than 90 MINUTE");
321    }
322
323    #[test]
324    fn test_interval_comparison_seconds() {
325        let i1 = Interval::new("45 SECOND".to_string());
326        let i2 = Interval::new("90 SECOND".to_string());
327        assert!(i1 < i2, "45 SECOND should be less than 90 SECOND");
328    }
329
330    #[test]
331    fn test_interval_comparison_year_to_month() {
332        // "1-6 YEAR TO MONTH" = 1 year + 6 months = 18 months
333        let i1 = Interval::new("1-6 YEAR TO MONTH".to_string());
334        let i2 = Interval::new("18 MONTH".to_string());
335        assert_eq!(i1, i2, "1-6 YEAR TO MONTH should equal 18 MONTH");
336    }
337
338    #[test]
339    fn test_interval_comparison_year_to_month_ordering() {
340        let i1 = Interval::new("1-0 YEAR TO MONTH".to_string());
341        let i2 = Interval::new("1-6 YEAR TO MONTH".to_string());
342        let i3 = Interval::new("2-0 YEAR TO MONTH".to_string());
343        assert!(i1 < i2, "1-0 should be less than 1-6");
344        assert!(i2 < i3, "1-6 should be less than 2-0");
345    }
346
347    #[test]
348    fn test_interval_comparison_cross_type_month_vs_day() {
349        // 1 month (approximated as 30 days) < 31 days
350        let i1 = Interval::new("1 MONTH".to_string());
351        let i2 = Interval::new("31 DAY".to_string());
352        assert!(i1 < i2, "1 MONTH should be less than 31 DAY");
353    }
354
355    #[test]
356    fn test_interval_comparison_cross_type_month_vs_day_greater() {
357        // 1 month (approximated as 30 days) > 29 days
358        let i1 = Interval::new("1 MONTH".to_string());
359        let i2 = Interval::new("29 DAY".to_string());
360        assert!(i1 > i2, "1 MONTH should be greater than 29 DAY");
361    }
362
363    #[test]
364    fn test_interval_comparison_equality() {
365        let i1 = Interval::new("5 YEAR".to_string());
366        let i2 = Interval::new("5 YEAR".to_string());
367        assert_eq!(i1, i2, "Same intervals should be equal");
368    }
369
370    #[test]
371    fn test_interval_parsing_zero() {
372        let i = Interval::new("0 DAY".to_string());
373        assert_eq!(i.days, 0);
374        assert_eq!(i.months, 0);
375        assert_eq!(i.microseconds, 0);
376    }
377
378    #[test]
379    fn test_interval_ordering_total() {
380        let mut intervals = [
381            Interval::new("2 YEAR".to_string()),
382            Interval::new("1 MONTH".to_string()),
383            Interval::new("1 DAY".to_string()),
384            Interval::new("1 YEAR".to_string()),
385            Interval::new("30 DAY".to_string()),
386        ];
387
388        intervals.sort();
389
390        // Expected order: 1 DAY < 1 MONTH == 30 DAY < 1 YEAR < 2 YEAR
391        assert_eq!(intervals[0].value, "1 DAY");
392        // 1 MONTH and 30 DAY are equivalent, either could be at index 1 or 2
393        assert!(
394            (intervals[1].value == "1 MONTH" && intervals[2].value == "30 DAY")
395                || (intervals[1].value == "30 DAY" && intervals[2].value == "1 MONTH")
396        );
397        assert_eq!(intervals[3].value, "1 YEAR");
398        assert_eq!(intervals[4].value, "2 YEAR");
399    }
400
401    #[test]
402    fn test_interval_cmp_value_calculation() {
403        // Test internal cmp_value calculation
404        let i1 = Interval::new("1 YEAR".to_string());
405        // 1 year = 12 months = 360 days = 360 * 86400000000 microseconds
406        let expected = 360i128 * 86_400_000_000i128;
407        assert_eq!(i1.cmp_value(), expected);
408
409        let i2 = Interval::new("1 DAY".to_string());
410        // 1 day = 86400000000 microseconds
411        let expected = 86_400_000_000i128;
412        assert_eq!(i2.cmp_value(), expected);
413
414        let i3 = Interval::new("1 HOUR".to_string());
415        // 1 hour = 3600 seconds = 3600000000 microseconds
416        let expected = 3_600_000_000i128;
417        assert_eq!(i3.cmp_value(), expected);
418    }
419
420    #[test]
421    fn test_interval_from_str() {
422        let i: Interval = "5 YEAR".parse().unwrap();
423        assert_eq!(i.value, "5 YEAR");
424        assert_eq!(i.months, 60); // 5 * 12
425    }
426
427    #[test]
428    fn test_interval_display() {
429        let i = Interval::new("5 YEAR".to_string());
430        assert_eq!(format!("{}", i), "5 YEAR");
431    }
432
433    #[test]
434    fn test_interval_parsing_fractional_seconds() {
435        let i = Interval::new("1.5 SECOND".to_string());
436        assert_eq!(i.microseconds, 1_500_000); // 1.5 seconds = 1500000 microseconds
437    }
438
439    #[test]
440    fn test_interval_comparison_with_fractional_seconds() {
441        let i1 = Interval::new("1.5 SECOND".to_string());
442        let i2 = Interval::new("2 SECOND".to_string());
443        assert!(i1 < i2, "1.5 SECOND should be less than 2 SECOND");
444    }
445
446    #[test]
447    fn test_interval_hash_consistency() {
448        use std::{
449            collections::hash_map::DefaultHasher,
450            hash::{Hash, Hasher},
451        };
452
453        // Helper function to compute hash
454        fn calculate_hash<T: Hash>(t: &T) -> u64 {
455            let mut s = DefaultHasher::new();
456            t.hash(&mut s);
457            s.finish()
458        }
459
460        // Test that equal intervals have equal hashes
461        let i1 = Interval::new("1 YEAR".to_string());
462        let i2 = Interval::new("12 MONTH".to_string());
463
464        // These should be equal
465        assert_eq!(i1, i2, "1 YEAR should equal 12 MONTH");
466
467        // And they should have the same hash
468        assert_eq!(
469            calculate_hash(&i1),
470            calculate_hash(&i2),
471            "Equal intervals must have equal hash values"
472        );
473
474        // Test another case: year to month
475        let i3 = Interval::new("1-6 YEAR TO MONTH".to_string());
476        let i4 = Interval::new("18 MONTH".to_string());
477
478        assert_eq!(i3, i4, "1-6 YEAR TO MONTH should equal 18 MONTH");
479        assert_eq!(
480            calculate_hash(&i3),
481            calculate_hash(&i4),
482            "Equal intervals must have equal hash values"
483        );
484
485        // Test that different intervals have different hashes (usually)
486        let i5 = Interval::new("1 YEAR".to_string());
487        let i6 = Interval::new("2 YEAR".to_string());
488
489        assert_ne!(i5, i6, "1 YEAR should not equal 2 YEAR");
490        // Note: We can't guarantee different hashes for different values,
491        // but we can verify the Hash/Eq invariant holds
492    }
493}
vibesql_types/temporal/interval.rs

vibesql_types/temporal/
interval.rs
