diesel/pg/types/date_and_time/
chrono.rs

1//! This module makes it possible to map `chrono::DateTime` values to postgres `Date`
2//! and `Timestamp` fields. It is enabled with the `chrono` feature.
3
4extern crate chrono;
5use self::chrono::{DateTime, Duration, Local, NaiveDate, NaiveDateTime, NaiveTime, TimeZone, Utc};
6
7use super::{PgDate, PgInterval, PgTime, PgTimestamp};
8use crate::deserialize::{self, Defaultable, FromSql};
9use crate::pg::{Pg, PgValue};
10use crate::serialize::{self, Output, ToSql};
11use crate::sql_types::{Date, Interval, Time, Timestamp, Timestamptz};
12
13// Postgres timestamps start from January 1st 2000.
14fn pg_epoch() -> NaiveDateTime {
15    NaiveDate::from_ymd_opt(2000, 1, 1)
16        .expect("This is in supported range of chrono dates")
17        .and_hms_opt(0, 0, 0)
18        .expect("This is a valid input")
19}
20
21#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
22impl FromSql<Timestamp, Pg> for NaiveDateTime {
23    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
24        let PgTimestamp(offset) = FromSql::<Timestamp, Pg>::from_sql(bytes)?;
25        match pg_epoch().checked_add_signed(Duration::microseconds(offset)) {
26            Some(v) => Ok(v),
27            None => {
28                let message = "Tried to deserialize a timestamp that is too large for Chrono";
29                Err(message.into())
30            }
31        }
32    }
33}
34
35#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
36impl ToSql<Timestamp, Pg> for NaiveDateTime {
37    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
38        let time = match (self.signed_duration_since(pg_epoch())).num_microseconds() {
39            Some(time) => time,
40            None => {
41                let error_message =
42                    ::alloc::__export::must_use({
        ::alloc::fmt::format(format_args!("{0:?} as microseconds is too large to fit in an i64",
                self))
    })format!("{self:?} as microseconds is too large to fit in an i64");
43                return Err(error_message.into());
44            }
45        };
46        ToSql::<Timestamp, Pg>::to_sql(&PgTimestamp(time), &mut out.reborrow())
47    }
48}
49
50#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
51impl FromSql<Timestamptz, Pg> for NaiveDateTime {
52    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
53        FromSql::<Timestamp, Pg>::from_sql(bytes)
54    }
55}
56
57#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
58impl ToSql<Timestamptz, Pg> for NaiveDateTime {
59    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
60        ToSql::<Timestamp, Pg>::to_sql(self, out)
61    }
62}
63
64#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
65impl Defaultable for NaiveDateTime {
66    fn default_value() -> Self {
67        Self::default()
68    }
69}
70
71#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
72impl FromSql<Timestamptz, Pg> for DateTime<Utc> {
73    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
74        let naive_date_time = <NaiveDateTime as FromSql<Timestamptz, Pg>>::from_sql(bytes)?;
75        Ok(Utc.from_utc_datetime(&naive_date_time))
76    }
77}
78
79#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
80impl Defaultable for DateTime<Utc> {
81    fn default_value() -> Self {
82        Self::default()
83    }
84}
85
86#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
87impl FromSql<Timestamptz, Pg> for DateTime<Local> {
88    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
89        let naive_date_time = <NaiveDateTime as FromSql<Timestamptz, Pg>>::from_sql(bytes)?;
90        Ok(Local::from_utc_datetime(&Local, &naive_date_time))
91    }
92}
93
94#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
95impl Defaultable for DateTime<Local> {
96    fn default_value() -> Self {
97        Self::default()
98    }
99}
100
101#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
102impl<TZ: TimeZone> ToSql<Timestamptz, Pg> for DateTime<TZ> {
103    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
104        ToSql::<Timestamptz, Pg>::to_sql(&self.naive_utc(), &mut out.reborrow())
105    }
106}
107
108fn midnight() -> NaiveTime {
109    NaiveTime::from_hms_opt(0, 0, 0).expect("This is a valid hms spec")
110}
111
112#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
113impl ToSql<Time, Pg> for NaiveTime {
114    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
115        let duration = self.signed_duration_since(midnight());
116        match duration.num_microseconds() {
117            Some(offset) => ToSql::<Time, Pg>::to_sql(&PgTime(offset), &mut out.reborrow()),
118            None => ::core::panicking::panic("internal error: entered unreachable code")unreachable!(),
119        }
120    }
121}
122
123#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
124impl FromSql<Time, Pg> for NaiveTime {
125    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
126        let PgTime(offset) = FromSql::<Time, Pg>::from_sql(bytes)?;
127        let duration = Duration::microseconds(offset);
128        Ok(midnight() + duration)
129    }
130}
131
132fn pg_epoch_date() -> NaiveDate {
133    NaiveDate::from_ymd_opt(2000, 1, 1).expect("This is in supported range of chrono dates")
134}
135
136#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
137impl ToSql<Date, Pg> for NaiveDate {
138    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
139        let days_since_epoch = self.signed_duration_since(pg_epoch_date()).num_days();
140        ToSql::<Date, Pg>::to_sql(&PgDate(days_since_epoch.try_into()?), &mut out.reborrow())
141    }
142}
143
144#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
145impl FromSql<Date, Pg> for NaiveDate {
146    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
147        let PgDate(offset) = FromSql::<Date, Pg>::from_sql(bytes)?;
148        #[allow(deprecated)] // otherwise we would need to bump our minimal chrono version
149        let duration = Duration::days(i64::from(offset));
150        match pg_epoch_date().checked_add_signed(duration) {
151            Some(date) => Ok(date),
152            None => {
153                let error_message = ::alloc::__export::must_use({
        ::alloc::fmt::format(format_args!("Chrono can only represent dates up to {0:?}",
                chrono::NaiveDate::MAX))
    })format!(
154                    "Chrono can only represent dates up to {:?}",
155                    chrono::NaiveDate::MAX
156                );
157                Err(error_message.into())
158            }
159        }
160    }
161}
162
163#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
164impl Defaultable for NaiveDate {
165    fn default_value() -> Self {
166        Self::default()
167    }
168}
169
170const DAYS_PER_MONTH: i64 = 30;
171const SECONDS_PER_DAY: i64 = 60 * 60 * 24;
172const MICROSECONDS_PER_SECOND: i64 = 1_000_000;
173
174#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
175impl ToSql<Interval, Pg> for Duration {
176    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
177        let microseconds: i64 = if let Some(v) = self.num_microseconds() {
178            v % (MICROSECONDS_PER_SECOND * SECONDS_PER_DAY)
179        } else {
180            return Err("Failed to create microseconds by overflow".into());
181        };
182        let days: i32 = self
183            .num_days()
184            .try_into()
185            .expect("Failed to get i32 days from i64");
186        // We don't use months here, because in PostgreSQL
187        // `timestamp - timestamp` returns interval where
188        // every delta is contained in days and microseconds, and 0 months.
189        // https://www.postgresql.org/docs/current/functions-datetime.html
190        let interval = PgInterval {
191            microseconds,
192            days,
193            months: 0,
194        };
195        <PgInterval as ToSql<Interval, Pg>>::to_sql(&interval, &mut out.reborrow())
196    }
197}
198
199#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
200impl FromSql<Interval, Pg> for Duration {
201    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
202        let interval: PgInterval = FromSql::<Interval, Pg>::from_sql(bytes)?;
203        // We use 1 month = 30 days and 1 day = 24 hours, as postgres
204        // use those ratios as default when explicitly converted.
205        // For reference, please read `justify_interval` from this page.
206        // https://www.postgresql.org/docs/current/functions-datetime.html
207        // widened, since any `i32` month and day pair fits `i64` days and chrono
208        let days = i64::from(interval.months) * DAYS_PER_MONTH + i64::from(interval.days);
209        Ok(Duration::days(days) + Duration::microseconds(interval.microseconds))
210    }
211}
212
213#[cfg(test)]
214mod tests {
215    extern crate chrono;
216    extern crate dotenvy;
217
218    use self::chrono::{Duration, FixedOffset, NaiveDate, NaiveTime, TimeZone, Utc};
219
220    use crate::dsl::{now, sql};
221    use crate::prelude::*;
222    use crate::select;
223    use crate::sql_types::{Date, Interval, Time, Timestamp, Timestamptz};
224    use crate::test_helpers::connection;
225
226    #[diesel_test_helper::test]
227    fn regression_interval_months_do_not_overflow() {
228        use crate::pg::value::PgValue;
229
230        let mut offenders = Vec::new();
231        // 71_582_788 months is where the product leaves `i32`
232        for months in [
233            0,
234            1,
235            -1,
236            71_582_787,
237            71_582_788,
238            71_582_789,
239            -71_582_789,
240            i32::MAX,
241            i32::MIN,
242        ] {
243            for days in [0, 1, -1, i32::MAX, i32::MIN] {
244                for microseconds in [0, 1, -1, i64::MAX, i64::MIN] {
245                    let mut buffer = Vec::new();
246                    buffer.extend_from_slice(&microseconds.to_be_bytes());
247                    buffer.extend_from_slice(&days.to_be_bytes());
248                    buffer.extend_from_slice(&months.to_be_bytes());
249
250                    let expected = Duration::days(i64::from(months) * 30 + i64::from(days))
251                        + Duration::microseconds(microseconds);
252                    let read = <Duration as crate::deserialize::FromSql<Interval, crate::pg::Pg>>::from_sql(
253                        PgValue::for_test(&buffer),
254                    );
255                    if read.as_ref().ok() != Some(&expected) {
256                        offenders.push(format!(
257                            "{months} months {days} days {microseconds} us read as {read:?}, not {expected:?}"
258                        ));
259                    }
260                }
261            }
262        }
263        // a deterministic xorshift over the whole three field space
264        let mut state: u64 = 0x2545F4914F6CDD1D;
265        let mut next = move || {
266            state ^= state << 13;
267            state ^= state >> 7;
268            state ^= state << 17;
269            state
270        };
271        for _ in 0..4096 {
272            let bytes = next().to_ne_bytes();
273            let months = i32::from_ne_bytes([bytes[0], bytes[1], bytes[2], bytes[3]]);
274            let days = i32::from_ne_bytes([bytes[4], bytes[5], bytes[6], bytes[7]]);
275            let microseconds = i64::from_ne_bytes(next().to_ne_bytes());
276            let mut buffer = Vec::new();
277            buffer.extend_from_slice(&microseconds.to_be_bytes());
278            buffer.extend_from_slice(&days.to_be_bytes());
279            buffer.extend_from_slice(&months.to_be_bytes());
280
281            let expected = Duration::days(i64::from(months) * 30 + i64::from(days))
282                + Duration::microseconds(microseconds);
283            let read = <Duration as crate::deserialize::FromSql<Interval, crate::pg::Pg>>::from_sql(
284                PgValue::for_test(&buffer),
285            );
286            if read.as_ref().ok() != Some(&expected) {
287                offenders.push(format!(
288                    "{months} months {days} days {microseconds} us read as {read:?}, not {expected:?}"
289                ));
290            }
291        }
292
293        assert!(
294            offenders.is_empty(),
295            "{} intervals converted wrongly, first {:?}",
296            offenders.len(),
297            &offenders[..offenders.len().min(3)]
298        );
299    }
300
301    #[diesel_test_helper::test]
302    fn regression_interval_from_postgres_does_not_overflow() {
303        let connection = &mut connection();
304        // postgres stores each of these happily, so none is a synthetic blob
305        for (literal, months, days) in [
306            ("178956970 years 7 mons", i32::MAX, 0),
307            ("1 mon 2147483647 days", 1, i32::MAX),
308            ("-178956970 years -8 mons", i32::MIN, 0),
309        ] {
310            let read = select(sql::<Interval>(&format!("'{literal}'::interval")))
311                .get_result::<Duration>(connection)
312                .unwrap();
313            let expected = Duration::days(i64::from(months) * 30 + i64::from(days));
314            assert_eq!(read, expected, "{literal} read as {read:?}");
315        }
316    }
317
318    #[diesel_test_helper::test]
319    fn unix_epoch_encodes_correctly() {
320        let connection = &mut connection();
321        let time = NaiveDate::from_ymd_opt(1970, 1, 1)
322            .unwrap()
323            .and_hms_opt(0, 0, 0)
324            .unwrap();
325        let query = select(sql::<Timestamp>("'1970-01-01'").eq(time));
326        assert!(query.get_result::<bool>(connection).unwrap());
327    }
328
329    #[diesel_test_helper::test]
330    fn unix_epoch_encodes_correctly_with_utc_timezone() {
331        let connection = &mut connection();
332        let time = Utc.with_ymd_and_hms(1970, 1, 1, 0, 0, 0).single().unwrap();
333        let query = select(sql::<Timestamptz>("'1970-01-01Z'::timestamptz").eq(time));
334        assert!(query.get_result::<bool>(connection).unwrap());
335    }
336
337    #[diesel_test_helper::test]
338    fn unix_epoch_encodes_correctly_with_timezone() {
339        let connection = &mut connection();
340        let time = FixedOffset::west_opt(3600)
341            .unwrap()
342            .with_ymd_and_hms(1970, 1, 1, 0, 0, 0)
343            .single()
344            .unwrap();
345        let query = select(sql::<Timestamptz>("'1970-01-01 01:00:00Z'::timestamptz").eq(time));
346        assert!(query.get_result::<bool>(connection).unwrap());
347    }
348
349    #[diesel_test_helper::test]
350    fn unix_epoch_decodes_correctly() {
351        let connection = &mut connection();
352        let time = NaiveDate::from_ymd_opt(1970, 1, 1)
353            .unwrap()
354            .and_hms_opt(0, 0, 0)
355            .unwrap();
356        let epoch_from_sql =
357            select(sql::<Timestamp>("'1970-01-01'::timestamp")).get_result(connection);
358        assert_eq!(Ok(time), epoch_from_sql);
359    }
360
361    #[diesel_test_helper::test]
362    fn unix_epoch_decodes_correctly_with_timezone() {
363        let connection = &mut connection();
364        let time = Utc.with_ymd_and_hms(1970, 1, 1, 0, 0, 0).single().unwrap();
365        let epoch_from_sql =
366            select(sql::<Timestamptz>("'1970-01-01Z'::timestamptz")).get_result(connection);
367        assert_eq!(Ok(time), epoch_from_sql);
368    }
369
370    #[diesel_test_helper::test]
371    fn times_relative_to_now_encode_correctly() {
372        let connection = &mut connection();
373        let time = Utc::now().naive_utc() + Duration::try_seconds(60).unwrap();
374        let query = select(now.at_time_zone("utc").lt(time));
375        assert!(query.get_result::<bool>(connection).unwrap());
376
377        let time = Utc::now().naive_utc() - Duration::try_seconds(60).unwrap();
378        let query = select(now.at_time_zone("utc").gt(time));
379        assert!(query.get_result::<bool>(connection).unwrap());
380    }
381
382    #[diesel_test_helper::test]
383    fn times_with_timezones_round_trip_after_conversion() {
384        let connection = &mut connection();
385        let time = FixedOffset::east_opt(3600)
386            .unwrap()
387            .with_ymd_and_hms(2016, 1, 2, 1, 0, 0)
388            .unwrap();
389        let expected = NaiveDate::from_ymd_opt(2016, 1, 1)
390            .unwrap()
391            .and_hms_opt(20, 0, 0)
392            .unwrap();
393        let query = select(time.into_sql::<Timestamptz>().at_time_zone("EDT"));
394        assert_eq!(Ok(expected), query.get_result(connection));
395    }
396
397    #[diesel_test_helper::test]
398    fn times_of_day_encode_correctly() {
399        let connection = &mut connection();
400
401        let midnight = NaiveTime::from_hms_opt(0, 0, 0).unwrap();
402        let query = select(sql::<Time>("'00:00:00'::time").eq(midnight));
403        assert!(query.get_result::<bool>(connection).unwrap());
404
405        let noon = NaiveTime::from_hms_opt(12, 0, 0).unwrap();
406        let query = select(sql::<Time>("'12:00:00'::time").eq(noon));
407        assert!(query.get_result::<bool>(connection).unwrap());
408
409        let roughly_half_past_eleven = NaiveTime::from_hms_micro_opt(23, 37, 4, 2200).unwrap();
410        let query = select(sql::<Time>("'23:37:04.002200'::time").eq(roughly_half_past_eleven));
411        assert!(query.get_result::<bool>(connection).unwrap());
412    }
413
414    #[diesel_test_helper::test]
415    fn times_of_day_decode_correctly() {
416        let connection = &mut connection();
417        let midnight = NaiveTime::from_hms_opt(0, 0, 0).unwrap();
418        let query = select(sql::<Time>("'00:00:00'::time"));
419        assert_eq!(Ok(midnight), query.get_result::<NaiveTime>(connection));
420
421        let noon = NaiveTime::from_hms_opt(12, 0, 0).unwrap();
422        let query = select(sql::<Time>("'12:00:00'::time"));
423        assert_eq!(Ok(noon), query.get_result::<NaiveTime>(connection));
424
425        let roughly_half_past_eleven = NaiveTime::from_hms_micro_opt(23, 37, 4, 2200).unwrap();
426        let query = select(sql::<Time>("'23:37:04.002200'::time"));
427        assert_eq!(
428            Ok(roughly_half_past_eleven),
429            query.get_result::<NaiveTime>(connection)
430        );
431    }
432
433    #[diesel_test_helper::test]
434    fn dates_encode_correctly() {
435        let connection = &mut connection();
436        let january_first_2000 = NaiveDate::from_ymd_opt(2000, 1, 1).unwrap();
437        let query = select(sql::<Date>("'2000-1-1'").eq(january_first_2000));
438        assert!(query.get_result::<bool>(connection).unwrap());
439
440        let distant_past = NaiveDate::from_ymd_opt(-398, 4, 11).unwrap(); // year 0 is 1 BC in this function
441        let query = select(sql::<Date>("'399-4-11 BC'").eq(distant_past));
442        assert!(query.get_result::<bool>(connection).unwrap());
443
444        let julian_epoch = NaiveDate::from_ymd_opt(-4713, 11, 24).unwrap();
445        let query = select(sql::<Date>("'J0'::date").eq(julian_epoch));
446        assert!(query.get_result::<bool>(connection).unwrap());
447
448        let max_date = NaiveDate::from_ymd_opt(262142, 12, 31).unwrap();
449        let query = select(sql::<Date>("'262142-12-31'::date").eq(max_date));
450        assert!(query.get_result::<bool>(connection).unwrap());
451
452        let january_first_2018 = NaiveDate::from_ymd_opt(2018, 1, 1).unwrap();
453        let query = select(sql::<Date>("'2018-1-1'::date").eq(january_first_2018));
454        assert!(query.get_result::<bool>(connection).unwrap());
455
456        let distant_future = NaiveDate::from_ymd_opt(72_400, 1, 8).unwrap();
457        let query = select(sql::<Date>("'72400-1-8'::date").eq(distant_future));
458        assert!(query.get_result::<bool>(connection).unwrap());
459    }
460
461    #[diesel_test_helper::test]
462    fn dates_decode_correctly() {
463        let connection = &mut connection();
464        let january_first_2000 = NaiveDate::from_ymd_opt(2000, 1, 1).unwrap();
465        let query = select(sql::<Date>("'2000-1-1'::date"));
466        assert_eq!(
467            Ok(january_first_2000),
468            query.get_result::<NaiveDate>(connection)
469        );
470
471        let distant_past = NaiveDate::from_ymd_opt(-398, 4, 11).unwrap();
472        let query = select(sql::<Date>("'399-4-11 BC'::date"));
473        assert_eq!(Ok(distant_past), query.get_result::<NaiveDate>(connection));
474
475        let julian_epoch = NaiveDate::from_ymd_opt(-4713, 11, 24).unwrap();
476        let query = select(sql::<Date>("'J0'::date"));
477        assert_eq!(Ok(julian_epoch), query.get_result::<NaiveDate>(connection));
478
479        let max_date = NaiveDate::from_ymd_opt(262142, 12, 31).unwrap();
480        let query = select(sql::<Date>("'262142-12-31'::date"));
481        assert_eq!(Ok(max_date), query.get_result::<NaiveDate>(connection));
482
483        let january_first_2018 = NaiveDate::from_ymd_opt(2018, 1, 1).unwrap();
484        let query = select(sql::<Date>("'2018-1-1'::date"));
485        assert_eq!(
486            Ok(january_first_2018),
487            query.get_result::<NaiveDate>(connection)
488        );
489
490        let distant_future = NaiveDate::from_ymd_opt(72_400, 1, 8).unwrap();
491        let query = select(sql::<Date>("'72400-1-8'::date"));
492        assert_eq!(
493            Ok(distant_future),
494            query.get_result::<NaiveDate>(connection)
495        );
496    }
497
498    /// Get test duration and corresponding literal SQL strings.
499    fn get_test_duration_and_literal_strings() -> (Duration, Vec<&'static str>) {
500        (
501            Duration::days(60) + Duration::minutes(1) + Duration::microseconds(123456),
502            vec![
503                "60 days 1 minute 123456 microseconds",
504                "2 months 1 minute 123456 microseconds",
505                "5184060 seconds 123456 microseconds",
506                "60 days 60123456 microseconds",
507                "59 days 24 hours 60.123456 seconds",
508                "60 0:01:00.123456",
509                "58 48:01:00.123456",
510                "P0Y2M0DT0H1M0.123456S",
511                "0-2 0:01:00.123456",
512                "P0000-02-00T00:01:00.123456",
513                "1440:01:00.123456",
514                "1 month 30 days 0.5 minutes 30.123456 seconds",
515            ],
516        )
517    }
518
519    #[diesel_test_helper::test]
520    fn duration_encode_correctly() {
521        let connection = &mut connection();
522        let (duration, literal_strings) = get_test_duration_and_literal_strings();
523        for literal in literal_strings {
524            let query = select(sql::<Interval>(&format!("'{literal}'::interval")).eq(duration));
525            assert!(query.get_result::<bool>(connection).unwrap());
526        }
527    }
528
529    #[diesel_test_helper::test]
530    fn duration_decode_correctly() {
531        let connection = &mut connection();
532        let (duration, literal_strings) = get_test_duration_and_literal_strings();
533        for literal in literal_strings {
534            let query = select(sql::<Interval>(&format!("'{literal}'::interval")));
535            assert_eq!(Ok(duration), query.get_result::<Duration>(connection));
536        }
537    }
538}
diesel/pg/types/date_and_time/chrono.rs

diesel/pg/types/date_and_time/
chrono.rs
