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, Type, 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") or unix timestamp (in seconds) into `DateTime<Utc>`.
108impl FromSql for DateTime<Utc> {
109    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
110        if value.data_type() == Type::Integer {
111            return value.as_i64().and_then(|i| {
112                DateTime::from_timestamp_secs(i).ok_or_else(|| FromSqlError::OutOfRange(i))
113            });
114        }
115        {
116            // Try to parse value as rfc3339 first.
117            let s = value.as_str()?;
118
119            let fmt = if s.len() >= 11 && s.as_bytes()[10] == b'T' {
120                "%FT%T%.f%#z"
121            } else {
122                "%F %T%.f%#z"
123            };
124
125            if let Ok(dt) = DateTime::parse_from_str(s, fmt) {
126                return Ok(dt.with_timezone(&Utc));
127            }
128        }
129
130        // Couldn't parse as rfc3339 - fall back to NaiveDateTime.
131        NaiveDateTime::column_result(value).map(|dt| Utc.from_utc_datetime(&dt))
132    }
133}
134
135/// RFC3339 ("YYYY-MM-DD HH:MM:SS.SSS[+-]HH:MM") or unix timestamp (in seconds) into `DateTime<Local>`.
136impl FromSql for DateTime<Local> {
137    #[inline]
138    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
139        let utc_dt = DateTime::<Utc>::column_result(value)?;
140        Ok(utc_dt.with_timezone(&Local))
141    }
142}
143
144/// RFC3339 ("YYYY-MM-DD HH:MM:SS.SSS[+-]HH:MM") into `DateTime<FixedOffset>`.
145impl FromSql for DateTime<FixedOffset> {
146    #[inline]
147    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
148        let s = String::column_result(value)?;
149        Self::parse_from_rfc3339(s.as_str())
150            .or_else(|_| Self::parse_from_str(s.as_str(), "%F %T%.f%:z"))
151            .map_err(FromSqlError::other)
152    }
153}
154
155#[cfg(test)]
156mod test {
157    #[cfg(all(target_family = "wasm", target_os = "unknown"))]
158    use wasm_bindgen_test::wasm_bindgen_test as test;
159
160    use crate::{
161        types::{FromSql, ValueRef},
162        Connection, Result,
163    };
164    use chrono::{
165        DateTime, Duration, FixedOffset, Local, NaiveDate, NaiveDateTime, NaiveTime, TimeZone,
166        Timelike, 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 AS (strftime('%s', t)), 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        let v1: DateTime<Utc> = db.one_column("SELECT i FROM foo", [])?;
237        assert_eq!(utc.with_nanosecond(0).unwrap(), v1);
238
239        let v2: DateTime<Utc> = db.one_column("SELECT '2016-02-23 23:56:04.789'", [])?;
240        assert_eq!(utc, v2);
241
242        let v3: DateTime<Utc> = db.one_column("SELECT '2016-02-23 23:56:04'", [])?;
243        assert_eq!(utc - Duration::try_milliseconds(789).unwrap(), v3);
244
245        let v4: DateTime<Utc> = db.one_column("SELECT '2016-02-23 23:56:04.789+00:00'", [])?;
246        assert_eq!(utc, v4);
247        Ok(())
248    }
249
250    #[test]
251    fn test_date_time_local() -> Result<()> {
252        let db = checked_memory_handle()?;
253        let date = NaiveDate::from_ymd_opt(2016, 2, 23).unwrap();
254        let time = NaiveTime::from_hms_milli_opt(23, 56, 4, 789).unwrap();
255        let dt = NaiveDateTime::new(date, time);
256        let local = Local.from_local_datetime(&dt).single().unwrap();
257
258        db.execute("INSERT INTO foo (t) VALUES (?1)", [local])?;
259
260        // Stored string should be in UTC
261        let s: String = db.one_column("SELECT t FROM foo", [])?;
262        assert!(s.ends_with("+00:00"));
263
264        let v: DateTime<Local> = db.one_column("SELECT t FROM foo", [])?;
265        assert_eq!(local, v);
266        let v: DateTime<Local> = db.one_column("SELECT i FROM foo", [])?;
267        assert_eq!(local.with_nanosecond(0).unwrap(), v);
268        Ok(())
269    }
270
271    #[test]
272    fn test_date_time_fixed() -> Result<()> {
273        let db = checked_memory_handle()?;
274        let time = DateTime::parse_from_rfc3339("2020-04-07T11:23:45+04:00").unwrap();
275
276        db.execute("INSERT INTO foo (t) VALUES (?1)", [time])?;
277
278        // Stored string should preserve timezone offset
279        let s: String = db.one_column("SELECT t FROM foo", [])?;
280        assert!(s.ends_with("+04:00"));
281
282        let v: DateTime<FixedOffset> = db.one_column("SELECT t FROM foo", [])?;
283        assert_eq!(time.offset(), v.offset());
284        assert_eq!(time, v);
285        Ok(())
286    }
287
288    #[test]
289    fn test_sqlite_functions() -> Result<()> {
290        let db = checked_memory_handle()?;
291        db.one_column::<NaiveTime, _>("SELECT CURRENT_TIME", [])?;
292        db.one_column::<NaiveDate, _>("SELECT CURRENT_DATE", [])?;
293        db.one_column::<NaiveDateTime, _>("SELECT CURRENT_TIMESTAMP", [])?;
294        db.one_column::<DateTime<Utc>, _>("SELECT CURRENT_TIMESTAMP", [])?;
295        Ok(())
296    }
297
298    #[test]
299    fn test_naive_date_time_param() -> Result<()> {
300        let db = checked_memory_handle()?;
301        db.one_column::<bool, _>("SELECT 1 WHERE ?1 BETWEEN datetime('now', '-1 minute') AND datetime('now', '+1 minute')", [Utc::now().naive_utc()])?;
302        Ok(())
303    }
304
305    #[test]
306    fn test_date_time_param() -> Result<()> {
307        let db = checked_memory_handle()?;
308        db.one_column::<bool, _>("SELECT 1 WHERE ?1 BETWEEN datetime('now', '-1 minute') AND datetime('now', '+1 minute')", [Utc::now()])?;
309        Ok(())
310    }
311
312    #[test]
313    fn test_lenient_parse_timezone() {
314        DateTime::<Utc>::column_result(ValueRef::Text(b"1970-01-01T00:00:00Z")).unwrap();
315        DateTime::<Utc>::column_result(ValueRef::Text(b"1970-01-01T00:00:00+00")).unwrap();
316    }
317}
rusqlite/types/chrono.rs

rusqlite/types/
chrono.rs
