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

rusqlite/types/
time.rs
