1 //! Convert most of the [Time Strings](http://sqlite.org/lang_datefunc.html) to chrono types.
2 
3 use std::borrow::Cow;
4 
5 use chrono::{DateTime, Local, NaiveDate, NaiveDateTime, NaiveTime, TimeZone, Utc};
6 
7 use crate::types::{FromSql, FromSqlError, FromSqlResult, ToSql, ToSqlOutput, ValueRef};
8 use crate::Result;
9 
10 /// ISO 8601 calendar date without timezone => "YYYY-MM-DD"
11 impl ToSql for NaiveDate {
to_sql(&self) -> Result<ToSqlOutput<'_>>12     fn to_sql(&self) -> Result<ToSqlOutput<'_>> {
13         let date_str = self.format("%Y-%m-%d").to_string();
14         Ok(ToSqlOutput::from(date_str))
15     }
16 }
17 
18 /// "YYYY-MM-DD" => ISO 8601 calendar date without timezone.
19 impl FromSql for NaiveDate {
column_result(value: ValueRef<'_>) -> FromSqlResult<Self>20     fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
21         value
22             .as_str()
23             .and_then(|s| match NaiveDate::parse_from_str(s, "%Y-%m-%d") {
24                 Ok(dt) => Ok(dt),
25                 Err(err) => Err(FromSqlError::Other(Box::new(err))),
26             })
27     }
28 }
29 
30 /// ISO 8601 time without timezone => "HH:MM:SS.SSS"
31 impl ToSql for NaiveTime {
to_sql(&self) -> Result<ToSqlOutput<'_>>32     fn to_sql(&self) -> Result<ToSqlOutput<'_>> {
33         let date_str = self.format("%H:%M:%S%.f").to_string();
34         Ok(ToSqlOutput::from(date_str))
35     }
36 }
37 
38 /// "HH:MM"/"HH:MM:SS"/"HH:MM:SS.SSS" => ISO 8601 time without timezone.
39 impl FromSql for NaiveTime {
column_result(value: ValueRef<'_>) -> FromSqlResult<Self>40     fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
41         value.as_str().and_then(|s| {
42             let fmt = match s.len() {
43                 5 => "%H:%M",
44                 8 => "%H:%M:%S",
45                 _ => "%H:%M:%S%.f",
46             };
47             match NaiveTime::parse_from_str(s, fmt) {
48                 Ok(dt) => Ok(dt),
49                 Err(err) => Err(FromSqlError::Other(Box::new(err))),
50             }
51         })
52     }
53 }
54 
55 /// ISO 8601 combined date and time without timezone =>
56 /// "YYYY-MM-DDTHH:MM:SS.SSS"
57 impl ToSql for NaiveDateTime {
to_sql(&self) -> Result<ToSqlOutput<'_>>58     fn to_sql(&self) -> Result<ToSqlOutput<'_>> {
59         let date_str = self.format("%Y-%m-%dT%H:%M:%S%.f").to_string();
60         Ok(ToSqlOutput::from(date_str))
61     }
62 }
63 
64 /// "YYYY-MM-DD HH:MM:SS"/"YYYY-MM-DD HH:MM:SS.SSS" => ISO 8601 combined date
65 /// and time without timezone. ("YYYY-MM-DDTHH:MM:SS"/"YYYY-MM-DDTHH:MM:SS.SSS"
66 /// also supported)
67 impl FromSql for NaiveDateTime {
column_result(value: ValueRef<'_>) -> FromSqlResult<Self>68     fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
69         value.as_str().and_then(|s| {
70             let fmt = if s.len() >= 11 && s.as_bytes()[10] == b'T' {
71                 "%Y-%m-%dT%H:%M:%S%.f"
72             } else {
73                 "%Y-%m-%d %H:%M:%S%.f"
74             };
75 
76             match NaiveDateTime::parse_from_str(s, fmt) {
77                 Ok(dt) => Ok(dt),
78                 Err(err) => Err(FromSqlError::Other(Box::new(err))),
79             }
80         })
81     }
82 }
83 
84 /// Date and time with time zone => UTC RFC3339 timestamp
85 /// ("YYYY-MM-DDTHH:MM:SS.SSS+00:00").
86 impl<Tz: TimeZone> ToSql for DateTime<Tz> {
to_sql(&self) -> Result<ToSqlOutput<'_>>87     fn to_sql(&self) -> Result<ToSqlOutput<'_>> {
88         Ok(ToSqlOutput::from(self.with_timezone(&Utc).to_rfc3339()))
89     }
90 }
91 
92 /// RFC3339 ("YYYY-MM-DDTHH:MM:SS.SSS[+-]HH:MM") into `DateTime<Utc>`.
93 impl FromSql for DateTime<Utc> {
column_result(value: ValueRef<'_>) -> FromSqlResult<Self>94     fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
95         {
96             // Try to parse value as rfc3339 first.
97             let s = value.as_str()?;
98 
99             // If timestamp looks space-separated, make a copy and replace it with 'T'.
100             let s = if s.len() >= 11 && s.as_bytes()[10] == b' ' {
101                 let mut s = s.to_string();
102                 unsafe {
103                     let sbytes = s.as_mut_vec();
104                     sbytes[10] = b'T';
105                 }
106                 Cow::Owned(s)
107             } else {
108                 Cow::Borrowed(s)
109             };
110 
111             if let Ok(dt) = DateTime::parse_from_rfc3339(&s) {
112                 return Ok(dt.with_timezone(&Utc));
113             }
114         }
115 
116         // Couldn't parse as rfc3339 - fall back to NaiveDateTime.
117         NaiveDateTime::column_result(value).map(|dt| Utc.from_utc_datetime(&dt))
118     }
119 }
120 
121 /// RFC3339 ("YYYY-MM-DDTHH:MM:SS.SSS[+-]HH:MM") into `DateTime<Local>`.
122 impl FromSql for DateTime<Local> {
column_result(value: ValueRef<'_>) -> FromSqlResult<Self>123     fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
124         let utc_dt = DateTime::<Utc>::column_result(value)?;
125         Ok(utc_dt.with_timezone(&Local))
126     }
127 }
128 
129 #[cfg(test)]
130 mod test {
131     use crate::{Connection, Result, NO_PARAMS};
132     use chrono::{DateTime, Duration, Local, NaiveDate, NaiveDateTime, NaiveTime, TimeZone, Utc};
133 
checked_memory_handle() -> Connection134     fn checked_memory_handle() -> Connection {
135         let db = Connection::open_in_memory().unwrap();
136         db.execute_batch("CREATE TABLE foo (t TEXT, i INTEGER, f FLOAT, b BLOB)")
137             .unwrap();
138         db
139     }
140 
141     #[test]
test_naive_date()142     fn test_naive_date() {
143         let db = checked_memory_handle();
144         let date = NaiveDate::from_ymd(2016, 2, 23);
145         db.execute("INSERT INTO foo (t) VALUES (?)", &[&date])
146             .unwrap();
147 
148         let s: String = db
149             .query_row("SELECT t FROM foo", NO_PARAMS, |r| r.get(0))
150             .unwrap();
151         assert_eq!("2016-02-23", s);
152         let t: NaiveDate = db
153             .query_row("SELECT t FROM foo", NO_PARAMS, |r| r.get(0))
154             .unwrap();
155         assert_eq!(date, t);
156     }
157 
158     #[test]
test_naive_time()159     fn test_naive_time() {
160         let db = checked_memory_handle();
161         let time = NaiveTime::from_hms(23, 56, 4);
162         db.execute("INSERT INTO foo (t) VALUES (?)", &[&time])
163             .unwrap();
164 
165         let s: String = db
166             .query_row("SELECT t FROM foo", NO_PARAMS, |r| r.get(0))
167             .unwrap();
168         assert_eq!("23:56:04", s);
169         let v: NaiveTime = db
170             .query_row("SELECT t FROM foo", NO_PARAMS, |r| r.get(0))
171             .unwrap();
172         assert_eq!(time, v);
173     }
174 
175     #[test]
test_naive_date_time()176     fn test_naive_date_time() {
177         let db = checked_memory_handle();
178         let date = NaiveDate::from_ymd(2016, 2, 23);
179         let time = NaiveTime::from_hms(23, 56, 4);
180         let dt = NaiveDateTime::new(date, time);
181 
182         db.execute("INSERT INTO foo (t) VALUES (?)", &[&dt])
183             .unwrap();
184 
185         let s: String = db
186             .query_row("SELECT t FROM foo", NO_PARAMS, |r| r.get(0))
187             .unwrap();
188         assert_eq!("2016-02-23T23:56:04", s);
189         let v: NaiveDateTime = db
190             .query_row("SELECT t FROM foo", NO_PARAMS, |r| r.get(0))
191             .unwrap();
192         assert_eq!(dt, v);
193 
194         db.execute("UPDATE foo set b = datetime(t)", NO_PARAMS)
195             .unwrap(); // "YYYY-MM-DD HH:MM:SS"
196         let hms: NaiveDateTime = db
197             .query_row("SELECT b FROM foo", NO_PARAMS, |r| r.get(0))
198             .unwrap();
199         assert_eq!(dt, hms);
200     }
201 
202     #[test]
test_date_time_utc()203     fn test_date_time_utc() {
204         let db = checked_memory_handle();
205         let date = NaiveDate::from_ymd(2016, 2, 23);
206         let time = NaiveTime::from_hms_milli(23, 56, 4, 789);
207         let dt = NaiveDateTime::new(date, time);
208         let utc = Utc.from_utc_datetime(&dt);
209 
210         db.execute("INSERT INTO foo (t) VALUES (?)", &[&utc])
211             .unwrap();
212 
213         let s: String = db
214             .query_row("SELECT t FROM foo", NO_PARAMS, |r| r.get(0))
215             .unwrap();
216         assert_eq!("2016-02-23T23:56:04.789+00:00", s);
217 
218         let v1: DateTime<Utc> = db
219             .query_row("SELECT t FROM foo", NO_PARAMS, |r| r.get(0))
220             .unwrap();
221         assert_eq!(utc, v1);
222 
223         let v2: DateTime<Utc> = db
224             .query_row("SELECT '2016-02-23 23:56:04.789'", NO_PARAMS, |r| r.get(0))
225             .unwrap();
226         assert_eq!(utc, v2);
227 
228         let v3: DateTime<Utc> = db
229             .query_row("SELECT '2016-02-23 23:56:04'", NO_PARAMS, |r| r.get(0))
230             .unwrap();
231         assert_eq!(utc - Duration::milliseconds(789), v3);
232 
233         let v4: DateTime<Utc> = db
234             .query_row("SELECT '2016-02-23 23:56:04.789+00:00'", NO_PARAMS, |r| {
235                 r.get(0)
236             })
237             .unwrap();
238         assert_eq!(utc, v4);
239     }
240 
241     #[test]
test_date_time_local()242     fn test_date_time_local() {
243         let db = checked_memory_handle();
244         let date = NaiveDate::from_ymd(2016, 2, 23);
245         let time = NaiveTime::from_hms_milli(23, 56, 4, 789);
246         let dt = NaiveDateTime::new(date, time);
247         let local = Local.from_local_datetime(&dt).single().unwrap();
248 
249         db.execute("INSERT INTO foo (t) VALUES (?)", &[&local])
250             .unwrap();
251 
252         // Stored string should be in UTC
253         let s: String = db
254             .query_row("SELECT t FROM foo", NO_PARAMS, |r| r.get(0))
255             .unwrap();
256         assert!(s.ends_with("+00:00"));
257 
258         let v: DateTime<Local> = db
259             .query_row("SELECT t FROM foo", NO_PARAMS, |r| r.get(0))
260             .unwrap();
261         assert_eq!(local, v);
262     }
263 
264     #[test]
test_sqlite_functions()265     fn test_sqlite_functions() {
266         let db = checked_memory_handle();
267         let result: Result<NaiveTime> =
268             db.query_row("SELECT CURRENT_TIME", NO_PARAMS, |r| r.get(0));
269         assert!(result.is_ok());
270         let result: Result<NaiveDate> =
271             db.query_row("SELECT CURRENT_DATE", NO_PARAMS, |r| r.get(0));
272         assert!(result.is_ok());
273         let result: Result<NaiveDateTime> =
274             db.query_row("SELECT CURRENT_TIMESTAMP", NO_PARAMS, |r| r.get(0));
275         assert!(result.is_ok());
276         let result: Result<DateTime<Utc>> =
277             db.query_row("SELECT CURRENT_TIMESTAMP", NO_PARAMS, |r| r.get(0));
278         assert!(result.is_ok());
279     }
280 }
281