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, 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 FromSql<Timestamptz, Pg> for DateTime<Utc> {
66    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
67        let naive_date_time = <NaiveDateTime as FromSql<Timestamptz, Pg>>::from_sql(bytes)?;
68        Ok(Utc.from_utc_datetime(&naive_date_time))
69    }
70}
71
72#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
73impl FromSql<Timestamptz, Pg> for DateTime<Local> {
74    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
75        let naive_date_time = <NaiveDateTime as FromSql<Timestamptz, Pg>>::from_sql(bytes)?;
76        Ok(Local::from_utc_datetime(&Local, &naive_date_time))
77    }
78}
79
80#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
81impl<TZ: TimeZone> ToSql<Timestamptz, Pg> for DateTime<TZ> {
82    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
83        ToSql::<Timestamptz, Pg>::to_sql(&self.naive_utc(), &mut out.reborrow())
84    }
85}
86
87fn midnight() -> NaiveTime {
88    NaiveTime::from_hms_opt(0, 0, 0).expect("This is a valid hms spec")
89}
90
91#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
92impl ToSql<Time, Pg> for NaiveTime {
93    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
94        let duration = self.signed_duration_since(midnight());
95        match duration.num_microseconds() {
96            Some(offset) => ToSql::<Time, Pg>::to_sql(&PgTime(offset), &mut out.reborrow()),
97            None => unreachable!(),
98        }
99    }
100}
101
102#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
103impl FromSql<Time, Pg> for NaiveTime {
104    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
105        let PgTime(offset) = FromSql::<Time, Pg>::from_sql(bytes)?;
106        let duration = Duration::microseconds(offset);
107        Ok(midnight() + duration)
108    }
109}
110
111fn pg_epoch_date() -> NaiveDate {
112    NaiveDate::from_ymd_opt(2000, 1, 1).expect("This is in supported range of chrono dates")
113}
114
115#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
116impl ToSql<Date, Pg> for NaiveDate {
117    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
118        let days_since_epoch = self.signed_duration_since(pg_epoch_date()).num_days();
119        ToSql::<Date, Pg>::to_sql(&PgDate(days_since_epoch.try_into()?), &mut out.reborrow())
120    }
121}
122
123#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
124impl FromSql<Date, Pg> for NaiveDate {
125    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
126        let PgDate(offset) = FromSql::<Date, Pg>::from_sql(bytes)?;
127        #[allow(deprecated)] // otherwise we would need to bump our minimal chrono version
128        let duration = Duration::days(i64::from(offset));
129        match pg_epoch_date().checked_add_signed(duration) {
130            Some(date) => Ok(date),
131            None => {
132                let error_message = format!(
133                    "Chrono can only represent dates up to {:?}",
134                    chrono::NaiveDate::MAX
135                );
136                Err(error_message.into())
137            }
138        }
139    }
140}
141
142const DAYS_PER_MONTH: i32 = 30;
143const SECONDS_PER_DAY: i64 = 60 * 60 * 24;
144const MICROSECONDS_PER_SECOND: i64 = 1_000_000;
145
146#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
147impl ToSql<Interval, Pg> for Duration {
148    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
149        let microseconds: i64 = if let Some(v) = self.num_microseconds() {
150            v % (MICROSECONDS_PER_SECOND * SECONDS_PER_DAY)
151        } else {
152            return Err("Failed to create microseconds by overflow".into());
153        };
154        let days: i32 = self
155            .num_days()
156            .try_into()
157            .expect("Failed to get i32 days from i64");
158        // We don't use months here, because in PostgreSQL
159        // `timestamp - timestamp` returns interval where
160        // every delta is contained in days and microseconds, and 0 months.
161        // https://www.postgresql.org/docs/current/functions-datetime.html
162        let interval = PgInterval {
163            microseconds,
164            days,
165            months: 0,
166        };
167        <PgInterval as ToSql<Interval, Pg>>::to_sql(&interval, &mut out.reborrow())
168    }
169}
170
171#[cfg(all(feature = "chrono", feature = "postgres_backend"))]
172impl FromSql<Interval, Pg> for Duration {
173    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
174        let interval: PgInterval = FromSql::<Interval, Pg>::from_sql(bytes)?;
175        // We use 1 month = 30 days and 1 day = 24 hours, as postgres
176        // use those ratios as default when explicitly converted.
177        // For reference, please read `justify_interval` from this page.
178        // https://www.postgresql.org/docs/current/functions-datetime.html
179        let days = interval.months * DAYS_PER_MONTH + interval.days;
180        Ok(Duration::days(days as i64) + Duration::microseconds(interval.microseconds))
181    }
182}
183
184#[cfg(test)]
185mod tests {
186    extern crate chrono;
187    extern crate dotenvy;
188
189    use self::chrono::{Duration, FixedOffset, NaiveDate, NaiveTime, TimeZone, Utc};
190
191    use crate::dsl::{now, sql};
192    use crate::prelude::*;
193    use crate::select;
194    use crate::sql_types::{Date, Interval, Time, Timestamp, Timestamptz};
195    use crate::test_helpers::connection;
196
197    #[test]
198    fn unix_epoch_encodes_correctly() {
199        let connection = &mut connection();
200        let time = NaiveDate::from_ymd_opt(1970, 1, 1)
201            .unwrap()
202            .and_hms_opt(0, 0, 0)
203            .unwrap();
204        let query = select(sql::<Timestamp>("'1970-01-01'").eq(time));
205        assert!(query.get_result::<bool>(connection).unwrap());
206    }
207
208    #[test]
209    fn unix_epoch_encodes_correctly_with_utc_timezone() {
210        let connection = &mut connection();
211        let time = Utc.with_ymd_and_hms(1970, 1, 1, 0, 0, 0).single().unwrap();
212        let query = select(sql::<Timestamptz>("'1970-01-01Z'::timestamptz").eq(time));
213        assert!(query.get_result::<bool>(connection).unwrap());
214    }
215
216    #[test]
217    fn unix_epoch_encodes_correctly_with_timezone() {
218        let connection = &mut connection();
219        let time = FixedOffset::west_opt(3600)
220            .unwrap()
221            .with_ymd_and_hms(1970, 1, 1, 0, 0, 0)
222            .single()
223            .unwrap();
224        let query = select(sql::<Timestamptz>("'1970-01-01 01:00:00Z'::timestamptz").eq(time));
225        assert!(query.get_result::<bool>(connection).unwrap());
226    }
227
228    #[test]
229    fn unix_epoch_decodes_correctly() {
230        let connection = &mut connection();
231        let time = NaiveDate::from_ymd_opt(1970, 1, 1)
232            .unwrap()
233            .and_hms_opt(0, 0, 0)
234            .unwrap();
235        let epoch_from_sql =
236            select(sql::<Timestamp>("'1970-01-01'::timestamp")).get_result(connection);
237        assert_eq!(Ok(time), epoch_from_sql);
238    }
239
240    #[test]
241    fn unix_epoch_decodes_correctly_with_timezone() {
242        let connection = &mut connection();
243        let time = Utc.with_ymd_and_hms(1970, 1, 1, 0, 0, 0).single().unwrap();
244        let epoch_from_sql =
245            select(sql::<Timestamptz>("'1970-01-01Z'::timestamptz")).get_result(connection);
246        assert_eq!(Ok(time), epoch_from_sql);
247    }
248
249    #[test]
250    fn times_relative_to_now_encode_correctly() {
251        let connection = &mut connection();
252        let time = Utc::now().naive_utc() + Duration::try_seconds(60).unwrap();
253        let query = select(now.at_time_zone("utc").lt(time));
254        assert!(query.get_result::<bool>(connection).unwrap());
255
256        let time = Utc::now().naive_utc() - Duration::try_seconds(60).unwrap();
257        let query = select(now.at_time_zone("utc").gt(time));
258        assert!(query.get_result::<bool>(connection).unwrap());
259    }
260
261    #[test]
262    fn times_with_timezones_round_trip_after_conversion() {
263        let connection = &mut connection();
264        let time = FixedOffset::east_opt(3600)
265            .unwrap()
266            .with_ymd_and_hms(2016, 1, 2, 1, 0, 0)
267            .unwrap();
268        let expected = NaiveDate::from_ymd_opt(2016, 1, 1)
269            .unwrap()
270            .and_hms_opt(20, 0, 0)
271            .unwrap();
272        let query = select(time.into_sql::<Timestamptz>().at_time_zone("EDT"));
273        assert_eq!(Ok(expected), query.get_result(connection));
274    }
275
276    #[test]
277    fn times_of_day_encode_correctly() {
278        let connection = &mut connection();
279
280        let midnight = NaiveTime::from_hms_opt(0, 0, 0).unwrap();
281        let query = select(sql::<Time>("'00:00:00'::time").eq(midnight));
282        assert!(query.get_result::<bool>(connection).unwrap());
283
284        let noon = NaiveTime::from_hms_opt(12, 0, 0).unwrap();
285        let query = select(sql::<Time>("'12:00:00'::time").eq(noon));
286        assert!(query.get_result::<bool>(connection).unwrap());
287
288        let roughly_half_past_eleven = NaiveTime::from_hms_micro_opt(23, 37, 4, 2200).unwrap();
289        let query = select(sql::<Time>("'23:37:04.002200'::time").eq(roughly_half_past_eleven));
290        assert!(query.get_result::<bool>(connection).unwrap());
291    }
292
293    #[test]
294    fn times_of_day_decode_correctly() {
295        let connection = &mut connection();
296        let midnight = NaiveTime::from_hms_opt(0, 0, 0).unwrap();
297        let query = select(sql::<Time>("'00:00:00'::time"));
298        assert_eq!(Ok(midnight), query.get_result::<NaiveTime>(connection));
299
300        let noon = NaiveTime::from_hms_opt(12, 0, 0).unwrap();
301        let query = select(sql::<Time>("'12:00:00'::time"));
302        assert_eq!(Ok(noon), query.get_result::<NaiveTime>(connection));
303
304        let roughly_half_past_eleven = NaiveTime::from_hms_micro_opt(23, 37, 4, 2200).unwrap();
305        let query = select(sql::<Time>("'23:37:04.002200'::time"));
306        assert_eq!(
307            Ok(roughly_half_past_eleven),
308            query.get_result::<NaiveTime>(connection)
309        );
310    }
311
312    #[test]
313    fn dates_encode_correctly() {
314        let connection = &mut connection();
315        let january_first_2000 = NaiveDate::from_ymd_opt(2000, 1, 1).unwrap();
316        let query = select(sql::<Date>("'2000-1-1'").eq(january_first_2000));
317        assert!(query.get_result::<bool>(connection).unwrap());
318
319        let distant_past = NaiveDate::from_ymd_opt(-398, 4, 11).unwrap(); // year 0 is 1 BC in this function
320        let query = select(sql::<Date>("'399-4-11 BC'").eq(distant_past));
321        assert!(query.get_result::<bool>(connection).unwrap());
322
323        let julian_epoch = NaiveDate::from_ymd_opt(-4713, 11, 24).unwrap();
324        let query = select(sql::<Date>("'J0'::date").eq(julian_epoch));
325        assert!(query.get_result::<bool>(connection).unwrap());
326
327        let max_date = NaiveDate::from_ymd_opt(262142, 12, 31).unwrap();
328        let query = select(sql::<Date>("'262142-12-31'::date").eq(max_date));
329        assert!(query.get_result::<bool>(connection).unwrap());
330
331        let january_first_2018 = NaiveDate::from_ymd_opt(2018, 1, 1).unwrap();
332        let query = select(sql::<Date>("'2018-1-1'::date").eq(january_first_2018));
333        assert!(query.get_result::<bool>(connection).unwrap());
334
335        let distant_future = NaiveDate::from_ymd_opt(72_400, 1, 8).unwrap();
336        let query = select(sql::<Date>("'72400-1-8'::date").eq(distant_future));
337        assert!(query.get_result::<bool>(connection).unwrap());
338    }
339
340    #[test]
341    fn dates_decode_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'::date"));
345        assert_eq!(
346            Ok(january_first_2000),
347            query.get_result::<NaiveDate>(connection)
348        );
349
350        let distant_past = NaiveDate::from_ymd_opt(-398, 4, 11).unwrap();
351        let query = select(sql::<Date>("'399-4-11 BC'::date"));
352        assert_eq!(Ok(distant_past), query.get_result::<NaiveDate>(connection));
353
354        let julian_epoch = NaiveDate::from_ymd_opt(-4713, 11, 24).unwrap();
355        let query = select(sql::<Date>("'J0'::date"));
356        assert_eq!(Ok(julian_epoch), query.get_result::<NaiveDate>(connection));
357
358        let max_date = NaiveDate::from_ymd_opt(262142, 12, 31).unwrap();
359        let query = select(sql::<Date>("'262142-12-31'::date"));
360        assert_eq!(Ok(max_date), query.get_result::<NaiveDate>(connection));
361
362        let january_first_2018 = NaiveDate::from_ymd_opt(2018, 1, 1).unwrap();
363        let query = select(sql::<Date>("'2018-1-1'::date"));
364        assert_eq!(
365            Ok(january_first_2018),
366            query.get_result::<NaiveDate>(connection)
367        );
368
369        let distant_future = NaiveDate::from_ymd_opt(72_400, 1, 8).unwrap();
370        let query = select(sql::<Date>("'72400-1-8'::date"));
371        assert_eq!(
372            Ok(distant_future),
373            query.get_result::<NaiveDate>(connection)
374        );
375    }
376
377    /// Get test duration and corresponding literal SQL strings.
378    fn get_test_duration_and_literal_strings() -> (Duration, Vec<&'static str>) {
379        (
380            Duration::days(60) + Duration::minutes(1) + Duration::microseconds(123456),
381            vec![
382                "60 days 1 minute 123456 microseconds",
383                "2 months 1 minute 123456 microseconds",
384                "5184060 seconds 123456 microseconds",
385                "60 days 60123456 microseconds",
386                "59 days 24 hours 60.123456 seconds",
387                "60 0:01:00.123456",
388                "58 48:01:00.123456",
389                "P0Y2M0DT0H1M0.123456S",
390                "0-2 0:01:00.123456",
391                "P0000-02-00T00:01:00.123456",
392                "1440:01:00.123456",
393                "1 month 30 days 0.5 minutes 30.123456 seconds",
394            ],
395        )
396    }
397
398    #[test]
399    fn duration_encode_correctly() {
400        let connection = &mut connection();
401        let (duration, literal_strings) = get_test_duration_and_literal_strings();
402        for literal in literal_strings {
403            let query = select(sql::<Interval>(&format!("'{}'::interval", literal)).eq(duration));
404            assert!(query.get_result::<bool>(connection).unwrap());
405        }
406    }
407
408    #[test]
409    fn duration_decode_correctly() {
410        let connection = &mut connection();
411        let (duration, literal_strings) = get_test_duration_and_literal_strings();
412        for literal in literal_strings {
413            let query = select(sql::<Interval>(&format!("'{}'::interval", literal)));
414            assert_eq!(Ok(duration), query.get_result::<Duration>(connection));
415        }
416    }
417}
diesel/pg/types/date_and_time/chrono.rs

diesel/pg/types/date_and_time/
chrono.rs
