diesel/mysql/types/date_and_time/
time.rs

1use std::os::raw as libc;
2use time::{
3    Date as NaiveDate, Month, OffsetDateTime, PrimitiveDateTime, Time as NaiveTime, UtcOffset,
4};
5
6use crate::deserialize::{self, FromSql};
7use crate::mysql::{Mysql, MysqlValue};
8use crate::serialize::{self, Output, ToSql};
9use crate::sql_types::{Date, Datetime, Time, Timestamp};
10
11use super::{MysqlTime, MysqlTimestampType};
12
13fn to_time(dt: MysqlTime) -> Result<NaiveTime, Box<dyn std::error::Error>> {
14    for (name, field) in [
15        ("year", dt.year),
16        ("month", dt.month),
17        ("day", dt.day),
18        ("offset", dt.time_zone_displacement.try_into()?),
19    ] {
20        if field != 0 {
21            return Err(format!("Unable to convert {dt:?} to time: {name} must be 0").into());
22        }
23    }
24
25    let hour: u8 = dt.hour.try_into()?;
26    let minute: u8 = dt.minute.try_into()?;
27    let second: u8 = dt.second.try_into()?;
28    let microsecond: u32 = dt.second_part.try_into()?;
29
30    Ok(NaiveTime::from_hms_micro(
31        hour,
32        minute,
33        second,
34        microsecond,
35    )?)
36}
37
38fn to_datetime(dt: MysqlTime) -> Result<OffsetDateTime, Box<dyn std::error::Error>> {
39    let year: i32 = dt.year.try_into()?;
40    let month: u8 = dt.month.try_into()?;
41    let month: Month = month.try_into()?;
42    let day: u8 = dt.day.try_into()?;
43    let hour: u8 = dt.hour.try_into()?;
44    let minute: u8 = dt.minute.try_into()?;
45    let second: u8 = dt.second.try_into()?;
46    let microsecond: u32 = dt.second_part.try_into()?;
47    let offset = UtcOffset::from_whole_seconds(dt.time_zone_displacement)?;
48
49    Ok(PrimitiveDateTime::new(
50        NaiveDate::from_calendar_date(year, month, day)?,
51        NaiveTime::from_hms_micro(hour, minute, second, microsecond)?,
52    )
53    .assume_offset(offset))
54}
55
56fn to_primitive_datetime(dt: OffsetDateTime) -> PrimitiveDateTime {
57    let dt = dt.to_offset(UtcOffset::UTC);
58    PrimitiveDateTime::new(dt.date(), dt.time())
59}
60
61// Mysql datetime column has a wider range than timestamp column, so let's implement the fundamental operations in terms of datetime.
62#[cfg(all(feature = "time", feature = "mysql_backend"))]
63impl ToSql<Datetime, Mysql> for PrimitiveDateTime {
64    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Mysql>) -> serialize::Result {
65        let mysql_time = MysqlTime {
66            year: self.year().try_into()?,
67            month: self.month() as libc::c_uint,
68            day: self.day() as libc::c_uint,
69            hour: self.hour() as libc::c_uint,
70            minute: self.minute() as libc::c_uint,
71            second: self.second() as libc::c_uint,
72            second_part: libc::c_ulong::from(self.microsecond()),
73            neg: false,
74            time_type: MysqlTimestampType::MYSQL_TIMESTAMP_DATETIME,
75            time_zone_displacement: 0,
76        };
77
78        <MysqlTime as ToSql<Timestamp, Mysql>>::to_sql(&mysql_time, &mut out.reborrow())
79    }
80}
81
82#[cfg(all(feature = "time", feature = "mysql_backend"))]
83impl FromSql<Datetime, Mysql> for PrimitiveDateTime {
84    fn from_sql(bytes: MysqlValue<'_>) -> deserialize::Result<Self> {
85        let mysql_time = <MysqlTime as FromSql<Timestamp, Mysql>>::from_sql(bytes)?;
86
87        to_datetime(mysql_time)
88            .map(to_primitive_datetime)
89            .map_err(|err| format!("Cannot parse this date: {mysql_time:?}: {err}").into())
90    }
91}
92
93// We can implement timestamps in terms of datetimes
94#[cfg(all(feature = "time", feature = "mysql_backend"))]
95impl ToSql<Timestamp, Mysql> for PrimitiveDateTime {
96    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Mysql>) -> serialize::Result {
97        <PrimitiveDateTime as ToSql<Datetime, Mysql>>::to_sql(self, out)
98    }
99}
100
101#[cfg(all(feature = "time", feature = "mysql_backend"))]
102impl FromSql<Timestamp, Mysql> for PrimitiveDateTime {
103    fn from_sql(bytes: MysqlValue<'_>) -> deserialize::Result<Self> {
104        <PrimitiveDateTime as FromSql<Datetime, Mysql>>::from_sql(bytes)
105    }
106}
107
108// Delegate offset datetimes in terms of UTC primitive datetimes; this stores everything in the DB as UTC
109#[cfg(all(feature = "time", feature = "mysql_backend"))]
110impl ToSql<Datetime, Mysql> for OffsetDateTime {
111    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Mysql>) -> serialize::Result {
112        let prim = to_primitive_datetime(*self);
113        <PrimitiveDateTime as ToSql<Datetime, Mysql>>::to_sql(&prim, &mut out.reborrow())
114    }
115}
116
117#[cfg(all(feature = "time", feature = "mysql_backend"))]
118impl FromSql<Datetime, Mysql> for OffsetDateTime {
119    fn from_sql(bytes: MysqlValue<'_>) -> deserialize::Result<Self> {
120        let prim = <PrimitiveDateTime as FromSql<Datetime, Mysql>>::from_sql(bytes)?;
121        Ok(prim.assume_offset(UtcOffset::UTC))
122    }
123}
124
125// delegate timestamp column to datetime column for offset datetimes
126#[cfg(all(feature = "time", feature = "mysql_backend"))]
127impl ToSql<Timestamp, Mysql> for OffsetDateTime {
128    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Mysql>) -> serialize::Result {
129        <OffsetDateTime as ToSql<Datetime, Mysql>>::to_sql(self, out)
130    }
131}
132
133#[cfg(all(feature = "time", feature = "mysql_backend"))]
134impl FromSql<Timestamp, Mysql> for OffsetDateTime {
135    fn from_sql(bytes: MysqlValue<'_>) -> deserialize::Result<Self> {
136        <OffsetDateTime as FromSql<Datetime, Mysql>>::from_sql(bytes)
137    }
138}
139
140#[cfg(all(feature = "time", feature = "mysql_backend"))]
141impl ToSql<Time, Mysql> for NaiveTime {
142    fn to_sql<'b>(&'b self, out: &mut serialize::Output<'b, '_, Mysql>) -> serialize::Result {
143        let mysql_time = MysqlTime {
144            hour: self.hour() as libc::c_uint,
145            minute: self.minute() as libc::c_uint,
146            second: self.second() as libc::c_uint,
147            day: 0,
148            month: 0,
149            second_part: 0,
150            year: 0,
151            neg: false,
152            time_type: MysqlTimestampType::MYSQL_TIMESTAMP_TIME,
153            time_zone_displacement: 0,
154        };
155
156        <MysqlTime as ToSql<Time, Mysql>>::to_sql(&mysql_time, &mut out.reborrow())
157    }
158}
159
160#[cfg(all(feature = "time", feature = "mysql_backend"))]
161impl FromSql<Time, Mysql> for NaiveTime {
162    fn from_sql(bytes: MysqlValue<'_>) -> deserialize::Result<Self> {
163        let mysql_time = <MysqlTime as FromSql<Time, Mysql>>::from_sql(bytes)?;
164
165        to_time(mysql_time)
166            .map_err(|err| format!("Unable to convert {mysql_time:?} to time: {err}").into())
167    }
168}
169
170#[cfg(all(feature = "time", feature = "mysql_backend"))]
171impl ToSql<Date, Mysql> for NaiveDate {
172    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Mysql>) -> serialize::Result {
173        let mysql_time = MysqlTime {
174            year: self.year().try_into()?,
175            month: self.month() as libc::c_uint,
176            day: self.day() as libc::c_uint,
177            hour: 0,
178            minute: 0,
179            second: 0,
180            second_part: 0,
181            neg: false,
182            time_type: MysqlTimestampType::MYSQL_TIMESTAMP_DATE,
183            time_zone_displacement: 0,
184        };
185
186        <MysqlTime as ToSql<Date, Mysql>>::to_sql(&mysql_time, &mut out.reborrow())
187    }
188}
189
190#[cfg(all(feature = "time", feature = "mysql_backend"))]
191impl FromSql<Date, Mysql> for NaiveDate {
192    fn from_sql(bytes: MysqlValue<'_>) -> deserialize::Result<Self> {
193        let mysql_time = <MysqlTime as FromSql<Date, Mysql>>::from_sql(bytes)?;
194
195        to_datetime(mysql_time)
196            .map_err(|err| format!("Unable to convert {mysql_time:?} to time: {err}").into())
197            .and_then(|dt| {
198                let prim = to_primitive_datetime(dt);
199                if prim.time() == NaiveTime::MIDNIGHT {
200                    Ok(prim.date())
201                } else {
202                    Err(format!("Unable to convert {prim:?} to date: non-0 time part").into())
203                }
204            })
205    }
206}
207
208#[cfg(test)]
209mod tests {
210    extern crate dotenvy;
211    extern crate time;
212
213    use time::{
214        macros::{date, datetime, time},
215        Date as NaiveDate, Duration, OffsetDateTime, Time as NaiveTime,
216    };
217
218    use super::to_primitive_datetime;
219
220    use crate::dsl::{now, sql};
221    use crate::prelude::*;
222    use crate::select;
223    use crate::sql_types::{Date, Datetime, Time, Timestamp};
224    use crate::test_helpers::connection;
225
226    #[diesel_test_helper::test]
227    fn unix_epoch_encodes_correctly() {
228        let connection = &mut connection();
229        let time = datetime!(1970-1-1 0:0:0);
230        let query = select(sql::<Timestamp>("CAST('1970-01-01' AS DATETIME)").eq(time));
231        assert!(query.get_result::<bool>(connection).unwrap());
232        let query = select(sql::<Datetime>("CAST('1970-01-01' AS DATETIME)").eq(time));
233        assert!(query.get_result::<bool>(connection).unwrap());
234    }
235
236    #[diesel_test_helper::test]
237    fn unix_epoch_decodes_correctly() {
238        let connection = &mut connection();
239        let time = datetime!(1970-1-1 0:0:0);
240        let epoch_from_sql =
241            select(sql::<Timestamp>("CAST('1970-01-01' AS DATETIME)")).get_result(connection);
242        assert_eq!(Ok(time), epoch_from_sql);
243        let epoch_from_sql =
244            select(sql::<Datetime>("CAST('1970-01-01' AS DATETIME)")).get_result(connection);
245        assert_eq!(Ok(time), epoch_from_sql);
246    }
247
248    #[diesel_test_helper::test]
249    fn times_relative_to_now_encode_correctly() {
250        let connection = &mut connection();
251        let time = to_primitive_datetime(OffsetDateTime::now_utc()) + Duration::days(1);
252        let query = select(now.lt(time));
253        assert!(query.get_result::<bool>(connection).unwrap());
254
255        let time = to_primitive_datetime(OffsetDateTime::now_utc()) - Duration::days(1);
256        let query = select(now.gt(time));
257        assert!(query.get_result::<bool>(connection).unwrap());
258    }
259
260    #[diesel_test_helper::test]
261    fn times_of_day_encode_correctly() {
262        let connection = &mut connection();
263
264        let midnight = time!(0:0:0);
265        let query = select(sql::<Time>("CAST('00:00:00' AS TIME)").eq(midnight));
266        assert!(query.get_result::<bool>(connection).unwrap());
267
268        let noon = time!(12:0:0);
269        let query = select(sql::<Time>("CAST('12:00:00' AS TIME)").eq(noon));
270        assert!(query.get_result::<bool>(connection).unwrap());
271
272        let roughly_half_past_eleven = time!(23:37:4);
273        let query = select(sql::<Time>("CAST('23:37:04' AS TIME)").eq(roughly_half_past_eleven));
274        assert!(query.get_result::<bool>(connection).unwrap());
275    }
276
277    #[diesel_test_helper::test]
278    fn times_of_day_decode_correctly() {
279        let connection = &mut connection();
280        let midnight = time!(0:0:0);
281        let query = select(sql::<Time>("CAST('00:00:00' AS TIME)"));
282        assert_eq!(Ok(midnight), query.get_result::<NaiveTime>(connection));
283
284        let noon = time!(12:0:0);
285        let query = select(sql::<Time>("CAST('12:00:00' AS TIME)"));
286        assert_eq!(Ok(noon), query.get_result::<NaiveTime>(connection));
287
288        let roughly_half_past_eleven = time!(23:37:4);
289        let query = select(sql::<Time>("CAST('23:37:04' AS TIME)"));
290        assert_eq!(
291            Ok(roughly_half_past_eleven),
292            query.get_result::<NaiveTime>(connection)
293        );
294    }
295
296    #[diesel_test_helper::test]
297    fn dates_encode_correctly() {
298        let connection = &mut connection();
299        let january_first_2000 = date!(2000 - 1 - 1);
300        let query = select(sql::<Date>("CAST('2000-1-1' AS DATE)").eq(january_first_2000));
301        assert!(query.get_result::<bool>(connection).unwrap());
302
303        let january_first_2018 = date!(2018 - 1 - 1);
304        let query = select(sql::<Date>("CAST('2018-1-1' AS DATE)").eq(january_first_2018));
305        assert!(query.get_result::<bool>(connection).unwrap());
306    }
307
308    #[diesel_test_helper::test]
309    fn dates_decode_correctly() {
310        let connection = &mut connection();
311        let january_first_2000 = date!(2000 - 1 - 1);
312        let query = select(sql::<Date>("CAST('2000-1-1' AS DATE)"));
313        assert_eq!(
314            Ok(january_first_2000),
315            query.get_result::<NaiveDate>(connection)
316        );
317
318        let january_first_2018 = date!(2018 - 1 - 1);
319        let query = select(sql::<Date>("CAST('2018-1-1' AS DATE)"));
320        assert_eq!(
321            Ok(january_first_2018),
322            query.get_result::<NaiveDate>(connection)
323        );
324
325        crate::sql_query("SET sql_mode = (SELECT REPLACE(@@sql_mode, 'NO_ZERO_DATE,', ''))")
326            .execute(connection)
327            .unwrap();
328        let query = select(sql::<Date>("CAST('0000-00-00' AS DATE)"));
329        assert!(query.get_result::<NaiveDate>(connection).is_err());
330    }
331}