Skip to main content

rusqlite/types/
time.rs

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 [`OffsetDateTime`].
3//! [`ToSql`] and [`FromSql`] implementation for [`PrimitiveDateTime`].
4//! [`ToSql`] and [`FromSql`] implementation for [`Date`].
5//! [`ToSql`] and [`FromSql`] implementation for [`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
15use 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
54/// `OffsetDatetime` => RFC3339 format ("YYYY-MM-DD HH:MM:SS.SSS[+-]HH:MM")
55impl 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
65// Supports parsing formats 2-7 and 12 (unix timestamp) from https://www.sqlite.org/lang_datefunc.html
66// Formats 2-7 without a timezone assumes UTC
67impl 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                // legacy
77                return Self::parse(s, &LEGACY_DATE_TIME_FORMAT).map_err(FromSqlError::other);
78            }
79            if s[8..].contains('+') || s[8..].contains('-') {
80                // Formats 2-7 with timezone
81                return Self::parse(s, &OFFSET_DATE_TIME_FORMAT).map_err(FromSqlError::other);
82            }
83            // Formats 2-7 without timezone
84            PrimitiveDateTime::parse(s, &UTC_DATE_TIME_FORMAT)
85                .map(PrimitiveDateTime::assume_utc)
86                .map_err(FromSqlError::other)
87        })
88    }
89}
90
91/// ISO 8601 calendar date without timezone => "YYYY-MM-DD"
92impl 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
102/// "YYYY-MM-DD" => ISO 8601 calendar date without timezone.
103impl 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
112/// ISO 8601 time without timezone => "HH:MM:SS.SSS"
113impl 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
123/// "HH:MM"/"HH:MM:SS"/"HH:MM:SS.SSS" => ISO 8601 time without timezone.
124impl 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
133/// ISO 8601 combined date and time without timezone => "YYYY-MM-DD HH:MM:SS.SSS"
134impl 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
154/// YYYY-MM-DD HH:MM
155/// YYYY-MM-DDTHH:MM
156/// YYYY-MM-DD HH:MM:SS
157/// YYYY-MM-DDTHH:MM:SS
158/// YYYY-MM-DD HH:MM:SS.SSS
159/// YYYY-MM-DDTHH:MM:SS.SSS
160/// => ISO 8601 combined date and time with timezone
161impl 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)); //January 1, 1970 2:46:40 AM
206        ts_vec.push(make_datetime(10_000, 1000)); //January 1, 1970 2:46:40 AM (and one microsecond)
207        ts_vec.push(make_datetime(1_500_391_124, 1_000_000)); //July 18, 2017
208        ts_vec.push(make_datetime(2_000_000_000, 2_000_000)); //May 18, 2033
209        ts_vec.push(make_datetime(3_000_000_000, 999_999_999)); //January 24, 2065
210        ts_vec.push(make_datetime(10_000_000_000, 0)); //November 20, 2286
211
212        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            // Rfc3339
231            (
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            // Format 2
240            ("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            // Format 3
244            ("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            // Format 4
251            (
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            // Format 5
264            ("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            // Format 6
268            ("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            // Format 7
275            (
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            // Legacy
288            (
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)", [])?; // "YYYY-MM-DD HH:MM:SS"
340        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)", [])?; // "YYYY-MM-DD HH:MM:SS"
358        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}