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