rusqlite/types/
chrono.rs

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