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};
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(|p| p.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
144/// YYYY-MM-DD HH:MM
145/// YYYY-MM-DDTHH:MM
146/// YYYY-MM-DD HH:MM:SS
147/// YYYY-MM-DDTHH:MM:SS
148/// YYYY-MM-DD HH:MM:SS.SSS
149/// YYYY-MM-DDTHH:MM:SS.SSS
150/// => ISO 8601 combined date and time with timezone
151impl FromSql for PrimitiveDateTime {
152    #[inline]
153    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
154        value.as_str().and_then(|s| {
155            Self::parse(s, &PRIMITIVE_DATE_TIME_FORMAT)
156                .map_err(|err| FromSqlError::Other(err.into()))
157        })
158    }
159}
160
161#[cfg(test)]
162mod test {
163    #[cfg(all(target_family = "wasm", target_os = "unknown"))]
164    use wasm_bindgen_test::wasm_bindgen_test as test;
165
166    use crate::{Connection, Result};
167    use time::macros::{date, datetime, time};
168    use time::{Date, OffsetDateTime, PrimitiveDateTime, Time};
169
170    fn checked_memory_handle() -> Result<Connection> {
171        let db = Connection::open_in_memory()?;
172        db.execute_batch("CREATE TABLE foo (t TEXT, i INTEGER AS (strftime('%s', t)), b BLOB)")?;
173        Ok(db)
174    }
175
176    #[test]
177    fn test_offset_date_time() -> Result<()> {
178        let db = checked_memory_handle()?;
179
180        let mut ts_vec = vec![];
181
182        let make_datetime = |secs: i128, nanos: i128| {
183            OffsetDateTime::from_unix_timestamp_nanos(1_000_000_000 * secs + nanos).unwrap()
184        };
185
186        ts_vec.push(make_datetime(10_000, 0)); //January 1, 1970 2:46:40 AM
187        ts_vec.push(make_datetime(10_000, 1000)); //January 1, 1970 2:46:40 AM (and one microsecond)
188        ts_vec.push(make_datetime(1_500_391_124, 1_000_000)); //July 18, 2017
189        ts_vec.push(make_datetime(2_000_000_000, 2_000_000)); //May 18, 2033
190        ts_vec.push(make_datetime(3_000_000_000, 999_999_999)); //January 24, 2065
191        ts_vec.push(make_datetime(10_000_000_000, 0)); //November 20, 2286
192
193        for ts in ts_vec {
194            db.execute("INSERT INTO foo(t) VALUES (?1)", [ts])?;
195
196            let from: OffsetDateTime = db.one_column("SELECT t FROM foo", [])?;
197            assert_eq!(from, ts);
198
199            let from: OffsetDateTime = db.one_column("SELECT i FROM foo", [])?;
200            assert_eq!(from, ts.truncate_to_second());
201
202            db.execute("DELETE FROM foo", [])?;
203        }
204        Ok(())
205    }
206
207    #[test]
208    fn test_offset_date_time_parsing() -> Result<()> {
209        let db = checked_memory_handle()?;
210        let tests = vec![
211            // Rfc3339
212            (
213                "2013-10-07T08:23:19.123456789Z",
214                datetime!(2013-10-07 8:23:19.123456789 UTC),
215            ),
216            (
217                "2013-10-07 08:23:19.123456789Z",
218                datetime!(2013-10-07 8:23:19.123456789 UTC),
219            ),
220            // Format 2
221            ("2013-10-07 08:23", datetime!(2013-10-07 8:23 UTC)),
222            ("2013-10-07 08:23Z", datetime!(2013-10-07 8:23 UTC)),
223            ("2013-10-07 08:23+04:00", datetime!(2013-10-07 8:23 +4)),
224            // Format 3
225            ("2013-10-07 08:23:19", datetime!(2013-10-07 8:23:19 UTC)),
226            ("2013-10-07 08:23:19Z", datetime!(2013-10-07 8:23:19 UTC)),
227            (
228                "2013-10-07 08:23:19+04:00",
229                datetime!(2013-10-07 8:23:19 +4),
230            ),
231            // Format 4
232            (
233                "2013-10-07 08:23:19.123",
234                datetime!(2013-10-07 8:23:19.123 UTC),
235            ),
236            (
237                "2013-10-07 08:23:19.123Z",
238                datetime!(2013-10-07 8:23:19.123 UTC),
239            ),
240            (
241                "2013-10-07 08:23:19.123+04:00",
242                datetime!(2013-10-07 8:23:19.123 +4),
243            ),
244            // Format 5
245            ("2013-10-07T08:23", datetime!(2013-10-07 8:23 UTC)),
246            ("2013-10-07T08:23Z", datetime!(2013-10-07 8:23 UTC)),
247            ("2013-10-07T08:23+04:00", datetime!(2013-10-07 8:23 +4)),
248            // Format 6
249            ("2013-10-07T08:23:19", datetime!(2013-10-07 8:23:19 UTC)),
250            ("2013-10-07T08:23:19Z", datetime!(2013-10-07 8:23:19 UTC)),
251            (
252                "2013-10-07T08:23:19+04:00",
253                datetime!(2013-10-07 8:23:19 +4),
254            ),
255            // Format 7
256            (
257                "2013-10-07T08:23:19.123",
258                datetime!(2013-10-07 8:23:19.123 UTC),
259            ),
260            (
261                "2013-10-07T08:23:19.123Z",
262                datetime!(2013-10-07 8:23:19.123 UTC),
263            ),
264            (
265                "2013-10-07T08:23:19.123+04:00",
266                datetime!(2013-10-07 8:23:19.123 +4),
267            ),
268            // Legacy
269            (
270                "2013-10-07 08:23:12:987 -07:00",
271                datetime!(2013-10-07 8:23:12.987 -7),
272            ),
273        ];
274
275        for (s, t) in tests {
276            let result: OffsetDateTime = db.one_column("SELECT ?1", [s])?;
277            assert_eq!(result, t);
278        }
279        Ok(())
280    }
281
282    #[test]
283    fn test_date() -> Result<()> {
284        let db = checked_memory_handle()?;
285        let date = date!(2016 - 02 - 23);
286        db.execute("INSERT INTO foo (t) VALUES (?1)", [date])?;
287
288        let s: String = db.one_column("SELECT t FROM foo", [])?;
289        assert_eq!("2016-02-23", s);
290        let t: Date = db.one_column("SELECT t FROM foo", [])?;
291        assert_eq!(date, t);
292        Ok(())
293    }
294
295    #[test]
296    fn test_time() -> Result<()> {
297        let db = checked_memory_handle()?;
298        let time = time!(23:56:04.00001);
299        db.execute("INSERT INTO foo (t) VALUES (?1)", [time])?;
300
301        let s: String = db.one_column("SELECT t FROM foo", [])?;
302        assert_eq!("23:56:04.00001", s);
303        let v: Time = db.one_column("SELECT t FROM foo", [])?;
304        assert_eq!(time, v);
305        Ok(())
306    }
307
308    #[test]
309    fn test_primitive_date_time() -> Result<()> {
310        let db = checked_memory_handle()?;
311        let dt = date!(2016 - 02 - 23).with_time(time!(23:56:04));
312
313        db.execute("INSERT INTO foo (t) VALUES (?1)", [dt])?;
314
315        let s: String = db.one_column("SELECT t FROM foo", [])?;
316        assert_eq!("2016-02-23 23:56:04.0", s);
317        let v: PrimitiveDateTime = db.one_column("SELECT t FROM foo", [])?;
318        assert_eq!(dt, v);
319
320        db.execute("UPDATE foo set b = datetime(t)", [])?; // "YYYY-MM-DD HH:MM:SS"
321        let hms: PrimitiveDateTime = db.one_column("SELECT b FROM foo", [])?;
322        assert_eq!(dt, hms);
323        Ok(())
324    }
325
326    #[test]
327    fn test_date_parsing() -> Result<()> {
328        let db = checked_memory_handle()?;
329        let result: Date = db.one_column("SELECT ?1", ["2013-10-07"])?;
330        assert_eq!(result, date!(2013 - 10 - 07));
331        Ok(())
332    }
333
334    #[test]
335    fn test_time_parsing() -> Result<()> {
336        let db = checked_memory_handle()?;
337        let tests = vec![
338            ("08:23", time!(08:23)),
339            ("08:23:19", time!(08:23:19)),
340            ("08:23:19.111", time!(08:23:19.111)),
341        ];
342
343        for (s, t) in tests {
344            let result: Time = db.one_column("SELECT ?1", [s])?;
345            assert_eq!(result, t);
346        }
347        Ok(())
348    }
349
350    #[test]
351    fn test_primitive_date_time_parsing() -> Result<()> {
352        let db = checked_memory_handle()?;
353
354        let tests = vec![
355            ("2013-10-07T08:23", datetime!(2013-10-07 8:23)),
356            ("2013-10-07T08:23:19", datetime!(2013-10-07 8:23:19)),
357            ("2013-10-07T08:23:19.111", datetime!(2013-10-07 8:23:19.111)),
358            ("2013-10-07 08:23", datetime!(2013-10-07 8:23)),
359            ("2013-10-07 08:23:19", datetime!(2013-10-07 8:23:19)),
360            ("2013-10-07 08:23:19.111", datetime!(2013-10-07 8:23:19.111)),
361        ];
362
363        for (s, t) in tests {
364            let result: PrimitiveDateTime = db.one_column("SELECT ?1", [s])?;
365            assert_eq!(result, t);
366        }
367        Ok(())
368    }
369
370    #[test]
371    fn test_sqlite_functions() -> Result<()> {
372        let db = checked_memory_handle()?;
373        db.one_column::<Time, _>("SELECT CURRENT_TIME", [])?;
374        db.one_column::<Date, _>("SELECT CURRENT_DATE", [])?;
375        db.one_column::<PrimitiveDateTime, _>("SELECT CURRENT_TIMESTAMP", [])?;
376        db.one_column::<OffsetDateTime, _>("SELECT CURRENT_TIMESTAMP", [])?;
377        Ok(())
378    }
379
380    #[test]
381    fn test_time_param() -> Result<()> {
382        let db = checked_memory_handle()?;
383        let now = OffsetDateTime::now_utc().time();
384        let result: Result<bool> = db.one_column(
385            "SELECT 1 WHERE ?1 BETWEEN time('now', '-1 minute') AND time('now', '+1 minute')",
386            [now],
387        );
388        result?;
389        Ok(())
390    }
391
392    #[test]
393    fn test_date_param() -> Result<()> {
394        let db = checked_memory_handle()?;
395        let now = OffsetDateTime::now_utc().date();
396        let result: Result<bool> = db.one_column(
397            "SELECT 1 WHERE ?1 BETWEEN date('now', '-1 day') AND date('now', '+1 day')",
398            [now],
399        );
400        result?;
401        Ok(())
402    }
403
404    #[test]
405    fn test_primitive_date_time_param() -> Result<()> {
406        let db = checked_memory_handle()?;
407        let now = PrimitiveDateTime::new(
408            OffsetDateTime::now_utc().date(),
409            OffsetDateTime::now_utc().time(),
410        );
411        let result: Result<bool> = db.one_column(
412            "SELECT 1 WHERE ?1 BETWEEN datetime('now', '-1 minute') AND datetime('now', '+1 minute')",
413            [now],
414        );
415        result?;
416        Ok(())
417    }
418
419    #[test]
420    fn test_offset_date_time_param() -> Result<()> {
421        let db = checked_memory_handle()?;
422        let result: Result<bool> = db.one_column(
423            "SELECT 1 WHERE ?1 BETWEEN datetime('now', '-1 minute') AND datetime('now', '+1 minute')",
424            [OffsetDateTime::now_utc()],
425        );
426        result?;
427        Ok(())
428    }
429}
rusqlite/types/time.rs

rusqlite/types/
time.rs
