diesel/mysql/types/date_and_time/
chrono.rs

1use chrono::{Datelike, NaiveDate, NaiveDateTime, NaiveTime, Timelike};
2use std::os::raw as libc;
3
4use crate::deserialize::{self, FromSql};
5use crate::mysql::{Mysql, MysqlValue};
6use crate::serialize::{self, Output, ToSql};
7use crate::sql_types::{Date, Datetime, Time, Timestamp};
8
9use super::{MysqlTime, MysqlTimestampType};
10
11#[cfg(all(feature = "chrono", feature = "mysql_backend"))]
12impl ToSql<Datetime, Mysql> for NaiveDateTime {
13    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Mysql>) -> serialize::Result {
14        <NaiveDateTime as ToSql<Timestamp, Mysql>>::to_sql(self, out)
15    }
16}
17
18#[cfg(all(feature = "chrono", feature = "mysql_backend"))]
19impl FromSql<Datetime, Mysql> for NaiveDateTime {
20    fn from_sql(bytes: MysqlValue<'_>) -> deserialize::Result<Self> {
21        <NaiveDateTime as FromSql<Timestamp, Mysql>>::from_sql(bytes)
22    }
23}
24
25#[cfg(all(feature = "chrono", feature = "mysql_backend"))]
26impl ToSql<Timestamp, Mysql> for NaiveDateTime {
27    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Mysql>) -> serialize::Result {
28        let mysql_time = MysqlTime {
29            year: self.year().try_into()?,
30            month: self.month() as libc::c_uint,
31            day: self.day() as libc::c_uint,
32            hour: self.hour() as libc::c_uint,
33            minute: self.minute() as libc::c_uint,
34            second: self.second() as libc::c_uint,
35            #[allow(deprecated)] // otherwise we would need to bump our minimal chrono version
36            second_part: libc::c_ulong::from(self.timestamp_subsec_micros()),
37            neg: false,
38            time_type: MysqlTimestampType::MYSQL_TIMESTAMP_DATETIME,
39            time_zone_displacement: 0,
40        };
41
42        <MysqlTime as ToSql<Timestamp, Mysql>>::to_sql(&mysql_time, &mut out.reborrow())
43    }
44}
45
46#[cfg(all(feature = "chrono", feature = "mysql_backend"))]
47impl FromSql<Timestamp, Mysql> for NaiveDateTime {
48    fn from_sql(bytes: MysqlValue<'_>) -> deserialize::Result<Self> {
49        let mysql_time = <MysqlTime as FromSql<Timestamp, Mysql>>::from_sql(bytes)?;
50
51        let micro = mysql_time.second_part.try_into()?;
52        NaiveDate::from_ymd_opt(
53            mysql_time.year.try_into()?,
54            mysql_time.month,
55            mysql_time.day,
56        )
57        .and_then(|v| {
58            v.and_hms_micro_opt(mysql_time.hour, mysql_time.minute, mysql_time.second, micro)
59        })
60        .ok_or_else(|| format!("Cannot parse this date: {mysql_time:?}").into())
61    }
62}
63
64#[cfg(all(feature = "chrono", feature = "mysql_backend"))]
65impl ToSql<Time, Mysql> for NaiveTime {
66    fn to_sql<'b>(&'b self, out: &mut serialize::Output<'b, '_, Mysql>) -> serialize::Result {
67        let mysql_time = MysqlTime {
68            hour: self.hour() as libc::c_uint,
69            minute: self.minute() as libc::c_uint,
70            second: self.second() as libc::c_uint,
71            day: 0,
72            month: 0,
73            second_part: 0,
74            year: 0,
75            neg: false,
76            time_type: MysqlTimestampType::MYSQL_TIMESTAMP_TIME,
77            time_zone_displacement: 0,
78        };
79
80        <MysqlTime as ToSql<Time, Mysql>>::to_sql(&mysql_time, &mut out.reborrow())
81    }
82}
83
84#[cfg(all(feature = "chrono", feature = "mysql_backend"))]
85impl FromSql<Time, Mysql> for NaiveTime {
86    fn from_sql(bytes: MysqlValue<'_>) -> deserialize::Result<Self> {
87        let mysql_time = <MysqlTime as FromSql<Time, Mysql>>::from_sql(bytes)?;
88        NaiveTime::from_hms_opt(mysql_time.hour, mysql_time.minute, mysql_time.second)
89            .ok_or_else(|| format!("Unable to convert {mysql_time:?} to chrono").into())
90    }
91}
92
93#[cfg(all(feature = "chrono", feature = "mysql_backend"))]
94impl ToSql<Date, Mysql> for NaiveDate {
95    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Mysql>) -> serialize::Result {
96        let mysql_time = MysqlTime {
97            year: self.year().try_into()?,
98            month: self.month() as libc::c_uint,
99            day: self.day() as libc::c_uint,
100            hour: 0,
101            minute: 0,
102            second: 0,
103            second_part: 0,
104            neg: false,
105            time_type: MysqlTimestampType::MYSQL_TIMESTAMP_DATE,
106            time_zone_displacement: 0,
107        };
108
109        <MysqlTime as ToSql<Date, Mysql>>::to_sql(&mysql_time, &mut out.reborrow())
110    }
111}
112
113#[cfg(all(feature = "chrono", feature = "mysql_backend"))]
114impl FromSql<Date, Mysql> for NaiveDate {
115    fn from_sql(bytes: MysqlValue<'_>) -> deserialize::Result<Self> {
116        let mysql_time = <MysqlTime as FromSql<Date, Mysql>>::from_sql(bytes)?;
117        NaiveDate::from_ymd_opt(
118            mysql_time.year.try_into()?,
119            mysql_time.month,
120            mysql_time.day,
121        )
122        .ok_or_else(|| format!("Unable to convert {mysql_time:?} to chrono").into())
123    }
124}
125
126#[cfg(test)]
127mod tests {
128    extern crate chrono;
129    extern crate dotenvy;
130
131    use self::chrono::{Duration, NaiveDate, NaiveTime, Utc};
132
133    use crate::dsl::{now, sql};
134    use crate::prelude::*;
135    use crate::select;
136    use crate::sql_types::{Date, Datetime, Time, Timestamp};
137    use crate::test_helpers::connection;
138
139    #[diesel_test_helper::test]
140    fn unix_epoch_encodes_correctly() {
141        let connection = &mut connection();
142        let time = NaiveDate::from_ymd_opt(1970, 1, 1)
143            .unwrap()
144            .and_hms_opt(0, 0, 0)
145            .unwrap();
146        let query = select(sql::<Timestamp>("CAST('1970-01-01' AS DATETIME)").eq(time));
147        assert!(query.get_result::<bool>(connection).unwrap());
148        let query = select(sql::<Datetime>("CAST('1970-01-01' AS DATETIME)").eq(time));
149        assert!(query.get_result::<bool>(connection).unwrap());
150    }
151
152    #[diesel_test_helper::test]
153    fn unix_epoch_decodes_correctly() {
154        let connection = &mut connection();
155        let time = NaiveDate::from_ymd_opt(1970, 1, 1)
156            .unwrap()
157            .and_hms_opt(0, 0, 0)
158            .unwrap();
159        let epoch_from_sql =
160            select(sql::<Timestamp>("CAST('1970-01-01' AS DATETIME)")).get_result(connection);
161        assert_eq!(Ok(time), epoch_from_sql);
162        let epoch_from_sql =
163            select(sql::<Datetime>("CAST('1970-01-01' AS DATETIME)")).get_result(connection);
164        assert_eq!(Ok(time), epoch_from_sql);
165    }
166
167    #[diesel_test_helper::test]
168    fn times_relative_to_now_encode_correctly() {
169        let connection = &mut connection();
170        let time = Utc::now().naive_utc() + Duration::try_days(1).unwrap();
171        let query = select(now.lt(time));
172        assert!(query.get_result::<bool>(connection).unwrap());
173
174        let time = Utc::now().naive_utc() - Duration::try_days(1).unwrap();
175        let query = select(now.gt(time));
176        assert!(query.get_result::<bool>(connection).unwrap());
177    }
178
179    #[diesel_test_helper::test]
180    fn times_of_day_encode_correctly() {
181        let connection = &mut connection();
182
183        let midnight = NaiveTime::from_hms_opt(0, 0, 0).unwrap();
184        let query = select(sql::<Time>("CAST('00:00:00' AS TIME)").eq(midnight));
185        assert!(query.get_result::<bool>(connection).unwrap());
186
187        let noon = NaiveTime::from_hms_opt(12, 0, 0).unwrap();
188        let query = select(sql::<Time>("CAST('12:00:00' AS TIME)").eq(noon));
189        assert!(query.get_result::<bool>(connection).unwrap());
190
191        let roughly_half_past_eleven = NaiveTime::from_hms_opt(23, 37, 4).unwrap();
192        let query = select(sql::<Time>("CAST('23:37:04' AS TIME)").eq(roughly_half_past_eleven));
193        assert!(query.get_result::<bool>(connection).unwrap());
194    }
195
196    #[diesel_test_helper::test]
197    fn times_of_day_decode_correctly() {
198        let connection = &mut connection();
199        let midnight = NaiveTime::from_hms_opt(0, 0, 0).unwrap();
200        let query = select(sql::<Time>("CAST('00:00:00' AS TIME)"));
201        assert_eq!(Ok(midnight), query.get_result::<NaiveTime>(connection));
202
203        let noon = NaiveTime::from_hms_opt(12, 0, 0).unwrap();
204        let query = select(sql::<Time>("CAST('12:00:00' AS TIME)"));
205        assert_eq!(Ok(noon), query.get_result::<NaiveTime>(connection));
206
207        let roughly_half_past_eleven = NaiveTime::from_hms_opt(23, 37, 4).unwrap();
208        let query = select(sql::<Time>("CAST('23:37:04' AS TIME)"));
209        assert_eq!(
210            Ok(roughly_half_past_eleven),
211            query.get_result::<NaiveTime>(connection)
212        );
213    }
214
215    #[diesel_test_helper::test]
216    fn dates_encode_correctly() {
217        let connection = &mut connection();
218        let january_first_2000 = NaiveDate::from_ymd_opt(2000, 1, 1).unwrap();
219        let query = select(sql::<Date>("CAST('2000-1-1' AS DATE)").eq(january_first_2000));
220        assert!(query.get_result::<bool>(connection).unwrap());
221
222        let january_first_2018 = NaiveDate::from_ymd_opt(2018, 1, 1).unwrap();
223        let query = select(sql::<Date>("CAST('2018-1-1' AS DATE)").eq(january_first_2018));
224        assert!(query.get_result::<bool>(connection).unwrap());
225    }
226
227    #[diesel_test_helper::test]
228    fn dates_decode_correctly() {
229        let connection = &mut connection();
230        let january_first_2000 = NaiveDate::from_ymd_opt(2000, 1, 1).unwrap();
231        let query = select(sql::<Date>("CAST('2000-1-1' AS DATE)"));
232        assert_eq!(
233            Ok(january_first_2000),
234            query.get_result::<NaiveDate>(connection)
235        );
236
237        let january_first_2018 = NaiveDate::from_ymd_opt(2018, 1, 1).unwrap();
238        let query = select(sql::<Date>("CAST('2018-1-1' AS DATE)"));
239        assert_eq!(
240            Ok(january_first_2018),
241            query.get_result::<NaiveDate>(connection)
242        );
243
244        crate::sql_query("SET sql_mode = (SELECT REPLACE(@@sql_mode, 'NO_ZERO_DATE,', ''))")
245            .execute(connection)
246            .unwrap();
247        let query = select(sql::<Date>("CAST('0000-00-00' AS DATE)"));
248        assert!(query.get_result::<NaiveDate>(connection).is_err());
249    }
250}