rusqlite/types/
chrono.rs

1//! Convert most of the [Time Strings](http://sqlite.org/lang_datefunc.html) to chrono types.
2
3use chrono::{DateTime, FixedOffset, Local, NaiveDate, NaiveDateTime, NaiveTime, TimeZone, Utc};
4
5use crate::types::{FromSql, FromSqlError, FromSqlResult, ToSql, ToSqlOutput, ValueRef};
6use crate::Result;
7
8/// ISO 8601 calendar date without timezone => "YYYY-MM-DD"
9impl ToSql for NaiveDate {
10    #[inline]
11    fn to_sql(&self) -> Result<ToSqlOutput<'_>> {
12        let date_str = self.format("%F").to_string();
13        Ok(ToSqlOutput::from(date_str))
14    }
15}
16
17/// "YYYY-MM-DD" => ISO 8601 calendar date without timezone.
18impl FromSql for NaiveDate {
19    #[inline]
20    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
21        value
22            .as_str()
23            .and_then(|s| Self::parse_from_str(s, "%F").map_err(FromSqlError::other))
24    }
25}
26
27/// ISO 8601 time without timezone => "HH:MM:SS.SSS"
28impl ToSql for NaiveTime {
29    #[inline]
30    fn to_sql(&self) -> Result<ToSqlOutput<'_>> {
31        let date_str = self.format("%T%.f").to_string();
32        Ok(ToSqlOutput::from(date_str))
33    }
34}
35
36/// "HH:MM"/"HH:MM:SS"/"HH:MM:SS.SSS" => ISO 8601 time without timezone.
37impl FromSql for NaiveTime {
38    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
39        value.as_str().and_then(|s| {
40            let fmt = match s.len() {
41                5 => "%H:%M",
42                8 => "%T",
43                _ => "%T%.f",
44            };
45            Self::parse_from_str(s, fmt).map_err(FromSqlError::other)
46        })
47    }
48}
49
50/// ISO 8601 combined date and time without timezone =>
51/// "YYYY-MM-DD HH:MM:SS.SSS"
52impl ToSql for NaiveDateTime {
53    #[inline]
54    fn to_sql(&self) -> Result<ToSqlOutput<'_>> {
55        let date_str = self.format("%F %T%.f").to_string();
56        Ok(ToSqlOutput::from(date_str))
57    }
58}
59
60/// "YYYY-MM-DD HH:MM:SS"/"YYYY-MM-DD HH:MM:SS.SSS" => ISO 8601 combined date
61/// and time without timezone. ("YYYY-MM-DDTHH:MM:SS"/"YYYY-MM-DDTHH:MM:SS.SSS"
62/// also supported)
63impl FromSql for NaiveDateTime {
64    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
65        value.as_str().and_then(|s| {
66            let fmt = if s.len() >= 11 && s.as_bytes()[10] == b'T' {
67                "%FT%T%.f"
68            } else {
69                "%F %T%.f"
70            };
71
72            Self::parse_from_str(s, fmt).map_err(FromSqlError::other)
73        })
74    }
75}
76
77/// UTC time => UTC RFC3339 timestamp
78/// ("YYYY-MM-DD HH:MM:SS.SSS+00:00").
79impl ToSql for DateTime<Utc> {
80    #[inline]
81    fn to_sql(&self) -> Result<ToSqlOutput<'_>> {
82        let date_str = self.format("%F %T%.f%:z").to_string();
83        Ok(ToSqlOutput::from(date_str))
84    }
85}
86
87/// Local time => UTC RFC3339 timestamp
88/// ("YYYY-MM-DD HH:MM:SS.SSS+00:00").
89impl ToSql for DateTime<Local> {
90    #[inline]
91    fn to_sql(&self) -> Result<ToSqlOutput<'_>> {
92        let date_str = self.with_timezone(&Utc).format("%F %T%.f%:z").to_string();
93        Ok(ToSqlOutput::from(date_str))
94    }
95}
96
97/// Date and time with time zone => RFC3339 timestamp
98/// ("YYYY-MM-DD HH:MM:SS.SSS[+-]HH:MM").
99impl ToSql for DateTime<FixedOffset> {
100    #[inline]
101    fn to_sql(&self) -> Result<ToSqlOutput<'_>> {
102        let date_str = self.format("%F %T%.f%:z").to_string();
103        Ok(ToSqlOutput::from(date_str))
104    }
105}
106
107/// RFC3339 ("YYYY-MM-DD HH:MM:SS.SSS[+-]HH:MM") into `DateTime<Utc>`.
108impl FromSql for DateTime<Utc> {
109    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
110        {
111            // Try to parse value as rfc3339 first.
112            let s = value.as_str()?;
113
114            let fmt = if s.len() >= 11 && s.as_bytes()[10] == b'T' {
115                "%FT%T%.f%#z"
116            } else {
117                "%F %T%.f%#z"
118            };
119
120            if let Ok(dt) = DateTime::parse_from_str(s, fmt) {
121                return Ok(dt.with_timezone(&Utc));
122            }
123        }
124
125        // Couldn't parse as rfc3339 - fall back to NaiveDateTime.
126        NaiveDateTime::column_result(value).map(|dt| Utc.from_utc_datetime(&dt))
127    }
128}
129
130/// RFC3339 ("YYYY-MM-DD HH:MM:SS.SSS[+-]HH:MM") into `DateTime<Local>`.
131impl FromSql for DateTime<Local> {
132    #[inline]
133    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
134        let utc_dt = DateTime::<Utc>::column_result(value)?;
135        Ok(utc_dt.with_timezone(&Local))
136    }
137}
138
139/// RFC3339 ("YYYY-MM-DD HH:MM:SS.SSS[+-]HH:MM") into `DateTime<FixedOffset>`.
140impl FromSql for DateTime<FixedOffset> {
141    #[inline]
142    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
143        let s = String::column_result(value)?;
144        Self::parse_from_rfc3339(s.as_str())
145            .or_else(|_| Self::parse_from_str(s.as_str(), "%F %T%.f%:z"))
146            .map_err(FromSqlError::other)
147    }
148}
149
150#[cfg(test)]
151mod test {
152    use crate::{
153        types::{FromSql, ValueRef},
154        Connection, Result,
155    };
156    use chrono::{
157        DateTime, Duration, FixedOffset, Local, NaiveDate, NaiveDateTime, NaiveTime, TimeZone, Utc,
158    };
159
160    fn checked_memory_handle() -> Result<Connection> {
161        let db = Connection::open_in_memory()?;
162        db.execute_batch("CREATE TABLE foo (t TEXT, i INTEGER, f FLOAT, b BLOB)")?;
163        Ok(db)
164    }
165
166    #[test]
167    fn test_naive_date() -> Result<()> {
168        let db = checked_memory_handle()?;
169        let date = NaiveDate::from_ymd_opt(2016, 2, 23).unwrap();
170        db.execute("INSERT INTO foo (t) VALUES (?1)", [date])?;
171
172        let s: String = db.one_column("SELECT t FROM foo", [])?;
173        assert_eq!("2016-02-23", s);
174        let t: NaiveDate = db.one_column("SELECT t FROM foo", [])?;
175        assert_eq!(date, t);
176        Ok(())
177    }
178
179    #[test]
180    fn test_naive_time() -> Result<()> {
181        let db = checked_memory_handle()?;
182        let time = NaiveTime::from_hms_opt(23, 56, 4).unwrap();
183        db.execute("INSERT INTO foo (t) VALUES (?1)", [time])?;
184
185        let s: String = db.one_column("SELECT t FROM foo", [])?;
186        assert_eq!("23:56:04", s);
187        let v: NaiveTime = db.one_column("SELECT t FROM foo", [])?;
188        assert_eq!(time, v);
189        Ok(())
190    }
191
192    #[test]
193    fn test_naive_date_time() -> Result<()> {
194        let db = checked_memory_handle()?;
195        let date = NaiveDate::from_ymd_opt(2016, 2, 23).unwrap();
196        let time = NaiveTime::from_hms_opt(23, 56, 4).unwrap();
197        let dt = NaiveDateTime::new(date, time);
198
199        db.execute("INSERT INTO foo (t) VALUES (?1)", [dt])?;
200
201        let s: String = db.one_column("SELECT t FROM foo", [])?;
202        assert_eq!("2016-02-23 23:56:04", s);
203        let v: NaiveDateTime = db.one_column("SELECT t FROM foo", [])?;
204        assert_eq!(dt, v);
205
206        db.execute("UPDATE foo set b = datetime(t)", [])?; // "YYYY-MM-DD HH:MM:SS"
207        let hms: NaiveDateTime = db.one_column("SELECT b FROM foo", [])?;
208        assert_eq!(dt, hms);
209        Ok(())
210    }
211
212    #[test]
213    fn test_date_time_utc() -> Result<()> {
214        let db = checked_memory_handle()?;
215        let date = NaiveDate::from_ymd_opt(2016, 2, 23).unwrap();
216        let time = NaiveTime::from_hms_milli_opt(23, 56, 4, 789).unwrap();
217        let dt = NaiveDateTime::new(date, time);
218        let utc = Utc.from_utc_datetime(&dt);
219
220        db.execute("INSERT INTO foo (t) VALUES (?1)", [utc])?;
221
222        let s: String = db.one_column("SELECT t FROM foo", [])?;
223        assert_eq!("2016-02-23 23:56:04.789+00:00", s);
224
225        let v1: DateTime<Utc> = db.one_column("SELECT t FROM foo", [])?;
226        assert_eq!(utc, v1);
227
228        let v2: DateTime<Utc> = db.one_column("SELECT '2016-02-23 23:56:04.789'", [])?;
229        assert_eq!(utc, v2);
230
231        let v3: DateTime<Utc> = db.one_column("SELECT '2016-02-23 23:56:04'", [])?;
232        assert_eq!(utc - Duration::try_milliseconds(789).unwrap(), v3);
233
234        let v4: DateTime<Utc> = db.one_column("SELECT '2016-02-23 23:56:04.789+00:00'", [])?;
235        assert_eq!(utc, v4);
236        Ok(())
237    }
238
239    #[test]
240    fn test_date_time_local() -> Result<()> {
241        let db = checked_memory_handle()?;
242        let date = NaiveDate::from_ymd_opt(2016, 2, 23).unwrap();
243        let time = NaiveTime::from_hms_milli_opt(23, 56, 4, 789).unwrap();
244        let dt = NaiveDateTime::new(date, time);
245        let local = Local.from_local_datetime(&dt).single().unwrap();
246
247        db.execute("INSERT INTO foo (t) VALUES (?1)", [local])?;
248
249        // Stored string should be in UTC
250        let s: String = db.one_column("SELECT t FROM foo", [])?;
251        assert!(s.ends_with("+00:00"));
252
253        let v: DateTime<Local> = db.one_column("SELECT t FROM foo", [])?;
254        assert_eq!(local, v);
255        Ok(())
256    }
257
258    #[test]
259    fn test_date_time_fixed() -> Result<()> {
260        let db = checked_memory_handle()?;
261        let time = DateTime::parse_from_rfc3339("2020-04-07T11:23:45+04:00").unwrap();
262
263        db.execute("INSERT INTO foo (t) VALUES (?1)", [time])?;
264
265        // Stored string should preserve timezone offset
266        let s: String = db.one_column("SELECT t FROM foo", [])?;
267        assert!(s.ends_with("+04:00"));
268
269        let v: DateTime<FixedOffset> = db.one_column("SELECT t FROM foo", [])?;
270        assert_eq!(time.offset(), v.offset());
271        assert_eq!(time, v);
272        Ok(())
273    }
274
275    #[test]
276    fn test_sqlite_functions() -> Result<()> {
277        let db = checked_memory_handle()?;
278        db.one_column::<NaiveTime, _>("SELECT CURRENT_TIME", [])?;
279        db.one_column::<NaiveDate, _>("SELECT CURRENT_DATE", [])?;
280        db.one_column::<NaiveDateTime, _>("SELECT CURRENT_TIMESTAMP", [])?;
281        db.one_column::<DateTime<Utc>, _>("SELECT CURRENT_TIMESTAMP", [])?;
282        Ok(())
283    }
284
285    #[test]
286    fn test_naive_date_time_param() -> Result<()> {
287        let db = checked_memory_handle()?;
288        db.one_column::<bool, _>("SELECT 1 WHERE ?1 BETWEEN datetime('now', '-1 minute') AND datetime('now', '+1 minute')", [Utc::now().naive_utc()])?;
289        Ok(())
290    }
291
292    #[test]
293    fn test_date_time_param() -> Result<()> {
294        let db = checked_memory_handle()?;
295        db.one_column::<bool, _>("SELECT 1 WHERE ?1 BETWEEN datetime('now', '-1 minute') AND datetime('now', '+1 minute')", [Utc::now()])?;
296        Ok(())
297    }
298
299    #[test]
300    fn test_lenient_parse_timezone() {
301        DateTime::<Utc>::column_result(ValueRef::Text(b"1970-01-01T00:00:00Z")).unwrap();
302        DateTime::<Utc>::column_result(ValueRef::Text(b"1970-01-01T00:00:00+00")).unwrap();
303    }
304}
rusqlite/types/chrono.rs

rusqlite/types/
chrono.rs
