1 //! Convert most of the [Time Strings](http://sqlite.org/lang_datefunc.html) to chrono types. 2 3 use chrono::{DateTime, FixedOffset, Local, NaiveDate, NaiveDateTime, NaiveTime, TimeZone, Utc}; 4 5 use crate::types::{FromSql, FromSqlError, FromSqlResult, ToSql, ToSqlOutput, ValueRef}; 6 use crate::Result; 7 8 /// ISO 8601 calendar date without timezone => "YYYY-MM-DD" 9 impl ToSql for NaiveDate { 10 #[inline] to_sql(&self) -> Result<ToSqlOutput<'_>>11 fn to_sql(&self) -> Result<ToSqlOutput<'_>> { 12 let date_str = self.format("%F").to_string(); 13 Ok(ToSqlOutput::from(date_str)) 14 } 15 } 16 17 /// "YYYY-MM-DD" => ISO 8601 calendar date without timezone. 18 impl FromSql for NaiveDate { 19 #[inline] 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, "%F") { 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 { 32 #[inline] to_sql(&self) -> Result<ToSqlOutput<'_>>33 fn to_sql(&self) -> Result<ToSqlOutput<'_>> { 34 let date_str = self.format("%T%.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 => "%T", 46 _ => "%T%.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 { 59 #[inline] to_sql(&self) -> Result<ToSqlOutput<'_>>60 fn to_sql(&self) -> Result<ToSqlOutput<'_>> { 61 let date_str = self.format("%F %T%.f").to_string(); 62 Ok(ToSqlOutput::from(date_str)) 63 } 64 } 65 66 /// "YYYY-MM-DD HH:MM:SS"/"YYYY-MM-DD HH:MM:SS.SSS" => ISO 8601 combined date 67 /// and time without timezone. ("YYYY-MM-DDTHH:MM:SS"/"YYYY-MM-DDTHH:MM:SS.SSS" 68 /// also supported) 69 impl FromSql for NaiveDateTime { column_result(value: ValueRef<'_>) -> FromSqlResult<Self>70 fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> { 71 value.as_str().and_then(|s| { 72 let fmt = if s.len() >= 11 && s.as_bytes()[10] == b'T' { 73 "%FT%T%.f" 74 } else { 75 "%F %T%.f" 76 }; 77 78 match NaiveDateTime::parse_from_str(s, fmt) { 79 Ok(dt) => Ok(dt), 80 Err(err) => Err(FromSqlError::Other(Box::new(err))), 81 } 82 }) 83 } 84 } 85 86 /// UTC time => UTC RFC3339 timestamp 87 /// ("YYYY-MM-DD HH:MM:SS.SSS+00:00"). 88 impl ToSql for DateTime<Utc> { 89 #[inline] to_sql(&self) -> Result<ToSqlOutput<'_>>90 fn to_sql(&self) -> Result<ToSqlOutput<'_>> { 91 let date_str = self.format("%F %T%.f%:z").to_string(); 92 Ok(ToSqlOutput::from(date_str)) 93 } 94 } 95 96 /// Local time => UTC RFC3339 timestamp 97 /// ("YYYY-MM-DD HH:MM:SS.SSS+00:00"). 98 impl ToSql for DateTime<Local> { 99 #[inline] to_sql(&self) -> Result<ToSqlOutput<'_>>100 fn to_sql(&self) -> Result<ToSqlOutput<'_>> { 101 let date_str = self.with_timezone(&Utc).format("%F %T%.f%:z").to_string(); 102 Ok(ToSqlOutput::from(date_str)) 103 } 104 } 105 106 /// Date and time with time zone => RFC3339 timestamp 107 /// ("YYYY-MM-DD HH:MM:SS.SSS[+-]HH:MM"). 108 impl ToSql for DateTime<FixedOffset> { 109 #[inline] to_sql(&self) -> Result<ToSqlOutput<'_>>110 fn to_sql(&self) -> Result<ToSqlOutput<'_>> { 111 let date_str = self.format("%F %T%.f%:z").to_string(); 112 Ok(ToSqlOutput::from(date_str)) 113 } 114 } 115 116 /// RFC3339 ("YYYY-MM-DD HH:MM:SS.SSS[+-]HH:MM") into `DateTime<Utc>`. 117 impl FromSql for DateTime<Utc> { column_result(value: ValueRef<'_>) -> FromSqlResult<Self>118 fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> { 119 { 120 // Try to parse value as rfc3339 first. 121 let s = value.as_str()?; 122 123 let fmt = if s.len() >= 11 && s.as_bytes()[10] == b'T' { 124 "%FT%T%.f%#z" 125 } else { 126 "%F %T%.f%#z" 127 }; 128 129 if let Ok(dt) = DateTime::parse_from_str(s, fmt) { 130 return Ok(dt.with_timezone(&Utc)); 131 } 132 } 133 134 // Couldn't parse as rfc3339 - fall back to NaiveDateTime. 135 NaiveDateTime::column_result(value).map(|dt| Utc.from_utc_datetime(&dt)) 136 } 137 } 138 139 /// RFC3339 ("YYYY-MM-DD HH:MM:SS.SSS[+-]HH:MM") into `DateTime<Local>`. 140 impl FromSql for DateTime<Local> { 141 #[inline] column_result(value: ValueRef<'_>) -> FromSqlResult<Self>142 fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> { 143 let utc_dt = DateTime::<Utc>::column_result(value)?; 144 Ok(utc_dt.with_timezone(&Local)) 145 } 146 } 147 148 /// RFC3339 ("YYYY-MM-DD HH:MM:SS.SSS[+-]HH:MM") into `DateTime<FixedOffset>`. 149 impl FromSql for DateTime<FixedOffset> { 150 #[inline] column_result(value: ValueRef<'_>) -> FromSqlResult<Self>151 fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> { 152 let s = String::column_result(value)?; 153 Self::parse_from_rfc3339(s.as_str()) 154 .or_else(|_| Self::parse_from_str(s.as_str(), "%F %T%.f%:z")) 155 .map_err(|e| FromSqlError::Other(Box::new(e))) 156 } 157 } 158 159 #[cfg(test)] 160 mod test { 161 use crate::{ 162 types::{FromSql, ValueRef}, 163 Connection, Result, 164 }; 165 use chrono::{ 166 DateTime, Duration, FixedOffset, Local, NaiveDate, NaiveDateTime, NaiveTime, TimeZone, Utc, 167 }; 168 checked_memory_handle() -> Result<Connection>169 fn checked_memory_handle() -> Result<Connection> { 170 let db = Connection::open_in_memory()?; 171 db.execute_batch("CREATE TABLE foo (t TEXT, i INTEGER, f FLOAT, b BLOB)")?; 172 Ok(db) 173 } 174 175 #[test] test_naive_date() -> Result<()>176 fn test_naive_date() -> Result<()> { 177 let db = checked_memory_handle()?; 178 let date = NaiveDate::from_ymd_opt(2016, 2, 23).unwrap(); 179 db.execute("INSERT INTO foo (t) VALUES (?1)", [date])?; 180 181 let s: String = db.one_column("SELECT t FROM foo")?; 182 assert_eq!("2016-02-23", s); 183 let t: NaiveDate = db.one_column("SELECT t FROM foo")?; 184 assert_eq!(date, t); 185 Ok(()) 186 } 187 188 #[test] test_naive_time() -> Result<()>189 fn test_naive_time() -> Result<()> { 190 let db = checked_memory_handle()?; 191 let time = NaiveTime::from_hms_opt(23, 56, 4).unwrap(); 192 db.execute("INSERT INTO foo (t) VALUES (?1)", [time])?; 193 194 let s: String = db.one_column("SELECT t FROM foo")?; 195 assert_eq!("23:56:04", s); 196 let v: NaiveTime = db.one_column("SELECT t FROM foo")?; 197 assert_eq!(time, v); 198 Ok(()) 199 } 200 201 #[test] test_naive_date_time() -> Result<()>202 fn test_naive_date_time() -> Result<()> { 203 let db = checked_memory_handle()?; 204 let date = NaiveDate::from_ymd_opt(2016, 2, 23).unwrap(); 205 let time = NaiveTime::from_hms_opt(23, 56, 4).unwrap(); 206 let dt = NaiveDateTime::new(date, time); 207 208 db.execute("INSERT INTO foo (t) VALUES (?1)", [dt])?; 209 210 let s: String = db.one_column("SELECT t FROM foo")?; 211 assert_eq!("2016-02-23 23:56:04", s); 212 let v: NaiveDateTime = db.one_column("SELECT t FROM foo")?; 213 assert_eq!(dt, v); 214 215 db.execute("UPDATE foo set b = datetime(t)", [])?; // "YYYY-MM-DD HH:MM:SS" 216 let hms: NaiveDateTime = db.one_column("SELECT b FROM foo")?; 217 assert_eq!(dt, hms); 218 Ok(()) 219 } 220 221 #[test] test_date_time_utc() -> Result<()>222 fn test_date_time_utc() -> Result<()> { 223 let db = checked_memory_handle()?; 224 let date = NaiveDate::from_ymd_opt(2016, 2, 23).unwrap(); 225 let time = NaiveTime::from_hms_milli_opt(23, 56, 4, 789).unwrap(); 226 let dt = NaiveDateTime::new(date, time); 227 let utc = Utc.from_utc_datetime(&dt); 228 229 db.execute("INSERT INTO foo (t) VALUES (?1)", [utc])?; 230 231 let s: String = db.one_column("SELECT t FROM foo")?; 232 assert_eq!("2016-02-23 23:56:04.789+00:00", s); 233 234 let v1: DateTime<Utc> = db.one_column("SELECT t FROM foo")?; 235 assert_eq!(utc, v1); 236 237 let v2: DateTime<Utc> = db.one_column("SELECT '2016-02-23 23:56:04.789'")?; 238 assert_eq!(utc, v2); 239 240 let v3: DateTime<Utc> = db.one_column("SELECT '2016-02-23 23:56:04'")?; 241 assert_eq!(utc - Duration::try_milliseconds(789).unwrap(), v3); 242 243 let v4: DateTime<Utc> = db.one_column("SELECT '2016-02-23 23:56:04.789+00:00'")?; 244 assert_eq!(utc, v4); 245 Ok(()) 246 } 247 248 #[test] test_date_time_local() -> Result<()>249 fn test_date_time_local() -> Result<()> { 250 let db = checked_memory_handle()?; 251 let date = NaiveDate::from_ymd_opt(2016, 2, 23).unwrap(); 252 let time = NaiveTime::from_hms_milli_opt(23, 56, 4, 789).unwrap(); 253 let dt = NaiveDateTime::new(date, time); 254 let local = Local.from_local_datetime(&dt).single().unwrap(); 255 256 db.execute("INSERT INTO foo (t) VALUES (?1)", [local])?; 257 258 // Stored string should be in UTC 259 let s: String = db.one_column("SELECT t FROM foo")?; 260 assert!(s.ends_with("+00:00")); 261 262 let v: DateTime<Local> = db.one_column("SELECT t FROM foo")?; 263 assert_eq!(local, v); 264 Ok(()) 265 } 266 267 #[test] test_date_time_fixed() -> Result<()>268 fn test_date_time_fixed() -> Result<()> { 269 let db = checked_memory_handle()?; 270 let time = DateTime::parse_from_rfc3339("2020-04-07T11:23:45+04:00").unwrap(); 271 272 db.execute("INSERT INTO foo (t) VALUES (?1)", [time])?; 273 274 // Stored string should preserve timezone offset 275 let s: String = db.one_column("SELECT t FROM foo")?; 276 assert!(s.ends_with("+04:00")); 277 278 let v: DateTime<FixedOffset> = db.one_column("SELECT t FROM foo")?; 279 assert_eq!(time.offset(), v.offset()); 280 assert_eq!(time, v); 281 Ok(()) 282 } 283 284 #[test] test_sqlite_functions() -> Result<()>285 fn test_sqlite_functions() -> Result<()> { 286 let db = checked_memory_handle()?; 287 let result: Result<NaiveTime> = db.one_column("SELECT CURRENT_TIME"); 288 result.unwrap(); 289 let result: Result<NaiveDate> = db.one_column("SELECT CURRENT_DATE"); 290 result.unwrap(); 291 let result: Result<NaiveDateTime> = db.one_column("SELECT CURRENT_TIMESTAMP"); 292 result.unwrap(); 293 let result: Result<DateTime<Utc>> = db.one_column("SELECT CURRENT_TIMESTAMP"); 294 result.unwrap(); 295 Ok(()) 296 } 297 298 #[test] test_naive_date_time_param() -> Result<()>299 fn test_naive_date_time_param() -> Result<()> { 300 let db = checked_memory_handle()?; 301 let result: Result<bool> = db.query_row("SELECT 1 WHERE ?1 BETWEEN datetime('now', '-1 minute') AND datetime('now', '+1 minute')", [Utc::now().naive_utc()], |r| r.get(0)); 302 result.unwrap(); 303 Ok(()) 304 } 305 306 #[test] test_date_time_param() -> Result<()>307 fn test_date_time_param() -> Result<()> { 308 let db = checked_memory_handle()?; 309 let result: Result<bool> = db.query_row("SELECT 1 WHERE ?1 BETWEEN datetime('now', '-1 minute') AND datetime('now', '+1 minute')", [Utc::now()], |r| r.get(0)); 310 result.unwrap(); 311 Ok(()) 312 } 313 314 #[test] test_lenient_parse_timezone()315 fn test_lenient_parse_timezone() { 316 DateTime::<Utc>::column_result(ValueRef::Text(b"1970-01-01T00:00:00Z")).unwrap(); 317 DateTime::<Utc>::column_result(ValueRef::Text(b"1970-01-01T00:00:00+00")).unwrap(); 318 } 319 } 320