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

rusqlite/types/
chrono.rs
