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, 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 FromSql<Timestamptz, Pg> for OffsetDateTime {
62    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
63        let primitive_date_time = <PrimitiveDateTime as FromSql<Timestamptz, Pg>>::from_sql(bytes)?;
64        Ok(primitive_date_time.assume_utc())
65    }
66}
67
68#[cfg(all(feature = "time", feature = "postgres_backend"))]
69impl ToSql<Timestamptz, Pg> for OffsetDateTime {
70    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
71        let as_utc = self.to_offset(UtcOffset::UTC);
72        let primitive_date_time = PrimitiveDateTime::new(as_utc.date(), as_utc.time());
73        ToSql::<Timestamptz, Pg>::to_sql(&primitive_date_time, &mut out.reborrow())
74    }
75}
76
77#[cfg(all(feature = "time", feature = "postgres_backend"))]
78impl ToSql<Time, Pg> for NaiveTime {
79    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
80        let duration = *self - NaiveTime::MIDNIGHT;
81        ToSql::<Time, Pg>::to_sql(
82            &PgTime(duration.whole_microseconds().try_into()?),
83            &mut out.reborrow(),
84        )
85    }
86}
87
88#[cfg(all(feature = "time", feature = "postgres_backend"))]
89impl FromSql<Time, Pg> for NaiveTime {
90    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
91        let PgTime(offset) = FromSql::<Time, Pg>::from_sql(bytes)?;
92        let duration = Duration::microseconds(offset);
93        Ok(NaiveTime::MIDNIGHT + duration)
94    }
95}
96
97const PG_EPOCH_DATE: NaiveDate = date!(2000 - 1 - 1);
98
99#[cfg(all(feature = "time", feature = "postgres_backend"))]
100impl ToSql<Date, Pg> for NaiveDate {
101    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Pg>) -> serialize::Result {
102        let days_since_epoch = (*self - PG_EPOCH_DATE).whole_days();
103        ToSql::<Date, Pg>::to_sql(&PgDate(days_since_epoch.try_into()?), &mut out.reborrow())
104    }
105}
106
107#[cfg(all(feature = "time", feature = "postgres_backend"))]
108impl FromSql<Date, Pg> for NaiveDate {
109    fn from_sql(bytes: PgValue<'_>) -> deserialize::Result<Self> {
110        let PgDate(offset) = FromSql::<Date, Pg>::from_sql(bytes)?;
111        match PG_EPOCH_DATE.checked_add(Duration::days(i64::from(offset))) {
112            Some(date) => Ok(date),
113            None => {
114                let error_message =
115                    format!("Time can only represent dates up to {:?}", NaiveDate::MAX);
116                Err(error_message.into())
117            }
118        }
119    }
120}
121
122#[cfg(test)]
123mod tests {
124    extern crate dotenvy;
125
126    use crate::dsl::{now, sql};
127    use crate::prelude::*;
128    use crate::select;
129    use crate::sql_types::{Date, Time, Timestamp, Timestamptz};
130    use crate::test_helpers::connection;
131
132    use time::{
133        macros::{date, datetime},
134        Date as NaiveDate, Duration, OffsetDateTime, PrimitiveDateTime, Time as NaiveTime,
135    };
136
137    fn naive_now() -> PrimitiveDateTime {
138        let offset_now = OffsetDateTime::now_utc();
139        PrimitiveDateTime::new(offset_now.date(), offset_now.time())
140    }
141
142    #[test]
143    fn unix_epoch_encodes_correctly() {
144        let connection = &mut connection();
145        let time = datetime!(1970-1-1 0:00:00);
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 = &mut connection();
153        let time = datetime!(1970-1-1 0:00:00 utc);
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 = &mut connection();
161        let time = datetime!(1970-1-1 0:00:00 -1:00);
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 = &mut connection();
169        let time = datetime!(1970-1-1 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 = &mut connection();
178        let time = datetime!(1970-1-1 0:00:00 utc);
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 = &mut connection();
187        let time = naive_now() + 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 = naive_now() - 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 = &mut connection();
199        let time = datetime!(2016-1-2 1:00:00 +1);
200        let expected = datetime!(2016-1-1 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 = &mut connection();
208
209        let query = select(sql::<Time>("'00:00:00'::time").eq(NaiveTime::MIDNIGHT));
210        assert!(query.get_result::<bool>(connection).unwrap());
211
212        let noon = NaiveTime::from_hms(12, 0, 0).expect("noon is a legal time");
213        let query = select(sql::<Time>("'12:00:00'::time").eq(noon));
214        assert!(query.get_result::<bool>(connection).unwrap());
215
216        let roughly_half_past_eleven =
217            NaiveTime::from_hms_micro(23, 37, 4, 2200).expect("this is also a legal time");
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 = &mut connection();
225        let query = select(sql::<Time>("'00:00:00'::time"));
226        let result: Result<NaiveTime, _> = query.get_result(connection);
227        assert_eq!(Ok(NaiveTime::MIDNIGHT), result);
228
229        let noon = NaiveTime::from_hms(12, 0, 0).expect("this time is legal");
230        let query = select(sql::<Time>("'12:00:00'::time"));
231        let result: Result<NaiveTime, _> = query.get_result(connection);
232        assert_eq!(Ok(noon), result);
233
234        let roughly_half_past_eleven =
235            NaiveTime::from_hms_micro(23, 37, 4, 2200).expect("this time is legal");
236        let query = select(sql::<Time>("'23:37:04.002200'::time"));
237        let result: Result<NaiveTime, _> = query.get_result(connection);
238        assert_eq!(Ok(roughly_half_past_eleven), result);
239    }
240
241    #[test]
242    fn dates_encode_correctly() {
243        let connection = &mut connection();
244        let january_first_2000 = date!(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 = date!(-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 = date!(-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 = NaiveDate::MAX;
257        let query = select(sql::<Date>("'9999-12-31'::date").eq(max_date));
258        assert!(query.get_result::<bool>(connection).unwrap());
259
260        let january_first_2018 = date!(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 = date!(9999 - 1 - 8);
265        let query = select(sql::<Date>("'9999-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 = &mut connection();
272        let january_first_2000 = date!(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 = date!(-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 = date!(-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 = NaiveDate::MAX;
288        let query = select(sql::<Date>("'9999-12-31'::date"));
289        assert_eq!(Ok(max_date), query.get_result::<NaiveDate>(connection));
290
291        let january_first_2018 = date!(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 = date!(9999 - 1 - 8);
299        let query = select(sql::<Date>("'9999-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/time.rs

diesel/pg/types/date_and_time/
time.rs
