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