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                    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 => 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 = 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: i32 = 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        let days = interval.months * DAYS_PER_MONTH + interval.days;
208        Ok(Duration::days(days as i64) + Duration::microseconds(interval.microseconds))
209    }
210}
211
212#[cfg(test)]
213mod tests {
214    extern crate chrono;
215    extern crate dotenvy;
216
217    use self::chrono::{Duration, FixedOffset, NaiveDate, NaiveTime, TimeZone, Utc};
218
219    use crate::dsl::{now, sql};
220    use crate::prelude::*;
221    use crate::select;
222    use crate::sql_types::{Date, Interval, Time, Timestamp, Timestamptz};
223    use crate::test_helpers::connection;
224
225    #[diesel_test_helper::test]
226    fn unix_epoch_encodes_correctly() {
227        let connection = &mut connection();
228        let time = NaiveDate::from_ymd_opt(1970, 1, 1)
229            .unwrap()
230            .and_hms_opt(0, 0, 0)
231            .unwrap();
232        let query = select(sql::<Timestamp>("'1970-01-01'").eq(time));
233        assert!(query.get_result::<bool>(connection).unwrap());
234    }
235
236    #[diesel_test_helper::test]
237    fn unix_epoch_encodes_correctly_with_utc_timezone() {
238        let connection = &mut connection();
239        let time = Utc.with_ymd_and_hms(1970, 1, 1, 0, 0, 0).single().unwrap();
240        let query = select(sql::<Timestamptz>("'1970-01-01Z'::timestamptz").eq(time));
241        assert!(query.get_result::<bool>(connection).unwrap());
242    }
243
244    #[diesel_test_helper::test]
245    fn unix_epoch_encodes_correctly_with_timezone() {
246        let connection = &mut connection();
247        let time = FixedOffset::west_opt(3600)
248            .unwrap()
249            .with_ymd_and_hms(1970, 1, 1, 0, 0, 0)
250            .single()
251            .unwrap();
252        let query = select(sql::<Timestamptz>("'1970-01-01 01:00:00Z'::timestamptz").eq(time));
253        assert!(query.get_result::<bool>(connection).unwrap());
254    }
255
256    #[diesel_test_helper::test]
257    fn unix_epoch_decodes_correctly() {
258        let connection = &mut connection();
259        let time = NaiveDate::from_ymd_opt(1970, 1, 1)
260            .unwrap()
261            .and_hms_opt(0, 0, 0)
262            .unwrap();
263        let epoch_from_sql =
264            select(sql::<Timestamp>("'1970-01-01'::timestamp")).get_result(connection);
265        assert_eq!(Ok(time), epoch_from_sql);
266    }
267
268    #[diesel_test_helper::test]
269    fn unix_epoch_decodes_correctly_with_timezone() {
270        let connection = &mut connection();
271        let time = Utc.with_ymd_and_hms(1970, 1, 1, 0, 0, 0).single().unwrap();
272        let epoch_from_sql =
273            select(sql::<Timestamptz>("'1970-01-01Z'::timestamptz")).get_result(connection);
274        assert_eq!(Ok(time), epoch_from_sql);
275    }
276
277    #[diesel_test_helper::test]
278    fn times_relative_to_now_encode_correctly() {
279        let connection = &mut connection();
280        let time = Utc::now().naive_utc() + Duration::try_seconds(60).unwrap();
281        let query = select(now.at_time_zone("utc").lt(time));
282        assert!(query.get_result::<bool>(connection).unwrap());
283
284        let time = Utc::now().naive_utc() - Duration::try_seconds(60).unwrap();
285        let query = select(now.at_time_zone("utc").gt(time));
286        assert!(query.get_result::<bool>(connection).unwrap());
287    }
288
289    #[diesel_test_helper::test]
290    fn times_with_timezones_round_trip_after_conversion() {
291        let connection = &mut connection();
292        let time = FixedOffset::east_opt(3600)
293            .unwrap()
294            .with_ymd_and_hms(2016, 1, 2, 1, 0, 0)
295            .unwrap();
296        let expected = NaiveDate::from_ymd_opt(2016, 1, 1)
297            .unwrap()
298            .and_hms_opt(20, 0, 0)
299            .unwrap();
300        let query = select(time.into_sql::<Timestamptz>().at_time_zone("EDT"));
301        assert_eq!(Ok(expected), query.get_result(connection));
302    }
303
304    #[diesel_test_helper::test]
305    fn times_of_day_encode_correctly() {
306        let connection = &mut connection();
307
308        let midnight = NaiveTime::from_hms_opt(0, 0, 0).unwrap();
309        let query = select(sql::<Time>("'00:00:00'::time").eq(midnight));
310        assert!(query.get_result::<bool>(connection).unwrap());
311
312        let noon = NaiveTime::from_hms_opt(12, 0, 0).unwrap();
313        let query = select(sql::<Time>("'12:00:00'::time").eq(noon));
314        assert!(query.get_result::<bool>(connection).unwrap());
315
316        let roughly_half_past_eleven = NaiveTime::from_hms_micro_opt(23, 37, 4, 2200).unwrap();
317        let query = select(sql::<Time>("'23:37:04.002200'::time").eq(roughly_half_past_eleven));
318        assert!(query.get_result::<bool>(connection).unwrap());
319    }
320
321    #[diesel_test_helper::test]
322    fn times_of_day_decode_correctly() {
323        let connection = &mut connection();
324        let midnight = NaiveTime::from_hms_opt(0, 0, 0).unwrap();
325        let query = select(sql::<Time>("'00:00:00'::time"));
326        assert_eq!(Ok(midnight), query.get_result::<NaiveTime>(connection));
327
328        let noon = NaiveTime::from_hms_opt(12, 0, 0).unwrap();
329        let query = select(sql::<Time>("'12:00:00'::time"));
330        assert_eq!(Ok(noon), query.get_result::<NaiveTime>(connection));
331
332        let roughly_half_past_eleven = NaiveTime::from_hms_micro_opt(23, 37, 4, 2200).unwrap();
333        let query = select(sql::<Time>("'23:37:04.002200'::time"));
334        assert_eq!(
335            Ok(roughly_half_past_eleven),
336            query.get_result::<NaiveTime>(connection)
337        );
338    }
339
340    #[diesel_test_helper::test]
341    fn dates_encode_correctly() {
342        let connection = &mut connection();
343        let january_first_2000 = NaiveDate::from_ymd_opt(2000, 1, 1).unwrap();
344        let query = select(sql::<Date>("'2000-1-1'").eq(january_first_2000));
345        assert!(query.get_result::<bool>(connection).unwrap());
346
347        let distant_past = NaiveDate::from_ymd_opt(-398, 4, 11).unwrap(); // year 0 is 1 BC in this function
348        let query = select(sql::<Date>("'399-4-11 BC'").eq(distant_past));
349        assert!(query.get_result::<bool>(connection).unwrap());
350
351        let julian_epoch = NaiveDate::from_ymd_opt(-4713, 11, 24).unwrap();
352        let query = select(sql::<Date>("'J0'::date").eq(julian_epoch));
353        assert!(query.get_result::<bool>(connection).unwrap());
354
355        let max_date = NaiveDate::from_ymd_opt(262142, 12, 31).unwrap();
356        let query = select(sql::<Date>("'262142-12-31'::date").eq(max_date));
357        assert!(query.get_result::<bool>(connection).unwrap());
358
359        let january_first_2018 = NaiveDate::from_ymd_opt(2018, 1, 1).unwrap();
360        let query = select(sql::<Date>("'2018-1-1'::date").eq(january_first_2018));
361        assert!(query.get_result::<bool>(connection).unwrap());
362
363        let distant_future = NaiveDate::from_ymd_opt(72_400, 1, 8).unwrap();
364        let query = select(sql::<Date>("'72400-1-8'::date").eq(distant_future));
365        assert!(query.get_result::<bool>(connection).unwrap());
366    }
367
368    #[diesel_test_helper::test]
369    fn dates_decode_correctly() {
370        let connection = &mut connection();
371        let january_first_2000 = NaiveDate::from_ymd_opt(2000, 1, 1).unwrap();
372        let query = select(sql::<Date>("'2000-1-1'::date"));
373        assert_eq!(
374            Ok(january_first_2000),
375            query.get_result::<NaiveDate>(connection)
376        );
377
378        let distant_past = NaiveDate::from_ymd_opt(-398, 4, 11).unwrap();
379        let query = select(sql::<Date>("'399-4-11 BC'::date"));
380        assert_eq!(Ok(distant_past), query.get_result::<NaiveDate>(connection));
381
382        let julian_epoch = NaiveDate::from_ymd_opt(-4713, 11, 24).unwrap();
383        let query = select(sql::<Date>("'J0'::date"));
384        assert_eq!(Ok(julian_epoch), query.get_result::<NaiveDate>(connection));
385
386        let max_date = NaiveDate::from_ymd_opt(262142, 12, 31).unwrap();
387        let query = select(sql::<Date>("'262142-12-31'::date"));
388        assert_eq!(Ok(max_date), query.get_result::<NaiveDate>(connection));
389
390        let january_first_2018 = NaiveDate::from_ymd_opt(2018, 1, 1).unwrap();
391        let query = select(sql::<Date>("'2018-1-1'::date"));
392        assert_eq!(
393            Ok(january_first_2018),
394            query.get_result::<NaiveDate>(connection)
395        );
396
397        let distant_future = NaiveDate::from_ymd_opt(72_400, 1, 8).unwrap();
398        let query = select(sql::<Date>("'72400-1-8'::date"));
399        assert_eq!(
400            Ok(distant_future),
401            query.get_result::<NaiveDate>(connection)
402        );
403    }
404
405    /// Get test duration and corresponding literal SQL strings.
406    fn get_test_duration_and_literal_strings() -> (Duration, Vec<&'static str>) {
407        (
408            Duration::days(60) + Duration::minutes(1) + Duration::microseconds(123456),
409            vec![
410                "60 days 1 minute 123456 microseconds",
411                "2 months 1 minute 123456 microseconds",
412                "5184060 seconds 123456 microseconds",
413                "60 days 60123456 microseconds",
414                "59 days 24 hours 60.123456 seconds",
415                "60 0:01:00.123456",
416                "58 48:01:00.123456",
417                "P0Y2M0DT0H1M0.123456S",
418                "0-2 0:01:00.123456",
419                "P0000-02-00T00:01:00.123456",
420                "1440:01:00.123456",
421                "1 month 30 days 0.5 minutes 30.123456 seconds",
422            ],
423        )
424    }
425
426    #[diesel_test_helper::test]
427    fn duration_encode_correctly() {
428        let connection = &mut connection();
429        let (duration, literal_strings) = get_test_duration_and_literal_strings();
430        for literal in literal_strings {
431            let query = select(sql::<Interval>(&format!("'{}'::interval", literal)).eq(duration));
432            assert!(query.get_result::<bool>(connection).unwrap());
433        }
434    }
435
436    #[diesel_test_helper::test]
437    fn duration_decode_correctly() {
438        let connection = &mut connection();
439        let (duration, literal_strings) = get_test_duration_and_literal_strings();
440        for literal in literal_strings {
441            let query = select(sql::<Interval>(&format!("'{}'::interval", literal)));
442            assert_eq!(Ok(duration), query.get_result::<Duration>(connection));
443        }
444    }
445}