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;
5
6use self::chrono::naive::MAX_DATE;
7use self::chrono::{DateTime, Duration, NaiveDate, NaiveDateTime, NaiveTime, TimeZone, Utc};
8use std::io::Write;
9
10use super::{PgDate, PgTime, PgTimestamp};
11use deserialize::{self, FromSql};
12use pg::Pg;
13use serialize::{self, Output, ToSql};
14use sql_types::{Date, Time, Timestamp, Timestamptz};
15
16// Postgres timestamps start from January 1st 2000.
17fn pg_epoch() -> NaiveDateTime {
18    NaiveDate::from_ymd(2000, 1, 1).and_hms(0, 0, 0)
19}
20
21impl FromSql<Timestamp, Pg> for NaiveDateTime {
22    fn from_sql(bytes: Option<&[u8]>) -> deserialize::Result<Self> {
23        let PgTimestamp(offset) = FromSql::<Timestamp, Pg>::from_sql(bytes)?;
24        match pg_epoch().checked_add_signed(Duration::microseconds(offset)) {
25            Some(v) => Ok(v),
26            None => {
27                let message = "Tried to deserialize a timestamp that is too large for Chrono";
28                Err(message.into())
29            }
30        }
31    }
32}
33
34impl ToSql<Timestamp, Pg> for NaiveDateTime {
35    fn to_sql<W: Write>(&self, out: &mut Output<W, Pg>) -> serialize::Result {
36        let time = match (self.signed_duration_since(pg_epoch())).num_microseconds() {
37            Some(time) => time,
38            None => {
39                let error_message =
40                    format!("{:?} as microseconds is too large to fit in an i64", self);
41                return Err(error_message.into());
42            }
43        };
44        ToSql::<Timestamp, Pg>::to_sql(&PgTimestamp(time), out)
45    }
46}
47
48impl FromSql<Timestamptz, Pg> for NaiveDateTime {
49    fn from_sql(bytes: Option<&[u8]>) -> deserialize::Result<Self> {
50        FromSql::<Timestamp, Pg>::from_sql(bytes)
51    }
52}
53
54impl ToSql<Timestamptz, Pg> for NaiveDateTime {
55    fn to_sql<W: Write>(&self, out: &mut Output<W, Pg>) -> serialize::Result {
56        ToSql::<Timestamp, Pg>::to_sql(self, out)
57    }
58}
59
60impl FromSql<Timestamptz, Pg> for DateTime<Utc> {
61    fn from_sql(bytes: Option<&[u8]>) -> deserialize::Result<Self> {
62        let naive_date_time = <NaiveDateTime as FromSql<Timestamptz, Pg>>::from_sql(bytes)?;
63        Ok(DateTime::from_utc(naive_date_time, Utc))
64    }
65}
66
67impl<TZ: TimeZone> ToSql<Timestamptz, Pg> for DateTime<TZ> {
68    fn to_sql<W: Write>(&self, out: &mut Output<W, Pg>) -> serialize::Result {
69        ToSql::<Timestamptz, Pg>::to_sql(&self.naive_utc(), out)
70    }
71}
72
73fn midnight() -> NaiveTime {
74    NaiveTime::from_hms(0, 0, 0)
75}
76
77impl ToSql<Time, Pg> for NaiveTime {
78    fn to_sql<W: Write>(&self, out: &mut Output<W, Pg>) -> serialize::Result {
79        let duration = self.signed_duration_since(midnight());
80        match duration.num_microseconds() {
81            Some(offset) => ToSql::<Time, Pg>::to_sql(&PgTime(offset), out),
82            None => unreachable!(),
83        }
84    }
85}
86
87impl FromSql<Time, Pg> for NaiveTime {
88    fn from_sql(bytes: Option<&[u8]>) -> deserialize::Result<Self> {
89        let PgTime(offset) = FromSql::<Time, Pg>::from_sql(bytes)?;
90        let duration = Duration::microseconds(offset);
91        Ok(midnight() + duration)
92    }
93}
94
95fn pg_epoch_date() -> NaiveDate {
96    NaiveDate::from_ymd(2000, 1, 1)
97}
98
99impl ToSql<Date, Pg> for NaiveDate {
100    fn to_sql<W: Write>(&self, out: &mut Output<W, Pg>) -> serialize::Result {
101        let days_since_epoch = self.signed_duration_since(pg_epoch_date()).num_days();
102        ToSql::<Date, Pg>::to_sql(&PgDate(days_since_epoch as i32), out)
103    }
104}
105
106impl FromSql<Date, Pg> for NaiveDate {
107    fn from_sql(bytes: Option<&[u8]>) -> deserialize::Result<Self> {
108        let PgDate(offset) = FromSql::<Date, Pg>::from_sql(bytes)?;
109        match pg_epoch_date().checked_add_signed(Duration::days(i64::from(offset))) {
110            Some(date) => Ok(date),
111            None => {
112                let error_message = format!("Chrono can only represent dates up to {:?}", MAX_DATE);
113                Err(error_message.into())
114            }
115        }
116    }
117}
118
119#[cfg(test)]
120mod tests {
121    extern crate chrono;
122    extern crate dotenv;
123
124    use self::chrono::naive::MAX_DATE;
125    use self::chrono::{Duration, FixedOffset, NaiveDate, NaiveTime, TimeZone, Utc};
126    use self::dotenv::dotenv;
127
128    use dsl::{now, sql};
129    use prelude::*;
130    use select;
131    use sql_types::{Date, Time, Timestamp, Timestamptz};
132
133    fn connection() -> PgConnection {
134        dotenv().ok();
135
136        let connection_url = ::std::env::var("PG_DATABASE_URL")
137            .or_else(|_| ::std::env::var("DATABASE_URL"))
138            .expect("DATABASE_URL must be set in order to run tests");
139        PgConnection::establish(&connection_url).unwrap()
140    }
141
142    #[test]
143    fn unix_epoch_encodes_correctly() {
144        let connection = connection();
145        let time = NaiveDate::from_ymd(1970, 1, 1).and_hms(0, 0, 0);
146        let query = select(sql::<Timestamp>("'1970-01-01'").eq(time));
147        assert!(query.get_result::<bool>(&connection).unwrap());
148    }
149
150    #[test]
151    fn unix_epoch_encodes_correctly_with_utc_timezone() {
152        let connection = connection();
153        let time = Utc.ymd(1970, 1, 1).and_hms(0, 0, 0);
154        let query = select(sql::<Timestamptz>("'1970-01-01Z'::timestamptz").eq(time));
155        assert!(query.get_result::<bool>(&connection).unwrap());
156    }
157
158    #[test]
159    fn unix_epoch_encodes_correctly_with_timezone() {
160        let connection = connection();
161        let time = FixedOffset::west(3600).ymd(1970, 1, 1).and_hms(0, 0, 0);
162        let query = select(sql::<Timestamptz>("'1970-01-01 01:00:00Z'::timestamptz").eq(time));
163        assert!(query.get_result::<bool>(&connection).unwrap());
164    }
165
166    #[test]
167    fn unix_epoch_decodes_correctly() {
168        let connection = connection();
169        let time = NaiveDate::from_ymd(1970, 1, 1).and_hms(0, 0, 0);
170        let epoch_from_sql =
171            select(sql::<Timestamp>("'1970-01-01'::timestamp")).get_result(&connection);
172        assert_eq!(Ok(time), epoch_from_sql);
173    }
174
175    #[test]
176    fn unix_epoch_decodes_correctly_with_timezone() {
177        let connection = connection();
178        let time = Utc.ymd(1970, 1, 1).and_hms(0, 0, 0);
179        let epoch_from_sql =
180            select(sql::<Timestamptz>("'1970-01-01Z'::timestamptz")).get_result(&connection);
181        assert_eq!(Ok(time), epoch_from_sql);
182    }
183
184    #[test]
185    fn times_relative_to_now_encode_correctly() {
186        let connection = connection();
187        let time = Utc::now().naive_utc() + Duration::seconds(60);
188        let query = select(now.at_time_zone("utc").lt(time));
189        assert!(query.get_result::<bool>(&connection).unwrap());
190
191        let time = Utc::now().naive_utc() - Duration::seconds(60);
192        let query = select(now.at_time_zone("utc").gt(time));
193        assert!(query.get_result::<bool>(&connection).unwrap());
194    }
195
196    #[test]
197    fn times_with_timezones_round_trip_after_conversion() {
198        let connection = connection();
199        let time = FixedOffset::east(3600).ymd(2016, 1, 2).and_hms(1, 0, 0);
200        let expected = NaiveDate::from_ymd(2016, 1, 1).and_hms(20, 0, 0);
201        let query = select(time.into_sql::<Timestamptz>().at_time_zone("EDT"));
202        assert_eq!(Ok(expected), query.get_result(&connection));
203    }
204
205    #[test]
206    fn times_of_day_encode_correctly() {
207        let connection = connection();
208
209        let midnight = NaiveTime::from_hms(0, 0, 0);
210        let query = select(sql::<Time>("'00:00:00'::time").eq(midnight));
211        assert!(query.get_result::<bool>(&connection).unwrap());
212
213        let noon = NaiveTime::from_hms(12, 0, 0);
214        let query = select(sql::<Time>("'12:00:00'::time").eq(noon));
215        assert!(query.get_result::<bool>(&connection).unwrap());
216
217        let roughly_half_past_eleven = NaiveTime::from_hms_micro(23, 37, 4, 2200);
218        let query = select(sql::<Time>("'23:37:04.002200'::time").eq(roughly_half_past_eleven));
219        assert!(query.get_result::<bool>(&connection).unwrap());
220    }
221
222    #[test]
223    fn times_of_day_decode_correctly() {
224        let connection = connection();
225        let midnight = NaiveTime::from_hms(0, 0, 0);
226        let query = select(sql::<Time>("'00:00:00'::time"));
227        assert_eq!(Ok(midnight), query.get_result::<NaiveTime>(&connection));
228
229        let noon = NaiveTime::from_hms(12, 0, 0);
230        let query = select(sql::<Time>("'12:00:00'::time"));
231        assert_eq!(Ok(noon), query.get_result::<NaiveTime>(&connection));
232
233        let roughly_half_past_eleven = NaiveTime::from_hms_micro(23, 37, 4, 2200);
234        let query = select(sql::<Time>("'23:37:04.002200'::time"));
235        assert_eq!(
236            Ok(roughly_half_past_eleven),
237            query.get_result::<NaiveTime>(&connection)
238        );
239    }
240
241    #[test]
242    fn dates_encode_correctly() {
243        let connection = connection();
244        let january_first_2000 = NaiveDate::from_ymd(2000, 1, 1);
245        let query = select(sql::<Date>("'2000-1-1'").eq(january_first_2000));
246        assert!(query.get_result::<bool>(&connection).unwrap());
247
248        let distant_past = NaiveDate::from_ymd(-398, 4, 11); // year 0 is 1 BC in this function
249        let query = select(sql::<Date>("'399-4-11 BC'").eq(distant_past));
250        assert!(query.get_result::<bool>(&connection).unwrap());
251
252        let julian_epoch = NaiveDate::from_ymd(-4713, 11, 24);
253        let query = select(sql::<Date>("'J0'::date").eq(julian_epoch));
254        assert!(query.get_result::<bool>(&connection).unwrap());
255
256        let max_date = MAX_DATE;
257        let query = select(sql::<Date>("'262143-12-31'::date").eq(max_date));
258        assert!(query.get_result::<bool>(&connection).unwrap());
259
260        let january_first_2018 = NaiveDate::from_ymd(2018, 1, 1);
261        let query = select(sql::<Date>("'2018-1-1'::date").eq(january_first_2018));
262        assert!(query.get_result::<bool>(&connection).unwrap());
263
264        let distant_future = NaiveDate::from_ymd(72_400, 1, 8);
265        let query = select(sql::<Date>("'72400-1-8'::date").eq(distant_future));
266        assert!(query.get_result::<bool>(&connection).unwrap());
267    }
268
269    #[test]
270    fn dates_decode_correctly() {
271        let connection = connection();
272        let january_first_2000 = NaiveDate::from_ymd(2000, 1, 1);
273        let query = select(sql::<Date>("'2000-1-1'::date"));
274        assert_eq!(
275            Ok(january_first_2000),
276            query.get_result::<NaiveDate>(&connection)
277        );
278
279        let distant_past = NaiveDate::from_ymd(-398, 4, 11);
280        let query = select(sql::<Date>("'399-4-11 BC'::date"));
281        assert_eq!(Ok(distant_past), query.get_result::<NaiveDate>(&connection));
282
283        let julian_epoch = NaiveDate::from_ymd(-4713, 11, 24);
284        let query = select(sql::<Date>("'J0'::date"));
285        assert_eq!(Ok(julian_epoch), query.get_result::<NaiveDate>(&connection));
286
287        let max_date = MAX_DATE;
288        let query = select(sql::<Date>("'262143-12-31'::date"));
289        assert_eq!(Ok(max_date), query.get_result::<NaiveDate>(&connection));
290
291        let january_first_2018 = NaiveDate::from_ymd(2018, 1, 1);
292        let query = select(sql::<Date>("'2018-1-1'::date"));
293        assert_eq!(
294            Ok(january_first_2018),
295            query.get_result::<NaiveDate>(&connection)
296        );
297
298        let distant_future = NaiveDate::from_ymd(72_400, 1, 8);
299        let query = select(sql::<Date>("'72400-1-8'::date"));
300        assert_eq!(
301            Ok(distant_future),
302            query.get_result::<NaiveDate>(&connection)
303        );
304    }
305}
diesel/pg/types/date_and_time/chrono.rs

diesel/pg/types/date_and_time/
chrono.rs
