sqlx_sqlite/types/
chrono.rs

1use std::fmt::Display;
2
3use crate::value::ValueRef;
4use crate::{
5    decode::Decode,
6    encode::{Encode, IsNull},
7    error::BoxDynError,
8    type_info::DataType,
9    types::Type,
10    Sqlite, SqliteArgumentValue, SqliteTypeInfo, SqliteValueRef,
11};
12use chrono::FixedOffset;
13use chrono::{
14    DateTime, Local, NaiveDate, NaiveDateTime, NaiveTime, Offset, SecondsFormat, TimeZone, Utc,
15};
16
17impl<Tz: TimeZone> Type<Sqlite> for DateTime<Tz> {
18    fn type_info() -> SqliteTypeInfo {
19        SqliteTypeInfo(DataType::Datetime)
20    }
21
22    fn compatible(ty: &SqliteTypeInfo) -> bool {
23        <NaiveDateTime as Type<Sqlite>>::compatible(ty)
24    }
25}
26
27impl Type<Sqlite> for NaiveDateTime {
28    fn type_info() -> SqliteTypeInfo {
29        SqliteTypeInfo(DataType::Datetime)
30    }
31
32    fn compatible(ty: &SqliteTypeInfo) -> bool {
33        matches!(
34            ty.0,
35            DataType::Datetime
36                | DataType::Text
37                | DataType::Integer
38                | DataType::Int4
39                | DataType::Float
40        )
41    }
42}
43
44impl Type<Sqlite> for NaiveDate {
45    fn type_info() -> SqliteTypeInfo {
46        SqliteTypeInfo(DataType::Date)
47    }
48
49    fn compatible(ty: &SqliteTypeInfo) -> bool {
50        matches!(ty.0, DataType::Date | DataType::Text)
51    }
52}
53
54impl Type<Sqlite> for NaiveTime {
55    fn type_info() -> SqliteTypeInfo {
56        SqliteTypeInfo(DataType::Time)
57    }
58
59    fn compatible(ty: &SqliteTypeInfo) -> bool {
60        matches!(ty.0, DataType::Time | DataType::Text)
61    }
62}
63
64impl<Tz: TimeZone> Encode<'_, Sqlite> for DateTime<Tz>
65where
66    Tz::Offset: Display,
67{
68    fn encode_by_ref(&self, buf: &mut Vec<SqliteArgumentValue<'_>>) -> Result<IsNull, BoxDynError> {
69        Encode::<Sqlite>::encode(self.to_rfc3339_opts(SecondsFormat::AutoSi, false), buf)
70    }
71}
72
73impl Encode<'_, Sqlite> for NaiveDateTime {
74    fn encode_by_ref(&self, buf: &mut Vec<SqliteArgumentValue<'_>>) -> Result<IsNull, BoxDynError> {
75        Encode::<Sqlite>::encode(self.format("%F %T%.f").to_string(), buf)
76    }
77}
78
79impl Encode<'_, Sqlite> for NaiveDate {
80    fn encode_by_ref(&self, buf: &mut Vec<SqliteArgumentValue<'_>>) -> Result<IsNull, BoxDynError> {
81        Encode::<Sqlite>::encode(self.format("%F").to_string(), buf)
82    }
83}
84
85impl Encode<'_, Sqlite> for NaiveTime {
86    fn encode_by_ref(&self, buf: &mut Vec<SqliteArgumentValue<'_>>) -> Result<IsNull, BoxDynError> {
87        Encode::<Sqlite>::encode(self.format("%T%.f").to_string(), buf)
88    }
89}
90
91impl<'r> Decode<'r, Sqlite> for DateTime<Utc> {
92    fn decode(value: SqliteValueRef<'r>) -> Result<Self, BoxDynError> {
93        Ok(Utc.from_utc_datetime(&decode_datetime(value)?.naive_utc()))
94    }
95}
96
97impl<'r> Decode<'r, Sqlite> for DateTime<Local> {
98    fn decode(value: SqliteValueRef<'r>) -> Result<Self, BoxDynError> {
99        Ok(Local.from_utc_datetime(&decode_datetime(value)?.naive_utc()))
100    }
101}
102
103impl<'r> Decode<'r, Sqlite> for DateTime<FixedOffset> {
104    fn decode(value: SqliteValueRef<'r>) -> Result<Self, BoxDynError> {
105        decode_datetime(value)
106    }
107}
108
109fn decode_datetime(value: SqliteValueRef<'_>) -> Result<DateTime<FixedOffset>, BoxDynError> {
110    let dt = match value.type_info().0 {
111        DataType::Text => decode_datetime_from_text(value.text()?),
112        DataType::Int4 | DataType::Integer => decode_datetime_from_int(value.int64()),
113        DataType::Float => decode_datetime_from_float(value.double()),
114
115        _ => None,
116    };
117
118    if let Some(dt) = dt {
119        Ok(dt)
120    } else {
121        Err(format!("invalid datetime: {}", value.text()?).into())
122    }
123}
124
125fn decode_datetime_from_text(value: &str) -> Option<DateTime<FixedOffset>> {
126    if let Ok(dt) = DateTime::parse_from_rfc3339(value) {
127        return Some(dt);
128    }
129
130    // Loop over common date time patterns, inspired by Diesel
131    // https://github.com/diesel-rs/diesel/blob/93ab183bcb06c69c0aee4a7557b6798fd52dd0d8/diesel/src/sqlite/types/date_and_time/chrono.rs#L56-L97
132    let sqlite_datetime_formats = &[
133        // Most likely format
134        "%F %T%.f",
135        // Other formats in order of appearance in docs
136        "%F %R",
137        "%F %RZ",
138        "%F %R%:z",
139        "%F %T%.fZ",
140        "%F %T%.f%:z",
141        "%FT%R",
142        "%FT%RZ",
143        "%FT%R%:z",
144        "%FT%T%.f",
145        "%FT%T%.fZ",
146        "%FT%T%.f%:z",
147    ];
148
149    for format in sqlite_datetime_formats {
150        if let Ok(dt) = DateTime::parse_from_str(value, format) {
151            return Some(dt);
152        }
153
154        if let Ok(dt) = NaiveDateTime::parse_from_str(value, format) {
155            return Some(Utc.fix().from_utc_datetime(&dt));
156        }
157    }
158
159    None
160}
161
162fn decode_datetime_from_int(value: i64) -> Option<DateTime<FixedOffset>> {
163    Utc.fix().timestamp_opt(value, 0).single()
164}
165
166fn decode_datetime_from_float(value: f64) -> Option<DateTime<FixedOffset>> {
167    let epoch_in_julian_days = 2_440_587.5;
168    let seconds_in_day = 86400.0;
169    let timestamp = (value - epoch_in_julian_days) * seconds_in_day;
170
171    if !timestamp.is_finite() {
172        return None;
173    }
174
175    // We don't really have a choice but to do lossy casts for this conversion
176    // We checked above if the value is infinite or NaN which could otherwise cause problems
177    #[allow(clippy::cast_possible_truncation, clippy::cast_sign_loss)]
178    {
179        let seconds = timestamp.trunc() as i64;
180        let nanos = (timestamp.fract() * 1E9).abs() as u32;
181
182        Utc.fix().timestamp_opt(seconds, nanos).single()
183    }
184}
185
186impl<'r> Decode<'r, Sqlite> for NaiveDateTime {
187    fn decode(value: SqliteValueRef<'r>) -> Result<Self, BoxDynError> {
188        Ok(decode_datetime(value)?.naive_local())
189    }
190}
191
192impl<'r> Decode<'r, Sqlite> for NaiveDate {
193    fn decode(value: SqliteValueRef<'r>) -> Result<Self, BoxDynError> {
194        Ok(NaiveDate::parse_from_str(value.text()?, "%F")?)
195    }
196}
197
198impl<'r> Decode<'r, Sqlite> for NaiveTime {
199    fn decode(value: SqliteValueRef<'r>) -> Result<Self, BoxDynError> {
200        let value = value.text()?;
201
202        // Loop over common time patterns, inspired by Diesel
203        // https://github.com/diesel-rs/diesel/blob/93ab183bcb06c69c0aee4a7557b6798fd52dd0d8/diesel/src/sqlite/types/date_and_time/chrono.rs#L29-L47
204        #[rustfmt::skip] // don't like how rustfmt mangles the comments
205        let sqlite_time_formats = &[
206            // Most likely format
207            "%T.f", "%T%.f",
208            // Other formats in order of appearance in docs
209            "%R", "%RZ", "%T%.fZ", "%R%:z", "%T%.f%:z",
210        ];
211
212        for format in sqlite_time_formats {
213            if let Ok(dt) = NaiveTime::parse_from_str(value, format) {
214                return Ok(dt);
215            }
216        }
217
218        Err(format!("invalid time: {value}").into())
219    }
220}
sqlx_sqlite/types/chrono.rs

sqlx_sqlite/types/
chrono.rs
