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

diesel/pg/expression/extensions/
interval_dsl.rs
