1 //! Convert formats 1-10 in [Time Values](https://sqlite.org/lang_datefunc.html#time_values) to time types. 2 //! [`ToSql`] and [`FromSql`] implementation for [`time::OffsetDateTime`]. 3 //! [`ToSql`] and [`FromSql`] implementation for [`time::PrimitiveDateTime`]. 4 //! [`ToSql`] and [`FromSql`] implementation for [`time::Date`]. 5 //! [`ToSql`] and [`FromSql`] implementation for [`time::Time`]. 6 //! Time Strings in: 7 //! - Format 2: "YYYY-MM-DD HH:MM" 8 //! - Format 5: "YYYY-MM-DDTHH:MM" 9 //! - Format 8: "HH:MM" 10 //! 11 //! without an explicit second value will assume 0 seconds. 12 //! Time String that contain an optional timezone without an explicit date are unsupported. 13 //! All other assumptions described in [Time Values](https://sqlite.org/lang_datefunc.html#time_values) section are unsupported. 14 15 use crate::types::{FromSql, FromSqlError, FromSqlResult, ToSql, ToSqlOutput, ValueRef}; 16 use crate::{Error, Result}; 17 use time::format_description::FormatItem; 18 use time::macros::format_description; 19 use time::{Date, OffsetDateTime, PrimitiveDateTime, Time}; 20 21 const OFFSET_DATE_TIME_ENCODING: &[FormatItem<'_>] = format_description!( 22 version = 2, 23 "[year]-[month]-[day] [hour]:[minute]:[second].[subsecond][offset_hour sign:mandatory]:[offset_minute]" 24 ); 25 const PRIMITIVE_DATE_TIME_ENCODING: &[FormatItem<'_>] = format_description!( 26 version = 2, 27 "[year]-[month]-[day] [hour]:[minute]:[second].[subsecond]" 28 ); 29 const TIME_ENCODING: &[FormatItem<'_>] = 30 format_description!(version = 2, "[hour]:[minute]:[second].[subsecond]"); 31 32 const DATE_FORMAT: &[FormatItem<'_>] = format_description!(version = 2, "[year]-[month]-[day]"); 33 const TIME_FORMAT: &[FormatItem<'_>] = format_description!( 34 version = 2, 35 "[hour]:[minute][optional [:[second][optional [.[subsecond]]]]]" 36 ); 37 const PRIMITIVE_DATE_TIME_FORMAT: &[FormatItem<'_>] = format_description!( 38 version = 2, 39 "[year]-[month]-[day][first [ ][T]][hour]:[minute][optional [:[second][optional [.[subsecond]]]]]" 40 ); 41 const UTC_DATE_TIME_FORMAT: &[FormatItem<'_>] = format_description!( 42 version = 2, 43 "[year]-[month]-[day][first [ ][T]][hour]:[minute][optional [:[second][optional [.[subsecond]]]]][optional [Z]]" 44 ); 45 const OFFSET_DATE_TIME_FORMAT: &[FormatItem<'_>] = format_description!( 46 version = 2, 47 "[year]-[month]-[day][first [ ][T]][hour]:[minute][optional [:[second][optional [.[subsecond]]]]][offset_hour sign:mandatory]:[offset_minute]" 48 ); 49 const LEGACY_DATE_TIME_FORMAT: &[FormatItem<'_>] = format_description!( 50 version = 2, 51 "[year]-[month]-[day] [hour]:[minute]:[second]:[subsecond] [offset_hour sign:mandatory]:[offset_minute]" 52 ); 53 54 /// OffsetDatetime => RFC3339 format ("YYYY-MM-DD HH:MM:SS.SSS[+-]HH:MM") 55 impl ToSql for OffsetDateTime { 56 #[inline] to_sql(&self) -> Result<ToSqlOutput<'_>>57 fn to_sql(&self) -> Result<ToSqlOutput<'_>> { 58 let time_string = self 59 .format(&OFFSET_DATE_TIME_ENCODING) 60 .map_err(|err| Error::ToSqlConversionFailure(err.into()))?; 61 Ok(ToSqlOutput::from(time_string)) 62 } 63 } 64 65 // Supports parsing formats 2-7 from https://www.sqlite.org/lang_datefunc.html 66 // Formats 2-7 without a timezone assumes UTC 67 impl FromSql for OffsetDateTime { column_result(value: ValueRef<'_>) -> FromSqlResult<Self>68 fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> { 69 value.as_str().and_then(|s| { 70 if let Some(b' ') = s.as_bytes().get(23) { 71 // legacy 72 return OffsetDateTime::parse(s, &LEGACY_DATE_TIME_FORMAT) 73 .map_err(|err| FromSqlError::Other(Box::new(err))); 74 } 75 if s[8..].contains('+') || s[8..].contains('-') { 76 // Formats 2-7 with timezone 77 return OffsetDateTime::parse(s, &OFFSET_DATE_TIME_FORMAT) 78 .map_err(|err| FromSqlError::Other(Box::new(err))); 79 } 80 // Formats 2-7 without timezone 81 PrimitiveDateTime::parse(s, &UTC_DATE_TIME_FORMAT) 82 .map(|p| p.assume_utc()) 83 .map_err(|err| FromSqlError::Other(Box::new(err))) 84 }) 85 } 86 } 87 88 /// ISO 8601 calendar date without timezone => "YYYY-MM-DD" 89 impl ToSql for Date { 90 #[inline] to_sql(&self) -> Result<ToSqlOutput<'_>>91 fn to_sql(&self) -> Result<ToSqlOutput<'_>> { 92 let date_str = self 93 .format(&DATE_FORMAT) 94 .map_err(|err| Error::ToSqlConversionFailure(err.into()))?; 95 Ok(ToSqlOutput::from(date_str)) 96 } 97 } 98 99 /// "YYYY-MM-DD" => ISO 8601 calendar date without timezone. 100 impl FromSql for Date { 101 #[inline] column_result(value: ValueRef<'_>) -> FromSqlResult<Self>102 fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> { 103 value.as_str().and_then(|s| { 104 Date::parse(s, &DATE_FORMAT).map_err(|err| FromSqlError::Other(err.into())) 105 }) 106 } 107 } 108 109 /// ISO 8601 time without timezone => "HH:MM:SS.SSS" 110 impl ToSql for Time { 111 #[inline] to_sql(&self) -> Result<ToSqlOutput<'_>>112 fn to_sql(&self) -> Result<ToSqlOutput<'_>> { 113 let time_str = self 114 .format(&TIME_ENCODING) 115 .map_err(|err| Error::ToSqlConversionFailure(err.into()))?; 116 Ok(ToSqlOutput::from(time_str)) 117 } 118 } 119 120 /// "HH:MM"/"HH:MM:SS"/"HH:MM:SS.SSS" => ISO 8601 time without timezone. 121 impl FromSql for Time { 122 #[inline] column_result(value: ValueRef<'_>) -> FromSqlResult<Self>123 fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> { 124 value.as_str().and_then(|s| { 125 Time::parse(s, &TIME_FORMAT).map_err(|err| FromSqlError::Other(err.into())) 126 }) 127 } 128 } 129 130 /// ISO 8601 combined date and time without timezone => "YYYY-MM-DD HH:MM:SS.SSS" 131 impl ToSql for PrimitiveDateTime { 132 #[inline] to_sql(&self) -> Result<ToSqlOutput<'_>>133 fn to_sql(&self) -> Result<ToSqlOutput<'_>> { 134 let date_time_str = self 135 .format(&PRIMITIVE_DATE_TIME_ENCODING) 136 .map_err(|err| Error::ToSqlConversionFailure(err.into()))?; 137 Ok(ToSqlOutput::from(date_time_str)) 138 } 139 } 140 141 /// YYYY-MM-DD HH:MM 142 /// YYYY-MM-DDTHH:MM 143 /// YYYY-MM-DD HH:MM:SS 144 /// YYYY-MM-DDTHH:MM:SS 145 /// YYYY-MM-DD HH:MM:SS.SSS 146 /// YYYY-MM-DDTHH:MM:SS.SSS 147 /// => ISO 8601 combined date and time with timezone 148 impl FromSql for PrimitiveDateTime { 149 #[inline] column_result(value: ValueRef<'_>) -> FromSqlResult<Self>150 fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> { 151 value.as_str().and_then(|s| { 152 PrimitiveDateTime::parse(s, &PRIMITIVE_DATE_TIME_FORMAT) 153 .map_err(|err| FromSqlError::Other(err.into())) 154 }) 155 } 156 } 157 158 #[cfg(test)] 159 mod test { 160 use crate::{Connection, Result}; 161 use time::macros::{date, datetime, time}; 162 use time::{Date, OffsetDateTime, PrimitiveDateTime, Time}; 163 checked_memory_handle() -> Result<Connection>164 fn checked_memory_handle() -> Result<Connection> { 165 let db = Connection::open_in_memory()?; 166 db.execute_batch("CREATE TABLE foo (t TEXT, i INTEGER, f FLOAT, b BLOB)")?; 167 Ok(db) 168 } 169 170 #[test] test_offset_date_time() -> Result<()>171 fn test_offset_date_time() -> Result<()> { 172 let db = checked_memory_handle()?; 173 174 let mut ts_vec = vec![]; 175 176 let make_datetime = |secs: i128, nanos: i128| { 177 OffsetDateTime::from_unix_timestamp_nanos(1_000_000_000 * secs + nanos).unwrap() 178 }; 179 180 ts_vec.push(make_datetime(10_000, 0)); //January 1, 1970 2:46:40 AM 181 ts_vec.push(make_datetime(10_000, 1000)); //January 1, 1970 2:46:40 AM (and one microsecond) 182 ts_vec.push(make_datetime(1_500_391_124, 1_000_000)); //July 18, 2017 183 ts_vec.push(make_datetime(2_000_000_000, 2_000_000)); //May 18, 2033 184 ts_vec.push(make_datetime(3_000_000_000, 999_999_999)); //January 24, 2065 185 ts_vec.push(make_datetime(10_000_000_000, 0)); //November 20, 2286 186 187 for ts in ts_vec { 188 db.execute("INSERT INTO foo(t) VALUES (?1)", [ts])?; 189 190 let from: OffsetDateTime = db.one_column("SELECT t FROM foo")?; 191 192 db.execute("DELETE FROM foo", [])?; 193 194 assert_eq!(from, ts); 195 } 196 Ok(()) 197 } 198 199 #[test] test_offset_date_time_parsing() -> Result<()>200 fn test_offset_date_time_parsing() -> Result<()> { 201 let db = checked_memory_handle()?; 202 let tests = vec![ 203 // Rfc3339 204 ( 205 "2013-10-07T08:23:19.123456789Z", 206 datetime!(2013-10-07 8:23:19.123456789 UTC), 207 ), 208 ( 209 "2013-10-07 08:23:19.123456789Z", 210 datetime!(2013-10-07 8:23:19.123456789 UTC), 211 ), 212 // Format 2 213 ("2013-10-07 08:23", datetime!(2013-10-07 8:23 UTC)), 214 ("2013-10-07 08:23Z", datetime!(2013-10-07 8:23 UTC)), 215 ("2013-10-07 08:23+04:00", datetime!(2013-10-07 8:23 +4)), 216 // Format 3 217 ("2013-10-07 08:23:19", datetime!(2013-10-07 8:23:19 UTC)), 218 ("2013-10-07 08:23:19Z", datetime!(2013-10-07 8:23:19 UTC)), 219 ( 220 "2013-10-07 08:23:19+04:00", 221 datetime!(2013-10-07 8:23:19 +4), 222 ), 223 // Format 4 224 ( 225 "2013-10-07 08:23:19.123", 226 datetime!(2013-10-07 8:23:19.123 UTC), 227 ), 228 ( 229 "2013-10-07 08:23:19.123Z", 230 datetime!(2013-10-07 8:23:19.123 UTC), 231 ), 232 ( 233 "2013-10-07 08:23:19.123+04:00", 234 datetime!(2013-10-07 8:23:19.123 +4), 235 ), 236 // Format 5 237 ("2013-10-07T08:23", datetime!(2013-10-07 8:23 UTC)), 238 ("2013-10-07T08:23Z", datetime!(2013-10-07 8:23 UTC)), 239 ("2013-10-07T08:23+04:00", datetime!(2013-10-07 8:23 +4)), 240 // Format 6 241 ("2013-10-07T08:23:19", datetime!(2013-10-07 8:23:19 UTC)), 242 ("2013-10-07T08:23:19Z", datetime!(2013-10-07 8:23:19 UTC)), 243 ( 244 "2013-10-07T08:23:19+04:00", 245 datetime!(2013-10-07 8:23:19 +4), 246 ), 247 // Format 7 248 ( 249 "2013-10-07T08:23:19.123", 250 datetime!(2013-10-07 8:23:19.123 UTC), 251 ), 252 ( 253 "2013-10-07T08:23:19.123Z", 254 datetime!(2013-10-07 8:23:19.123 UTC), 255 ), 256 ( 257 "2013-10-07T08:23:19.123+04:00", 258 datetime!(2013-10-07 8:23:19.123 +4), 259 ), 260 // Legacy 261 ( 262 "2013-10-07 08:23:12:987 -07:00", 263 datetime!(2013-10-07 8:23:12.987 -7), 264 ), 265 ]; 266 267 for (s, t) in tests { 268 let result: OffsetDateTime = db.query_row("SELECT ?1", [s], |r| r.get(0))?; 269 assert_eq!(result, t); 270 } 271 Ok(()) 272 } 273 274 #[test] test_date() -> Result<()>275 fn test_date() -> Result<()> { 276 let db = checked_memory_handle()?; 277 let date = date!(2016 - 02 - 23); 278 db.execute("INSERT INTO foo (t) VALUES (?1)", [date])?; 279 280 let s: String = db.one_column("SELECT t FROM foo")?; 281 assert_eq!("2016-02-23", s); 282 let t: Date = db.one_column("SELECT t FROM foo")?; 283 assert_eq!(date, t); 284 Ok(()) 285 } 286 287 #[test] test_time() -> Result<()>288 fn test_time() -> Result<()> { 289 let db = checked_memory_handle()?; 290 let time = time!(23:56:04.00001); 291 db.execute("INSERT INTO foo (t) VALUES (?1)", [time])?; 292 293 let s: String = db.one_column("SELECT t FROM foo")?; 294 assert_eq!("23:56:04.00001", s); 295 let v: Time = db.one_column("SELECT t FROM foo")?; 296 assert_eq!(time, v); 297 Ok(()) 298 } 299 300 #[test] test_primitive_date_time() -> Result<()>301 fn test_primitive_date_time() -> Result<()> { 302 let db = checked_memory_handle()?; 303 let dt = date!(2016 - 02 - 23).with_time(time!(23:56:04)); 304 305 db.execute("INSERT INTO foo (t) VALUES (?1)", [dt])?; 306 307 let s: String = db.one_column("SELECT t FROM foo")?; 308 assert_eq!("2016-02-23 23:56:04.0", s); 309 let v: PrimitiveDateTime = db.one_column("SELECT t FROM foo")?; 310 assert_eq!(dt, v); 311 312 db.execute("UPDATE foo set b = datetime(t)", [])?; // "YYYY-MM-DD HH:MM:SS" 313 let hms: PrimitiveDateTime = db.one_column("SELECT b FROM foo")?; 314 assert_eq!(dt, hms); 315 Ok(()) 316 } 317 318 #[test] test_date_parsing() -> Result<()>319 fn test_date_parsing() -> Result<()> { 320 let db = checked_memory_handle()?; 321 let result: Date = db.query_row("SELECT ?1", ["2013-10-07"], |r| r.get(0))?; 322 assert_eq!(result, date!(2013 - 10 - 07)); 323 Ok(()) 324 } 325 326 #[test] test_time_parsing() -> Result<()>327 fn test_time_parsing() -> Result<()> { 328 let db = checked_memory_handle()?; 329 let tests = vec![ 330 ("08:23", time!(08:23)), 331 ("08:23:19", time!(08:23:19)), 332 ("08:23:19.111", time!(08:23:19.111)), 333 ]; 334 335 for (s, t) in tests { 336 let result: Time = db.query_row("SELECT ?1", [s], |r| r.get(0))?; 337 assert_eq!(result, t); 338 } 339 Ok(()) 340 } 341 342 #[test] test_primitive_date_time_parsing() -> Result<()>343 fn test_primitive_date_time_parsing() -> Result<()> { 344 let db = checked_memory_handle()?; 345 346 let tests = vec![ 347 ("2013-10-07T08:23", datetime!(2013-10-07 8:23)), 348 ("2013-10-07T08:23:19", datetime!(2013-10-07 8:23:19)), 349 ("2013-10-07T08:23:19.111", datetime!(2013-10-07 8:23:19.111)), 350 ("2013-10-07 08:23", datetime!(2013-10-07 8:23)), 351 ("2013-10-07 08:23:19", datetime!(2013-10-07 8:23:19)), 352 ("2013-10-07 08:23:19.111", datetime!(2013-10-07 8:23:19.111)), 353 ]; 354 355 for (s, t) in tests { 356 let result: PrimitiveDateTime = db.query_row("SELECT ?1", [s], |r| r.get(0))?; 357 assert_eq!(result, t); 358 } 359 Ok(()) 360 } 361 362 #[test] test_sqlite_functions() -> Result<()>363 fn test_sqlite_functions() -> Result<()> { 364 let db = checked_memory_handle()?; 365 db.one_column::<Time>("SELECT CURRENT_TIME").unwrap(); 366 db.one_column::<Date>("SELECT CURRENT_DATE").unwrap(); 367 db.one_column::<PrimitiveDateTime>("SELECT CURRENT_TIMESTAMP") 368 .unwrap(); 369 db.one_column::<OffsetDateTime>("SELECT CURRENT_TIMESTAMP") 370 .unwrap(); 371 Ok(()) 372 } 373 374 #[test] test_time_param() -> Result<()>375 fn test_time_param() -> Result<()> { 376 let db = checked_memory_handle()?; 377 let now = OffsetDateTime::now_utc().time(); 378 let result: Result<bool> = db.query_row( 379 "SELECT 1 WHERE ?1 BETWEEN time('now', '-1 minute') AND time('now', '+1 minute')", 380 [now], 381 |r| r.get(0), 382 ); 383 result.unwrap(); 384 Ok(()) 385 } 386 387 #[test] test_date_param() -> Result<()>388 fn test_date_param() -> Result<()> { 389 let db = checked_memory_handle()?; 390 let now = OffsetDateTime::now_utc().date(); 391 let result: Result<bool> = db.query_row( 392 "SELECT 1 WHERE ?1 BETWEEN date('now', '-1 day') AND date('now', '+1 day')", 393 [now], 394 |r| r.get(0), 395 ); 396 result.unwrap(); 397 Ok(()) 398 } 399 400 #[test] test_primitive_date_time_param() -> Result<()>401 fn test_primitive_date_time_param() -> Result<()> { 402 let db = checked_memory_handle()?; 403 let now = PrimitiveDateTime::new( 404 OffsetDateTime::now_utc().date(), 405 OffsetDateTime::now_utc().time(), 406 ); 407 let result: Result<bool> = db.query_row( 408 "SELECT 1 WHERE ?1 BETWEEN datetime('now', '-1 minute') AND datetime('now', '+1 minute')", 409 [now], 410 |r| r.get(0), 411 ); 412 result.unwrap(); 413 Ok(()) 414 } 415 416 #[test] test_offset_date_time_param() -> Result<()>417 fn test_offset_date_time_param() -> Result<()> { 418 let db = checked_memory_handle()?; 419 let result: Result<bool> = db.query_row( 420 "SELECT 1 WHERE ?1 BETWEEN datetime('now', '-1 minute') AND datetime('now', '+1 minute')", 421 [OffsetDateTime::now_utc()], 422 |r| r.get(0), 423 ); 424 result.unwrap(); 425 Ok(()) 426 } 427 } 428