1use crate::types::{FromSql, FromSqlError, FromSqlResult, ToSql, ToSqlOutput, Type, ValueRef};
16use crate::{Error, Result};
17use time::format_description::FormatItem;
18use time::macros::format_description;
19use time::{Date, OffsetDateTime, PrimitiveDateTime, Time, UtcDateTime};
20
21const 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);
25const PRIMITIVE_DATE_TIME_ENCODING: &[FormatItem<'_>] = format_description!(
26 version = 2,
27 "[year]-[month]-[day] [hour]:[minute]:[second].[subsecond]"
28);
29const TIME_ENCODING: &[FormatItem<'_>] =
30 format_description!(version = 2, "[hour]:[minute]:[second].[subsecond]");
31
32const DATE_FORMAT: &[FormatItem<'_>] = format_description!(version = 2, "[year]-[month]-[day]");
33const TIME_FORMAT: &[FormatItem<'_>] = format_description!(
34 version = 2,
35 "[hour]:[minute][optional [:[second][optional [.[subsecond]]]]]"
36);
37const PRIMITIVE_DATE_TIME_FORMAT: &[FormatItem<'_>] = format_description!(
38 version = 2,
39 "[year]-[month]-[day][first [ ][T]][hour]:[minute][optional [:[second][optional [.[subsecond]]]]]"
40);
41const 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);
45const 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);
49const 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
54impl ToSql for OffsetDateTime {
56 #[inline]
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
65impl FromSql for OffsetDateTime {
68 fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
69 if value.data_type() == Type::Integer {
70 return value
71 .as_i64()
72 .and_then(|i| OffsetDateTime::from_unix_timestamp(i).map_err(FromSqlError::other));
73 }
74 value.as_str().and_then(|s| {
75 if let Some(b' ') = s.as_bytes().get(23) {
76 return Self::parse(s, &LEGACY_DATE_TIME_FORMAT).map_err(FromSqlError::other);
78 }
79 if s[8..].contains('+') || s[8..].contains('-') {
80 return Self::parse(s, &OFFSET_DATE_TIME_FORMAT).map_err(FromSqlError::other);
82 }
83 PrimitiveDateTime::parse(s, &UTC_DATE_TIME_FORMAT)
85 .map(PrimitiveDateTime::assume_utc)
86 .map_err(FromSqlError::other)
87 })
88 }
89}
90
91impl ToSql for Date {
93 #[inline]
94 fn to_sql(&self) -> Result<ToSqlOutput<'_>> {
95 let date_str = self
96 .format(&DATE_FORMAT)
97 .map_err(|err| Error::ToSqlConversionFailure(err.into()))?;
98 Ok(ToSqlOutput::from(date_str))
99 }
100}
101
102impl FromSql for Date {
104 #[inline]
105 fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
106 value.as_str().and_then(|s| {
107 Self::parse(s, &DATE_FORMAT).map_err(|err| FromSqlError::Other(err.into()))
108 })
109 }
110}
111
112impl ToSql for Time {
114 #[inline]
115 fn to_sql(&self) -> Result<ToSqlOutput<'_>> {
116 let time_str = self
117 .format(&TIME_ENCODING)
118 .map_err(|err| Error::ToSqlConversionFailure(err.into()))?;
119 Ok(ToSqlOutput::from(time_str))
120 }
121}
122
123impl FromSql for Time {
125 #[inline]
126 fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
127 value.as_str().and_then(|s| {
128 Self::parse(s, &TIME_FORMAT).map_err(|err| FromSqlError::Other(err.into()))
129 })
130 }
131}
132
133impl ToSql for PrimitiveDateTime {
135 #[inline]
136 fn to_sql(&self) -> Result<ToSqlOutput<'_>> {
137 let date_time_str = self
138 .format(&PRIMITIVE_DATE_TIME_ENCODING)
139 .map_err(|err| Error::ToSqlConversionFailure(err.into()))?;
140 Ok(ToSqlOutput::from(date_time_str))
141 }
142}
143
144impl ToSql for UtcDateTime {
145 #[inline]
146 fn to_sql(&self) -> Result<ToSqlOutput<'_>> {
147 let date_time_str = self
148 .format(&UTC_DATE_TIME_FORMAT)
149 .map_err(|err| Error::ToSqlConversionFailure(err.into()))?;
150 Ok(ToSqlOutput::from(date_time_str))
151 }
152}
153
154impl FromSql for PrimitiveDateTime {
162 #[inline]
163 fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
164 value.as_str().and_then(|s| {
165 Self::parse(s, &PRIMITIVE_DATE_TIME_FORMAT)
166 .map_err(|err| FromSqlError::Other(err.into()))
167 })
168 }
169}
170
171impl FromSql for UtcDateTime {
172 #[inline]
173 fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
174 value.as_str().and_then(|s| {
175 Self::parse(s, &UTC_DATE_TIME_FORMAT).map_err(|err| FromSqlError::Other(err.into()))
176 })
177 }
178}
179
180#[cfg(all(test, not(miri)))]
181mod test {
182 #[cfg(all(target_family = "wasm", target_os = "unknown"))]
183 use wasm_bindgen_test::wasm_bindgen_test as test;
184
185 use crate::{Connection, Result, ToSql};
186 use time::macros::{date, datetime, time, utc_datetime};
187 use time::{Date, OffsetDateTime, PrimitiveDateTime, Time, UtcDateTime};
188
189 fn checked_memory_handle() -> Result<Connection> {
190 let db = Connection::open_in_memory()?;
191 db.execute_batch("CREATE TABLE foo (t TEXT, i INTEGER AS (strftime('%s', t)), b BLOB)")?;
192 Ok(db)
193 }
194
195 #[test]
196 fn test_offset_date_time() -> Result<()> {
197 let db = checked_memory_handle()?;
198
199 let mut ts_vec = vec![];
200
201 let make_datetime = |secs: i128, nanos: i128| {
202 OffsetDateTime::from_unix_timestamp_nanos(1_000_000_000 * secs + nanos).unwrap()
203 };
204
205 ts_vec.push(make_datetime(10_000, 0)); ts_vec.push(make_datetime(10_000, 1000)); ts_vec.push(make_datetime(1_500_391_124, 1_000_000)); ts_vec.push(make_datetime(2_000_000_000, 2_000_000)); ts_vec.push(make_datetime(3_000_000_000, 999_999_999)); ts_vec.push(make_datetime(10_000_000_000, 0)); for ts in ts_vec {
213 db.execute("INSERT INTO foo(t) VALUES (?1)", [ts])?;
214
215 let from: OffsetDateTime = db.one_column("SELECT t FROM foo", [])?;
216 assert_eq!(from, ts);
217
218 let from: OffsetDateTime = db.one_column("SELECT i FROM foo", [])?;
219 assert_eq!(from, ts.truncate_to_second());
220
221 db.execute("DELETE FROM foo", [])?;
222 }
223 Ok(())
224 }
225
226 #[test]
227 fn test_offset_date_time_parsing() -> Result<()> {
228 let db = checked_memory_handle()?;
229 let tests = vec![
230 (
232 "2013-10-07T08:23:19.123456789Z",
233 datetime!(2013-10-07 8:23:19.123456789 UTC),
234 ),
235 (
236 "2013-10-07 08:23:19.123456789Z",
237 datetime!(2013-10-07 8:23:19.123456789 UTC),
238 ),
239 ("2013-10-07 08:23", datetime!(2013-10-07 8:23 UTC)),
241 ("2013-10-07 08:23Z", datetime!(2013-10-07 8:23 UTC)),
242 ("2013-10-07 08:23+04:00", datetime!(2013-10-07 8:23 +4)),
243 ("2013-10-07 08:23:19", datetime!(2013-10-07 8:23:19 UTC)),
245 ("2013-10-07 08:23:19Z", datetime!(2013-10-07 8:23:19 UTC)),
246 (
247 "2013-10-07 08:23:19+04:00",
248 datetime!(2013-10-07 8:23:19 +4),
249 ),
250 (
252 "2013-10-07 08:23:19.123",
253 datetime!(2013-10-07 8:23:19.123 UTC),
254 ),
255 (
256 "2013-10-07 08:23:19.123Z",
257 datetime!(2013-10-07 8:23:19.123 UTC),
258 ),
259 (
260 "2013-10-07 08:23:19.123+04:00",
261 datetime!(2013-10-07 8:23:19.123 +4),
262 ),
263 ("2013-10-07T08:23", datetime!(2013-10-07 8:23 UTC)),
265 ("2013-10-07T08:23Z", datetime!(2013-10-07 8:23 UTC)),
266 ("2013-10-07T08:23+04:00", datetime!(2013-10-07 8:23 +4)),
267 ("2013-10-07T08:23:19", datetime!(2013-10-07 8:23:19 UTC)),
269 ("2013-10-07T08:23:19Z", datetime!(2013-10-07 8:23:19 UTC)),
270 (
271 "2013-10-07T08:23:19+04:00",
272 datetime!(2013-10-07 8:23:19 +4),
273 ),
274 (
276 "2013-10-07T08:23:19.123",
277 datetime!(2013-10-07 8:23:19.123 UTC),
278 ),
279 (
280 "2013-10-07T08:23:19.123Z",
281 datetime!(2013-10-07 8:23:19.123 UTC),
282 ),
283 (
284 "2013-10-07T08:23:19.123+04:00",
285 datetime!(2013-10-07 8:23:19.123 +4),
286 ),
287 (
289 "2013-10-07 08:23:12:987 -07:00",
290 datetime!(2013-10-07 8:23:12.987 -7),
291 ),
292 ];
293
294 for (s, t) in tests {
295 let result: OffsetDateTime = db.one_column("SELECT ?1", [s])?;
296 assert_eq!(result, t);
297 }
298 Ok(())
299 }
300
301 #[test]
302 fn test_date() -> Result<()> {
303 let db = checked_memory_handle()?;
304 let date = date!(2016 - 02 - 23);
305 db.execute("INSERT INTO foo (t) VALUES (?1)", [date])?;
306
307 let s: String = db.one_column("SELECT t FROM foo", [])?;
308 assert_eq!("2016-02-23", s);
309 let t: Date = db.one_column("SELECT t FROM foo", [])?;
310 assert_eq!(date, t);
311 Ok(())
312 }
313
314 #[test]
315 fn test_time() -> Result<()> {
316 let db = checked_memory_handle()?;
317 let time = time!(23:56:04.00001);
318 db.execute("INSERT INTO foo (t) VALUES (?1)", [time])?;
319
320 let s: String = db.one_column("SELECT t FROM foo", [])?;
321 assert_eq!("23:56:04.00001", s);
322 let v: Time = db.one_column("SELECT t FROM foo", [])?;
323 assert_eq!(time, v);
324 Ok(())
325 }
326
327 #[test]
328 fn test_primitive_date_time() -> Result<()> {
329 let db = checked_memory_handle()?;
330 let dt = date!(2016 - 02 - 23).with_time(time!(23:56:04));
331
332 db.execute("INSERT INTO foo (t) VALUES (?1)", [dt])?;
333
334 let s: String = db.one_column("SELECT t FROM foo", [])?;
335 assert_eq!("2016-02-23 23:56:04.0", s);
336 let v: PrimitiveDateTime = db.one_column("SELECT t FROM foo", [])?;
337 assert_eq!(dt, v);
338
339 db.execute("UPDATE foo set b = datetime(t)", [])?; let hms: PrimitiveDateTime = db.one_column("SELECT b FROM foo", [])?;
341 assert_eq!(dt, hms);
342 Ok(())
343 }
344
345 #[test]
346 fn test_utc_date_time() -> Result<()> {
347 let db = checked_memory_handle()?;
348 let dt = UtcDateTime::new(date!(2016 - 02 - 23), time!(23:56:04));
349
350 db.execute("INSERT INTO foo (t) VALUES (?1)", [dt])?;
351
352 let s: String = db.one_column("SELECT t FROM foo", [])?;
353 assert_eq!("2016-02-23 23:56:04.0Z", s);
354 let v: UtcDateTime = db.one_column("SELECT t FROM foo", [])?;
355 assert_eq!(dt, v);
356
357 db.execute("UPDATE foo set b = datetime(t)", [])?; let hms: UtcDateTime = db.one_column("SELECT b FROM foo", [])?;
359 assert_eq!(dt, hms);
360 Ok(())
361 }
362
363 #[test]
364 fn test_date_parsing() -> Result<()> {
365 let db = checked_memory_handle()?;
366 let result: Date = db.one_column("SELECT ?1", ["2013-10-07"])?;
367 assert_eq!(result, date!(2013 - 10 - 07));
368 Ok(())
369 }
370
371 #[test]
372 fn test_time_parsing() -> Result<()> {
373 let db = checked_memory_handle()?;
374 let tests = vec![
375 ("08:23", time!(08:23)),
376 ("08:23:19", time!(08:23:19)),
377 ("08:23:19.111", time!(08:23:19.111)),
378 ];
379
380 for (s, t) in tests {
381 let result: Time = db.one_column("SELECT ?1", [s])?;
382 assert_eq!(result, t);
383 }
384 Ok(())
385 }
386
387 #[test]
388 fn test_primitive_date_time_parsing() -> Result<()> {
389 let db = checked_memory_handle()?;
390
391 let tests = vec![
392 ("2013-10-07T08:23", datetime!(2013-10-07 8:23)),
393 ("2013-10-07T08:23:19", datetime!(2013-10-07 8:23:19)),
394 ("2013-10-07T08:23:19.111", datetime!(2013-10-07 8:23:19.111)),
395 ("2013-10-07 08:23", datetime!(2013-10-07 8:23)),
396 ("2013-10-07 08:23:19", datetime!(2013-10-07 8:23:19)),
397 ("2013-10-07 08:23:19.111", datetime!(2013-10-07 8:23:19.111)),
398 ];
399
400 for (s, t) in tests {
401 let result: PrimitiveDateTime = db.one_column("SELECT ?1", [s])?;
402 assert_eq!(result, t);
403 }
404 Ok(())
405 }
406
407 #[test]
408 fn test_utc_date_time_parsing() -> Result<()> {
409 let db = checked_memory_handle()?;
410
411 let tests = vec![
412 ("2013-10-07T08:23", utc_datetime!(2013-10-07 8:23)),
413 ("2013-10-07T08:23:19", utc_datetime!(2013-10-07 8:23:19)),
414 (
415 "2013-10-07T08:23:19.111",
416 utc_datetime!(2013-10-07 8:23:19.111),
417 ),
418 ("2013-10-07 08:23", utc_datetime!(2013-10-07 8:23)),
419 ("2013-10-07 08:23:19", utc_datetime!(2013-10-07 8:23:19)),
420 (
421 "2013-10-07 08:23:19.111",
422 utc_datetime!(2013-10-07 8:23:19.111),
423 ),
424 ];
425
426 for (s, t) in tests {
427 let result: UtcDateTime = db.one_column("SELECT ?1", [s])?;
428 assert_eq!(result, t);
429 }
430 Ok(())
431 }
432
433 #[test]
434 fn test_sqlite_functions() -> Result<()> {
435 let db = checked_memory_handle()?;
436 db.one_column::<Time, _>("SELECT CURRENT_TIME", [])?;
437 db.one_column::<Date, _>("SELECT CURRENT_DATE", [])?;
438 db.one_column::<PrimitiveDateTime, _>("SELECT CURRENT_TIMESTAMP", [])?;
439 db.one_column::<UtcDateTime, _>("SELECT CURRENT_TIMESTAMP", [])?;
440 db.one_column::<OffsetDateTime, _>("SELECT CURRENT_TIMESTAMP", [])?;
441 Ok(())
442 }
443
444 #[test]
445 fn test_time_param() -> Result<()> {
446 let db = checked_memory_handle()?;
447 let now = OffsetDateTime::now_utc().time();
448 let result: Result<bool> = db.one_column(
449 "SELECT 1 WHERE ?1 BETWEEN time('now', '-1 minute') AND time('now', '+1 minute')",
450 [now],
451 );
452 result?;
453 Ok(())
454 }
455
456 #[test]
457 fn test_date_param() -> Result<()> {
458 let db = checked_memory_handle()?;
459 let now = OffsetDateTime::now_utc().date();
460 let result: Result<bool> = db.one_column(
461 "SELECT 1 WHERE ?1 BETWEEN date('now', '-1 day') AND date('now', '+1 day')",
462 [now],
463 );
464 result?;
465 Ok(())
466 }
467
468 #[test]
469 fn test_primitive_date_time_param() -> Result<()> {
470 test_param(PrimitiveDateTime::new(
471 OffsetDateTime::now_utc().date(),
472 OffsetDateTime::now_utc().time(),
473 ))
474 }
475
476 #[test]
477 fn test_utc_date_time_param() -> Result<()> {
478 test_param(UtcDateTime::now())
479 }
480
481 #[test]
482 fn test_offset_date_time_param() -> Result<()> {
483 test_param(OffsetDateTime::now_utc())
484 }
485
486 fn test_param<P: ToSql>(p: P) -> Result<()> {
487 let db = checked_memory_handle()?;
488 let result: Result<bool> = db.one_column(
489 "SELECT 1 WHERE ?1 BETWEEN datetime('now', '-1 minute') AND datetime('now', '+1 minute')",
490 [p],
491 );
492 result?;
493 Ok(())
494 }
495}