diesel/mysql/types/date_and_time/
chrono.rs1use 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)] 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}