diesel/pg/expression/extensions/
interval_dsl.rs

1use std::ops::Mul;
2
3use crate::data_types::PgInterval;
4
5/// A DSL added to integers and `f64` to construct PostgreSQL intervals.
6///
7/// The behavior of these methods when called on `NAN` or `Infinity`, or when
8/// overflow occurs is unspecified.
9///
10/// # Examples
11///
12/// ```rust
13/// # include!("../../../doctest_setup.rs");
14/// # use diesel::dsl::*;
15/// #
16/// # table! {
17/// #     users {
18/// #         id -> Serial,
19/// #         name -> VarChar,
20/// #         created_at -> Timestamp,
21/// #     }
22/// # }
23/// #
24/// # fn main() {
25/// #     use self::users::dsl::*;
26/// #     let connection = &mut connection_no_data();
27/// #     diesel::sql_query("CREATE TABLE users (id serial primary key, name
28/// #        varchar not null, created_at timestamp not null)")
29/// #     .execute(connection)
30/// #     .unwrap();
31/// diesel::sql_query(
32///     "INSERT INTO users (name, created_at) VALUES
33///     ('Sean', NOW()), ('Tess', NOW() - '5 minutes'::interval),
34///     ('Jim', NOW() - '10 minutes'::interval)",
35/// )
36/// .execute(connection)
37/// .unwrap();
38///
39/// let mut data: Vec<String> = users
40///     .select(name)
41///     .filter(created_at.gt(now - 7.minutes()))
42///     .load(connection)
43///     .unwrap();
44/// assert_eq!(2, data.len());
45/// assert_eq!("Sean".to_string(), data[0]);
46/// assert_eq!("Tess".to_string(), data[1]);
47/// # }
48/// ```
49///
50/// ```rust
51/// # include!("../../../doctest_setup.rs");
52/// # use diesel::dsl::*;
53/// #
54/// # table! {
55/// #     users {
56/// #         id -> Serial,
57/// #         name -> VarChar,
58/// #         created_at -> Timestamp,
59/// #     }
60/// # }
61/// #
62/// # fn main() {
63/// #     use self::users::dsl::*;
64/// #     let connection = &mut connection_no_data();
65/// #     diesel::sql_query("CREATE TABLE users (id serial primary key, name
66/// #        varchar not null, created_at timestamp not null)")
67/// #     .execute(connection)
68/// #     .unwrap();
69/// diesel::sql_query(
70///     "INSERT INTO users (name, created_at) VALUES
71///     ('Sean', NOW()), ('Tess', NOW() - '5 days'::interval),
72///     ('Jim', NOW() - '10 days'::interval)",
73/// )
74/// .execute(connection)
75/// .unwrap();
76///
77/// let mut data: Vec<String> = users
78///     .select(name)
79///     .filter(created_at.gt(now - 7.days()))
80///     .load(connection)
81///     .unwrap();
82/// assert_eq!(2, data.len());
83/// assert_eq!("Sean".to_string(), data[0]);
84/// assert_eq!("Tess".to_string(), data[1]);
85/// # }
86/// ```
87#[cfg(feature = "postgres_backend")]
88pub trait IntervalDsl: Sized + From<i32> + Mul<Self, Output = Self> {
89    /// Returns a PgInterval representing `self` as microseconds
90    fn microseconds(self) -> PgInterval;
91    /// Returns a PgInterval representing `self` in days
92    fn days(self) -> PgInterval;
93    /// Returns a PgInterval representing `self` in months
94    fn months(self) -> PgInterval;
95
96    /// Returns a PgInterval representing `self` as milliseconds
97    fn milliseconds(self) -> PgInterval {
98        (self * 1000.into()).microseconds()
99    }
100
101    /// Returns a PgInterval representing `self` as seconds
102    fn seconds(self) -> PgInterval {
103        (self * 1000.into()).milliseconds()
104    }
105
106    /// Returns a PgInterval representing `self` as minutes
107    fn minutes(self) -> PgInterval {
108        (self * 60.into()).seconds()
109    }
110
111    /// Returns a PgInterval representing `self` as hours
112    fn hours(self) -> PgInterval {
113        (self * 60.into()).minutes()
114    }
115
116    /// Returns a PgInterval representing `self` in weeks
117    ///
118    /// Note: When called on a high precision float, the returned interval may
119    /// be 1 microsecond different than the equivalent string passed to
120    /// PostgreSQL.
121    fn weeks(self) -> PgInterval {
122        (self * 7.into()).days()
123    }
124
125    /// Returns a PgInterval representing `self` in weeks
126    ///
127    /// Note: When called on a float, this method will mimic the behavior of
128    /// PostgreSQL's interval parsing, and will ignore units smaller than
129    /// months.
130    ///
131    /// ```rust
132    /// # use diesel::dsl::*;
133    /// assert_eq!(1.04.years(), 1.year());
134    /// assert_eq!(1.09.years(), 1.year() + 1.month());
135    /// ```
136    fn years(self) -> PgInterval {
137        (self * 12.into()).months()
138    }
139
140    /// Identical to `microseconds`
141    fn microsecond(self) -> PgInterval {
142        self.microseconds()
143    }
144
145    /// Identical to `milliseconds`
146    fn millisecond(self) -> PgInterval {
147        self.milliseconds()
148    }
149
150    /// Identical to `seconds`
151    fn second(self) -> PgInterval {
152        self.seconds()
153    }
154
155    /// Identical to `minutes`
156    fn minute(self) -> PgInterval {
157        self.minutes()
158    }
159
160    /// Identical to `hours`
161    fn hour(self) -> PgInterval {
162        self.hours()
163    }
164
165    /// Identical to `days`
166    fn day(self) -> PgInterval {
167        self.days()
168    }
169
170    /// Identical to `weeks`
171    fn week(self) -> PgInterval {
172        self.weeks()
173    }
174
175    /// Identical to `months`
176    fn month(self) -> PgInterval {
177        self.months()
178    }
179
180    /// Identical to `years`
181    fn year(self) -> PgInterval {
182        self.years()
183    }
184}
185
186impl IntervalDsl for i32 {
187    fn microseconds(self) -> PgInterval {
188        i64::from(self).microseconds()
189    }
190
191    fn days(self) -> PgInterval {
192        PgInterval::from_days(self)
193    }
194
195    fn months(self) -> PgInterval {
196        PgInterval::from_months(self)
197    }
198
199    fn milliseconds(self) -> PgInterval {
200        i64::from(self).milliseconds()
201    }
202
203    fn seconds(self) -> PgInterval {
204        i64::from(self).seconds()
205    }
206
207    fn minutes(self) -> PgInterval {
208        i64::from(self).minutes()
209    }
210
211    fn hours(self) -> PgInterval {
212        i64::from(self).hours()
213    }
214}
215
216impl IntervalDsl for i64 {
217    fn microseconds(self) -> PgInterval {
218        PgInterval::from_microseconds(self)
219    }
220
221    fn days(self) -> PgInterval {
222        i32::try_from(self)
223            .expect("Maximal supported day interval size is 32 bit")
224            .days()
225    }
226
227    fn months(self) -> PgInterval {
228        i32::try_from(self)
229            .expect("Maximal supported month interval size is 32 bit")
230            .months()
231    }
232}
233
234#[allow(clippy::cast_possible_truncation)] // we want to truncate
235impl IntervalDsl for f64 {
236    fn microseconds(self) -> PgInterval {
237        (self.round() as i64).microseconds()
238    }
239
240    fn days(self) -> PgInterval {
241        let fractional_days = (self.fract() * 86_400.0).seconds();
242        PgInterval::from_days(self.trunc() as i32) + fractional_days
243    }
244
245    fn months(self) -> PgInterval {
246        let fractional_months = (self.fract() * 30.0).days();
247        PgInterval::from_months(self.trunc() as i32) + fractional_months
248    }
249
250    fn years(self) -> PgInterval {
251        ((self * 12.0).round() as i32).months()
252    }
253}
254
255#[cfg(test)]
256// those macros define nested function
257// that's fine for this test code
258#[allow(clippy::items_after_statements)]
259mod tests {
260    extern crate dotenvy;
261    extern crate quickcheck;
262
263    use self::quickcheck::quickcheck;
264
265    use super::*;
266    use crate::dsl::sql;
267    use crate::prelude::*;
268    use crate::test_helpers::*;
269    use crate::{select, sql_types};
270
271    macro_rules! test_fn {
272        ($tpe:ty, $test_name:ident, $units: ident, $max_range: expr) => {
273            test_fn!($tpe, $test_name, $units, $max_range, 1, 0);
274        };
275        ($tpe:ty, $test_name:ident, $units:ident, $max_range: expr, $max_diff: expr) => {
276            test_fn!($tpe, $test_name, $units, $max_range, $max_diff, 0);
277        };
278        ($tpe:ty, $test_name:ident, $units:ident, $max_range: expr, $max_diff: expr, $max_month_diff: expr) => {
279            fn $test_name(val: $tpe) -> bool {
280                if val > $max_range || val < (-1 as $tpe) * $max_range || (val as f64).is_nan() {
281                    return true;
282                }
283                let conn = &mut pg_connection();
284                let sql_str = format!(concat!("'{} ", stringify!($units), "'::interval"), val);
285                let query = select(sql::<sql_types::Interval>(&sql_str));
286                let value = val.$units();
287                query
288                    .get_result::<PgInterval>(conn)
289                    .map(|res| {
290                        (value.months - res.months).abs() <= $max_month_diff
291                            && value.days == res.days
292                            && (value.microseconds - res.microseconds).abs() <= $max_diff
293                    })
294                    .unwrap_or(false)
295            }
296
297            quickcheck($test_name as fn($tpe) -> bool);
298        };
299    }
300
301    #[diesel_test_helper::test]
302    fn intervals_match_pg_values_i32() {
303        test_fn!(i32, test_microseconds, microseconds, i32::MAX);
304        test_fn!(i32, test_milliseconds, milliseconds, i32::MAX);
305        test_fn!(i32, test_seconds, seconds, i32::MAX);
306        test_fn!(i32, test_minutes, minutes, i32::MAX);
307        test_fn!(i32, test_hours, hours, i32::MAX);
308        test_fn!(i32, test_days, days, i32::MAX);
309        test_fn!(i32, test_weeks, weeks, i32::MAX / 7);
310        test_fn!(i32, test_months, months, i32::MAX);
311        test_fn!(i32, test_years, years, i32::MAX / 12);
312    }
313
314    #[diesel_test_helper::test]
315    fn intervals_match_pg_values_i64() {
316        // postgres does not really support intervals with more than i32::MAX microseconds
317        // https://www.postgresql.org/message-id/20140126025049.GL9750@momjian.us
318        test_fn!(i64, test_microseconds, microseconds, i32::MAX as i64);
319        test_fn!(i64, test_milliseconds, milliseconds, i32::MAX as i64);
320        test_fn!(i64, test_seconds, seconds, i32::MAX as i64);
321        test_fn!(i64, test_minutes, minutes, i32::MAX as i64);
322        test_fn!(i64, test_hours, hours, i32::MAX as i64);
323        test_fn!(i64, test_days, days, i32::MAX as i64);
324        test_fn!(i64, test_weeks, weeks, (i32::MAX / 7) as i64);
325        test_fn!(i64, test_months, months, i32::MAX as i64);
326        test_fn!(i64, test_years, years, (i32::MAX / 12) as i64);
327    }
328
329    #[diesel_test_helper::test]
330    fn intervals_match_pg_values_f64() {
331        const MAX_DIFF: i64 = 1_000_000;
332        // postgres does not really support intervals with more than i32::MAX microseconds
333        // https://www.postgresql.org/message-id/20140126025049.GL9750@momjian.us
334        test_fn!(
335            f64,
336            test_microseconds,
337            microseconds,
338            i32::MAX as f64,
339            MAX_DIFF
340        );
341        test_fn!(
342            f64,
343            test_milliseconds,
344            milliseconds,
345            i32::MAX as f64,
346            MAX_DIFF
347        );
348        test_fn!(f64, test_seconds, seconds, i32::MAX as f64, MAX_DIFF);
349        test_fn!(f64, test_minutes, minutes, i32::MAX as f64, MAX_DIFF);
350        test_fn!(f64, test_hours, hours, i32::MAX as f64, MAX_DIFF);
351        test_fn!(f64, test_days, days, i32::MAX as f64, MAX_DIFF);
352        test_fn!(f64, test_weeks, weeks, (i32::MAX / 7) as f64, MAX_DIFF);
353        test_fn!(f64, test_months, months, i32::MAX as f64, MAX_DIFF);
354        // different postgres versions seem to round intervals with years differently
355        // -1681.9781874756495 years is reported as -20183 months for postgres 14
356        // and as -20184 months for postgres 16
357        test_fn!(f64, test_years, years, (i32::MAX / 12) as f64, MAX_DIFF, 1);
358    }
359}
diesel/pg/expression/extensions/interval_dsl.rs

diesel/pg/expression/extensions/
interval_dsl.rs
