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