rusqlite/types/
time.rs

1//! [`ToSql`] and [`FromSql`] implementation for [`time::OffsetDateTime`].
2use crate::types::{FromSql, FromSqlError, FromSqlResult, ToSql, ToSqlOutput, ValueRef};
3use crate::{Error, Result};
4use time::format_description::well_known::Rfc3339;
5use time::format_description::FormatItem;
6use time::macros::format_description;
7use time::{OffsetDateTime, PrimitiveDateTime, UtcOffset};
8
9const PRIMITIVE_SHORT_DATE_TIME_FORMAT: &[FormatItem<'_>] =
10    format_description!("[year]-[month]-[day] [hour]:[minute]:[second]");
11const PRIMITIVE_DATE_TIME_FORMAT: &[FormatItem<'_>] =
12    format_description!("[year]-[month]-[day] [hour]:[minute]:[second].[subsecond]");
13const PRIMITIVE_DATE_TIME_Z_FORMAT: &[FormatItem<'_>] =
14    format_description!("[year]-[month]-[day] [hour]:[minute]:[second].[subsecond]Z");
15const OFFSET_SHORT_DATE_TIME_FORMAT: &[FormatItem<'_>] = format_description!(
16    "[year]-[month]-[day] [hour]:[minute]:[second][offset_hour sign:mandatory]:[offset_minute]"
17);
18const OFFSET_DATE_TIME_FORMAT: &[FormatItem<'_>] = format_description!(
19    "[year]-[month]-[day] [hour]:[minute]:[second].[subsecond][offset_hour sign:mandatory]:[offset_minute]"
20);
21const LEGACY_DATE_TIME_FORMAT: &[FormatItem<'_>] = format_description!(
22    "[year]-[month]-[day] [hour]:[minute]:[second]:[subsecond] [offset_hour sign:mandatory]:[offset_minute]"
23);
24
25impl ToSql for OffsetDateTime {
26    #[inline]
27    fn to_sql(&self) -> Result<ToSqlOutput<'_>> {
28        // FIXME keep original offset
29        let time_string = self
30            .to_offset(UtcOffset::UTC)
31            .format(&PRIMITIVE_DATE_TIME_Z_FORMAT)
32            .map_err(|err| Error::ToSqlConversionFailure(err.into()))?;
33        Ok(ToSqlOutput::from(time_string))
34    }
35}
36
37impl FromSql for OffsetDateTime {
38    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
39        value.as_str().and_then(|s| {
40            if s.len() > 10 && s.as_bytes()[10] == b'T' {
41                // YYYY-MM-DDTHH:MM:SS.SSS[+-]HH:MM
42                return OffsetDateTime::parse(s, &Rfc3339)
43                    .map_err(|err| FromSqlError::Other(Box::new(err)));
44            }
45            let s = s.strip_suffix('Z').unwrap_or(s);
46            match s.len() {
47                len if len <= 19 => {
48                    // TODO YYYY-MM-DDTHH:MM:SS
49                    PrimitiveDateTime::parse(s, &PRIMITIVE_SHORT_DATE_TIME_FORMAT)
50                        .map(PrimitiveDateTime::assume_utc)
51                }
52                _ if s.as_bytes()[19] == b':' => {
53                    // legacy
54                    OffsetDateTime::parse(s, &LEGACY_DATE_TIME_FORMAT)
55                }
56                _ if s.as_bytes()[19] == b'.' => OffsetDateTime::parse(s, &OFFSET_DATE_TIME_FORMAT)
57                    .or_else(|err| {
58                        PrimitiveDateTime::parse(s, &PRIMITIVE_DATE_TIME_FORMAT)
59                            .map(PrimitiveDateTime::assume_utc)
60                            .map_err(|_| err)
61                    }),
62                _ => OffsetDateTime::parse(s, &OFFSET_SHORT_DATE_TIME_FORMAT),
63            }
64            .map_err(|err| FromSqlError::Other(Box::new(err)))
65        })
66    }
67}
68
69#[cfg(test)]
70mod test {
71    use crate::{Connection, Result};
72    use time::format_description::well_known::Rfc3339;
73    use time::OffsetDateTime;
74
75    #[test]
76    fn test_offset_date_time() -> Result<()> {
77        let db = Connection::open_in_memory()?;
78        db.execute_batch("CREATE TABLE foo (t TEXT, i INTEGER, f FLOAT)")?;
79
80        let mut ts_vec = vec![];
81
82        let make_datetime = |secs: i128, nanos: i128| {
83            OffsetDateTime::from_unix_timestamp_nanos(1_000_000_000 * secs + nanos).unwrap()
84        };
85
86        ts_vec.push(make_datetime(10_000, 0)); //January 1, 1970 2:46:40 AM
87        ts_vec.push(make_datetime(10_000, 1000)); //January 1, 1970 2:46:40 AM (and one microsecond)
88        ts_vec.push(make_datetime(1_500_391_124, 1_000_000)); //July 18, 2017
89        ts_vec.push(make_datetime(2_000_000_000, 2_000_000)); //May 18, 2033
90        ts_vec.push(make_datetime(3_000_000_000, 999_999_999)); //January 24, 2065
91        ts_vec.push(make_datetime(10_000_000_000, 0)); //November 20, 2286
92
93        for ts in ts_vec {
94            db.execute("INSERT INTO foo(t) VALUES (?1)", [ts])?;
95
96            let from: OffsetDateTime = db.one_column("SELECT t FROM foo")?;
97
98            db.execute("DELETE FROM foo", [])?;
99
100            assert_eq!(from, ts);
101        }
102        Ok(())
103    }
104
105    #[test]
106    fn test_string_values() -> Result<()> {
107        let db = Connection::open_in_memory()?;
108        for (s, t) in vec![
109            (
110                "2013-10-07 08:23:19",
111                Ok(OffsetDateTime::parse("2013-10-07T08:23:19Z", &Rfc3339).unwrap()),
112            ),
113            (
114                "2013-10-07 08:23:19Z",
115                Ok(OffsetDateTime::parse("2013-10-07T08:23:19Z", &Rfc3339).unwrap()),
116            ),
117            (
118                "2013-10-07T08:23:19Z",
119                Ok(OffsetDateTime::parse("2013-10-07T08:23:19Z", &Rfc3339).unwrap()),
120            ),
121            (
122                "2013-10-07 08:23:19.120",
123                Ok(OffsetDateTime::parse("2013-10-07T08:23:19.120Z", &Rfc3339).unwrap()),
124            ),
125            (
126                "2013-10-07 08:23:19.120Z",
127                Ok(OffsetDateTime::parse("2013-10-07T08:23:19.120Z", &Rfc3339).unwrap()),
128            ),
129            (
130                "2013-10-07T08:23:19.120Z",
131                Ok(OffsetDateTime::parse("2013-10-07T08:23:19.120Z", &Rfc3339).unwrap()),
132            ),
133            (
134                "2013-10-07 04:23:19-04:00",
135                Ok(OffsetDateTime::parse("2013-10-07T04:23:19-04:00", &Rfc3339).unwrap()),
136            ),
137            (
138                "2013-10-07 04:23:19.120-04:00",
139                Ok(OffsetDateTime::parse("2013-10-07T04:23:19.120-04:00", &Rfc3339).unwrap()),
140            ),
141            (
142                "2013-10-07T04:23:19.120-04:00",
143                Ok(OffsetDateTime::parse("2013-10-07T04:23:19.120-04:00", &Rfc3339).unwrap()),
144            ),
145        ] {
146            let result: Result<OffsetDateTime> = db.query_row("SELECT ?1", [s], |r| r.get(0));
147            assert_eq!(result, t);
148        }
149        Ok(())
150    }
151
152    #[test]
153    fn test_sqlite_functions() -> Result<()> {
154        let db = Connection::open_in_memory()?;
155        let result: Result<OffsetDateTime> = db.one_column("SELECT CURRENT_TIMESTAMP");
156        result.unwrap();
157        Ok(())
158    }
159
160    #[test]
161    fn test_param() -> Result<()> {
162        let db = Connection::open_in_memory()?;
163        let result: Result<bool> = db.query_row("SELECT 1 WHERE ?1 BETWEEN datetime('now', '-1 minute') AND datetime('now', '+1 minute')", [OffsetDateTime::now_utc()], |r| r.get(0));
164        result.unwrap();
165        Ok(())
166    }
167}
rusqlite/types/time.rs

rusqlite/types/
time.rs
