diesel/pg/types/date_and_time/
time.rs

1//! This module makes it possible to map `time` date and time values to postgres `Date`
2//! and `Timestamp` fields. It is enabled with the `time` feature.
3
4extern crate time;
5
6use self::time::{
7    macros::{date, datetime},
8    Date as NaiveDate, Duration, OffsetDateTime, PrimitiveDateTime, Time as NaiveTime, UtcOffset,
9};
10
11use super::{PgDate, PgTime, PgTimestamp};
12use crate::deserialize::{self, Defaultable, FromSql};
13use crate::pg::{Pg, PgValue};
14use crate::serialize::{self, Output, ToSql};
15use crate::sql_types::{Date, Time, Timestamp, Timestamptz};
16
17// Postgres timestamps start from January 1st 2000.
18const PG_EPOCH: PrimitiveDateTime = datetime!(2000-1-1 0:00:00);
19
20#[cfg(all(feature = "time", feature = "postgres_backend"))]
21impl FromSql<Timestamp, Pg> for PrimitiveDateTime {
22    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
23        let PgTimestamp(offset) = FromSql::<Timestamp, Pg>::from_sql(bytes)?;
24        match PG_EPOCH.checked_add(Duration::microseconds(offset)) {
25            Some(v) => Ok(v),
26            None => {
27                let message = "Tried to deserialize a timestamp that is too large for Time";
28                Err(message.into())
29            }
30        }
31    }
32}
33
34#[cfg(all(feature = "time", feature = "postgres_backend"))]
35impl ToSql<Timestamp, Pg> for PrimitiveDateTime {
36    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
37        let micros = (*self - PG_EPOCH).whole_microseconds();
38        if micros > (i64::MAX as i128) {
39            let error_message = format!("{self:?} as microseconds is too large to fit in an i64");
40            return Err(error_message.into());
41        }
42        ToSql::<Timestamp, Pg>::to_sql(&PgTimestamp(micros.try_into()?), &mut out.reborrow())
43    }
44}
45
46#[cfg(all(feature = "time", feature = "postgres_backend"))]
47impl FromSql<Timestamptz, Pg> for PrimitiveDateTime {
48    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
49        FromSql::<Timestamp, Pg>::from_sql(bytes)
50    }
51}
52
53#[cfg(all(feature = "time", feature = "postgres_backend"))]
54impl ToSql<Timestamptz, Pg> for PrimitiveDateTime {
55    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
56        ToSql::<Timestamp, Pg>::to_sql(self, out)
57    }
58}
59
60#[cfg(all(feature = "time", feature = "postgres_backend"))]
61impl Defaultable for PrimitiveDateTime {
62    fn default_value() -> Self {
63        PG_EPOCH
64    }
65}
66
67#[cfg(all(feature = "time", feature = "postgres_backend"))]
68impl FromSql<Timestamptz, Pg> for OffsetDateTime {
69    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
70        let primitive_date_time = <PrimitiveDateTime as FromSql<Timestamptz, Pg>>::from_sql(bytes)?;
71        Ok(primitive_date_time.assume_utc())
72    }
73}
74
75#[cfg(all(feature = "time", feature = "postgres_backend"))]
76impl ToSql<Timestamptz, Pg> for OffsetDateTime {
77    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
78        let as_utc = self.to_offset(UtcOffset::UTC);
79        let primitive_date_time = PrimitiveDateTime::new(as_utc.date(), as_utc.time());
80        ToSql::<Timestamptz, Pg>::to_sql(&primitive_date_time, &mut out.reborrow())
81    }
82}
83
84#[cfg(all(feature = "time", feature = "postgres_backend"))]
85impl Defaultable for OffsetDateTime {
86    fn default_value() -> Self {
87        datetime!(2000-01-01 0:00:00 UTC)
88    }
89}
90
91#[cfg(all(feature = "time", 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 - NaiveTime::MIDNIGHT;
95        ToSql::<Time, Pg>::to_sql(
96            &PgTime(duration.whole_microseconds().try_into()?),
97            &mut out.reborrow(),
98        )
99    }
100}
101
102#[cfg(all(feature = "time", 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(NaiveTime::MIDNIGHT + duration)
108    }
109}
110
111const PG_EPOCH_DATE: NaiveDate = date!(2000 - 1 - 1);
112
113#[cfg(all(feature = "time", feature = "postgres_backend"))]
114impl ToSql<Date, Pg> for NaiveDate {
115    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
116        let days_since_epoch = (*self - PG_EPOCH_DATE).whole_days();
117        ToSql::<Date, Pg>::to_sql(&PgDate(days_since_epoch.try_into()?), &mut out.reborrow())
118    }
119}
120
121#[cfg(all(feature = "time", feature = "postgres_backend"))]
122impl FromSql<Date, Pg> for NaiveDate {
123    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
124        let PgDate(offset) = FromSql::<Date, Pg>::from_sql(bytes)?;
125        match PG_EPOCH_DATE.checked_add(Duration::days(i64::from(offset))) {
126            Some(date) => Ok(date),
127            None => {
128                let error_message =
129                    format!("Time can only represent dates up to {:?}", NaiveDate::MAX);
130                Err(error_message.into())
131            }
132        }
133    }
134}
135
136#[cfg(all(feature = "time", feature = "postgres_backend"))]
137impl Defaultable for NaiveDate {
138    fn default_value() -> Self {
139        PG_EPOCH_DATE
140    }
141}
142
143#[cfg(test)]
144mod tests {
145    extern crate dotenvy;
146
147    use crate::dsl::{now, sql};
148    use crate::prelude::*;
149    use crate::select;
150    use crate::sql_types::{Date, Time, Timestamp, Timestamptz};
151    use crate::test_helpers::connection;
152
153    use time::{
154        macros::{date, datetime},
155        Date as NaiveDate, Duration, OffsetDateTime, PrimitiveDateTime, Time as NaiveTime,
156    };
157
158    fn naive_now() -> PrimitiveDateTime {
159        let offset_now = OffsetDateTime::now_utc();
160        PrimitiveDateTime::new(offset_now.date(), offset_now.time())
161    }
162
163    #[diesel_test_helper::test]
164    fn unix_epoch_encodes_correctly() {
165        let connection = &mut connection();
166        let time = datetime!(1970-1-1 0:00:00);
167        let query = select(sql::<Timestamp>("'1970-01-01'").eq(time));
168        assert!(query.get_result::<bool>(connection).unwrap());
169    }
170
171    #[diesel_test_helper::test]
172    fn unix_epoch_encodes_correctly_with_utc_timezone() {
173        let connection = &mut connection();
174        let time = datetime!(1970-1-1 0:00:00 utc);
175        let query = select(sql::<Timestamptz>("'1970-01-01Z'::timestamptz").eq(time));
176        assert!(query.get_result::<bool>(connection).unwrap());
177    }
178
179    #[diesel_test_helper::test]
180    fn unix_epoch_encodes_correctly_with_timezone() {
181        let connection = &mut connection();
182        let time = datetime!(1970-1-1 0:00:00 -1:00);
183        let query = select(sql::<Timestamptz>("'1970-01-01 01:00:00Z'::timestamptz").eq(time));
184        assert!(query.get_result::<bool>(connection).unwrap());
185    }
186
187    #[diesel_test_helper::test]
188    fn unix_epoch_decodes_correctly() {
189        let connection = &mut connection();
190        let time = datetime!(1970-1-1 0:0:0);
191        let epoch_from_sql =
192            select(sql::<Timestamp>("'1970-01-01'::timestamp")).get_result(connection);
193        assert_eq!(Ok(time), epoch_from_sql);
194    }
195
196    #[diesel_test_helper::test]
197    fn unix_epoch_decodes_correctly_with_timezone() {
198        let connection = &mut connection();
199        let time = datetime!(1970-1-1 0:00:00 utc);
200        let epoch_from_sql =
201            select(sql::<Timestamptz>("'1970-01-01Z'::timestamptz")).get_result(connection);
202        assert_eq!(Ok(time), epoch_from_sql);
203    }
204
205    #[diesel_test_helper::test]
206    fn times_relative_to_now_encode_correctly() {
207        let connection = &mut connection();
208        let time = naive_now() + Duration::seconds(60);
209        let query = select(now.at_time_zone("utc").lt(time));
210        assert!(query.get_result::<bool>(connection).unwrap());
211
212        let time = naive_now() - Duration::seconds(60);
213        let query = select(now.at_time_zone("utc").gt(time));
214        assert!(query.get_result::<bool>(connection).unwrap());
215    }
216
217    #[diesel_test_helper::test]
218    fn times_with_timezones_round_trip_after_conversion() {
219        let connection = &mut connection();
220        let time = datetime!(2016-1-2 1:00:00 +1);
221        let expected = datetime!(2016-1-1 20:0:0);
222        let query = select(time.into_sql::<Timestamptz>().at_time_zone("EDT"));
223        assert_eq!(Ok(expected), query.get_result(connection));
224    }
225
226    #[diesel_test_helper::test]
227    fn times_of_day_encode_correctly() {
228        let connection = &mut connection();
229
230        let query = select(sql::<Time>("'00:00:00'::time").eq(NaiveTime::MIDNIGHT));
231        assert!(query.get_result::<bool>(connection).unwrap());
232
233        let noon = NaiveTime::from_hms(12, 0, 0).expect("noon is a legal time");
234        let query = select(sql::<Time>("'12:00:00'::time").eq(noon));
235        assert!(query.get_result::<bool>(connection).unwrap());
236
237        let roughly_half_past_eleven =
238            NaiveTime::from_hms_micro(23, 37, 4, 2200).expect("this is also a legal time");
239        let query = select(sql::<Time>("'23:37:04.002200'::time").eq(roughly_half_past_eleven));
240        assert!(query.get_result::<bool>(connection).unwrap());
241    }
242
243    #[diesel_test_helper::test]
244    fn times_of_day_decode_correctly() {
245        let connection = &mut connection();
246        let query = select(sql::<Time>("'00:00:00'::time"));
247        let result: Result<NaiveTime, _> = query.get_result(connection);
248        assert_eq!(Ok(NaiveTime::MIDNIGHT), result);
249
250        let noon = NaiveTime::from_hms(12, 0, 0).expect("this time is legal");
251        let query = select(sql::<Time>("'12:00:00'::time"));
252        let result: Result<NaiveTime, _> = query.get_result(connection);
253        assert_eq!(Ok(noon), result);
254
255        let roughly_half_past_eleven =
256            NaiveTime::from_hms_micro(23, 37, 4, 2200).expect("this time is legal");
257        let query = select(sql::<Time>("'23:37:04.002200'::time"));
258        let result: Result<NaiveTime, _> = query.get_result(connection);
259        assert_eq!(Ok(roughly_half_past_eleven), result);
260    }
261
262    #[diesel_test_helper::test]
263    fn dates_encode_correctly() {
264        let connection = &mut connection();
265        let january_first_2000 = date!(2000 - 1 - 1);
266        let query = select(sql::<Date>("'2000-1-1'").eq(january_first_2000));
267        assert!(query.get_result::<bool>(connection).unwrap());
268
269        let distant_past = date!(-398 - 4 - 11); // year 0 is 1 BC in this function
270        let query = select(sql::<Date>("'399-4-11 BC'").eq(distant_past));
271        assert!(query.get_result::<bool>(connection).unwrap());
272
273        let julian_epoch = date!(-4713 - 11 - 24);
274        let query = select(sql::<Date>("'J0'::date").eq(julian_epoch));
275        assert!(query.get_result::<bool>(connection).unwrap());
276
277        let max_date = NaiveDate::MAX;
278        let query = select(sql::<Date>("'9999-12-31'::date").eq(max_date));
279        assert!(query.get_result::<bool>(connection).unwrap());
280
281        let january_first_2018 = date!(2018 - 1 - 1);
282        let query = select(sql::<Date>("'2018-1-1'::date").eq(january_first_2018));
283        assert!(query.get_result::<bool>(connection).unwrap());
284
285        let distant_future = date!(9999 - 1 - 8);
286        let query = select(sql::<Date>("'9999-1-8'::date").eq(distant_future));
287        assert!(query.get_result::<bool>(connection).unwrap());
288    }
289
290    #[diesel_test_helper::test]
291    fn dates_decode_correctly() {
292        let connection = &mut connection();
293        let january_first_2000 = date!(2000 - 1 - 1);
294        let query = select(sql::<Date>("'2000-1-1'::date"));
295        assert_eq!(
296            Ok(january_first_2000),
297            query.get_result::<NaiveDate>(connection)
298        );
299
300        let distant_past = date!(-398 - 4 - 11);
301        let query = select(sql::<Date>("'399-4-11 BC'::date"));
302        assert_eq!(Ok(distant_past), query.get_result::<NaiveDate>(connection));
303
304        let julian_epoch = date!(-4713 - 11 - 24);
305        let query = select(sql::<Date>("'J0'::date"));
306        assert_eq!(Ok(julian_epoch), query.get_result::<NaiveDate>(connection));
307
308        let max_date = NaiveDate::MAX;
309        let query = select(sql::<Date>("'9999-12-31'::date"));
310        assert_eq!(Ok(max_date), query.get_result::<NaiveDate>(connection));
311
312        let january_first_2018 = date!(2018 - 1 - 1);
313        let query = select(sql::<Date>("'2018-1-1'::date"));
314        assert_eq!(
315            Ok(january_first_2018),
316            query.get_result::<NaiveDate>(connection)
317        );
318
319        let distant_future = date!(9999 - 1 - 8);
320        let query = select(sql::<Date>("'9999-1-8'::date"));
321        assert_eq!(
322            Ok(distant_future),
323            query.get_result::<NaiveDate>(connection)
324        );
325    }
326}
diesel/pg/types/date_and_time/time.rs

diesel/pg/types/date_and_time/
time.rs
