Skip to main content

diesel/sqlite/types/date_and_time/
chrono.rs

1extern crate chrono;
2
3use self::chrono::{DateTime, Local, NaiveDate, NaiveDateTime, NaiveTime, TimeZone, Utc};
4
5use crate::backend::Backend;
6use crate::deserialize::{self, FromSql};
7use crate::serialize::{self, IsNull, Output, ToSql};
8use crate::sql_types::{Date, Time, Timestamp, TimestamptzSqlite};
9use crate::sqlite::Sqlite;
10
11/// Warning to future editors:
12/// Changes in the following formats need to be kept in sync
13/// with the formats of the ["time"](super::time) module.
14/// We do not need a distinction between whole second and
15/// subsecond since %.f will only print the dot if needed.
16/// We always print as many subsecond as his given to us,
17/// this means the subsecond part can be 3, 6 or 9 digits.
18const DATE_FORMAT: &str = "%F";
19
20const ENCODE_TIME_FORMAT: &str = "%T%.f";
21
22const TIME_FORMATS: [&str; 9] = [
23    // Most likely formats
24    "%T%.f", "%T", // All other valid formats in order of increasing specificity
25    "%R", "%RZ", "%R%:z", "%TZ", "%T%:z", "%T%.fZ", "%T%.f%:z",
26];
27
28const ENCODE_NAIVE_DATETIME_FORMAT: &str = "%F %T%.f";
29
30const ENCODE_DATETIME_FORMAT: &str = "%F %T%.f%:z";
31
32const NAIVE_DATETIME_FORMATS: [&str; 18] = [
33    // Most likely formats
34    "%F %T%.f",
35    "%F %T%.f%:z",
36    "%F %T",
37    "%F %T%:z",
38    // All other formats in order of increasing specificity
39    "%F %R",
40    "%F %RZ",
41    "%F %R%:z",
42    "%F %TZ",
43    "%F %T%.fZ",
44    "%FT%R",
45    "%FT%RZ",
46    "%FT%R%:z",
47    "%FT%T",
48    "%FT%TZ",
49    "%FT%T%:z",
50    "%FT%T%.f",
51    "%FT%T%.fZ",
52    "%FT%T%.f%:z",
53];
54
55const DATETIME_FORMATS: [&str; 12] = [
56    // Most likely formats
57    "%F %T%.f%:z",
58    "%F %T%:z",
59    // All other formats in order of increasing specificity
60    "%F %RZ",
61    "%F %R%:z",
62    "%F %TZ",
63    "%F %T%.fZ",
64    "%FT%RZ",
65    "%FT%R%:z",
66    "%FT%TZ",
67    "%FT%T%:z",
68    "%FT%T%.fZ",
69    "%FT%T%.f%:z",
70];
71
72fn parse_julian(julian_days: f64) -> Option<NaiveDateTime> {
73    const EPOCH_IN_JULIAN_DAYS: f64 = 2_440_587.5;
74    const SECONDS_IN_DAY: f64 = 86400.0;
75    let timestamp = (julian_days - EPOCH_IN_JULIAN_DAYS) * SECONDS_IN_DAY;
76    #[allow(clippy::cast_possible_truncation)] // we want to truncate
77    let seconds = timestamp.trunc() as i64;
78    // that's not true, `fract` is always > 0
79    #[allow(clippy::cast_sign_loss, clippy::cast_possible_truncation)]
80    let nanos = (timestamp.fract() * 1E9) as u32;
81    #[allow(deprecated)] // otherwise we would need to bump our minimal chrono version
82    NaiveDateTime::from_timestamp_opt(seconds, nanos)
83}
84
85#[cfg(all(feature = "__sqlite-shared", feature = "chrono"))]
86impl FromSql<Date, Sqlite> for NaiveDate {
87    fn from_sql(mut value: <Sqlite as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
88        value
89            .parse_string(|s| Self::parse_from_str(s, DATE_FORMAT))
90            .map_err(Into::into)
91    }
92}
93
94#[cfg(all(feature = "__sqlite-shared", feature = "chrono"))]
95impl ToSql<Date, Sqlite> for NaiveDate {
96    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
97        out.set_value(self.format(DATE_FORMAT).to_string());
98        Ok(IsNull::No)
99    }
100}
101
102#[cfg(all(feature = "__sqlite-shared", feature = "chrono"))]
103impl FromSql<Time, Sqlite> for NaiveTime {
104    fn from_sql(mut value: <Sqlite as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
105        value.parse_string(|text| {
106            for format in TIME_FORMATS {
107                if let Ok(time) = Self::parse_from_str(text, format) {
108                    return Ok(time);
109                }
110            }
111
112            Err(::alloc::__export::must_use({
        ::alloc::fmt::format(format_args!("Invalid time {0}", text))
    })format!("Invalid time {text}").into())
113        })
114    }
115}
116
117#[cfg(all(feature = "__sqlite-shared", feature = "chrono"))]
118impl ToSql<Time, Sqlite> for NaiveTime {
119    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
120        out.set_value(self.format(ENCODE_TIME_FORMAT).to_string());
121        Ok(IsNull::No)
122    }
123}
124
125#[cfg(all(feature = "__sqlite-shared", feature = "chrono"))]
126impl FromSql<Timestamp, Sqlite> for NaiveDateTime {
127    fn from_sql(mut value: <Sqlite as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
128        value.parse_string(|text| {
129            for format in NAIVE_DATETIME_FORMATS {
130                if let Ok(dt) = Self::parse_from_str(text, format) {
131                    return Ok(dt);
132                }
133            }
134
135            if let Ok(julian_days) = text.parse::<f64>()
136                && let Some(timestamp) = parse_julian(julian_days)
137            {
138                return Ok(timestamp);
139            }
140
141            Err(::alloc::__export::must_use({
        ::alloc::fmt::format(format_args!("Invalid datetime {0}", text))
    })format!("Invalid datetime {text}").into())
142        })
143    }
144}
145
146#[cfg(all(feature = "__sqlite-shared", feature = "chrono"))]
147impl ToSql<Timestamp, Sqlite> for NaiveDateTime {
148    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
149        out.set_value(self.format(ENCODE_NAIVE_DATETIME_FORMAT).to_string());
150        Ok(IsNull::No)
151    }
152}
153
154// A naive datetime carries no offset, so it reads and writes as a plain `Timestamp`.
155#[cfg(all(feature = "__sqlite-shared", feature = "chrono"))]
156impl FromSql<TimestamptzSqlite, Sqlite> for NaiveDateTime {
157    fn from_sql(value: <Sqlite as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
158        FromSql::<Timestamp, Sqlite>::from_sql(value)
159    }
160}
161
162#[cfg(all(feature = "__sqlite-shared", feature = "chrono"))]
163impl ToSql<TimestamptzSqlite, Sqlite> for NaiveDateTime {
164    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
165        ToSql::<Timestamp, Sqlite>::to_sql(self, out)
166    }
167}
168
169#[cfg(all(feature = "__sqlite-shared", feature = "chrono"))]
170impl FromSql<TimestamptzSqlite, Sqlite> for DateTime<Utc> {
171    fn from_sql(mut value: <Sqlite as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
172        // First try to parse the timezone
173        if let Ok(dt) = value.parse_string(|text| {
174            for format in DATETIME_FORMATS {
175                if let Ok(dt) = DateTime::parse_from_str(text, format) {
176                    return Ok(dt.with_timezone(&Utc));
177                }
178            }
179
180            Err(())
181        }) {
182            return Ok(dt);
183        }
184
185        // Fallback on assuming Utc
186        let naive_date_time =
187            <NaiveDateTime as FromSql<TimestamptzSqlite, Sqlite>>::from_sql(value)?;
188        Ok(Utc.from_utc_datetime(&naive_date_time))
189    }
190}
191
192#[cfg(all(feature = "__sqlite-shared", feature = "chrono"))]
193impl FromSql<TimestamptzSqlite, Sqlite> for DateTime<Local> {
194    fn from_sql(mut value: <Sqlite as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
195        // First try to parse the timezone
196        if let Ok(dt) = value.parse_string(|text| {
197            for format in DATETIME_FORMATS {
198                if let Ok(dt) = DateTime::parse_from_str(text, format) {
199                    return Ok(dt.with_timezone(&Local));
200                }
201            }
202
203            Err(())
204        }) {
205            return Ok(dt);
206        }
207
208        // Fallback on assuming Local
209        let naive_date_time =
210            <NaiveDateTime as FromSql<TimestamptzSqlite, Sqlite>>::from_sql(value)?;
211        Ok(Local::from_utc_datetime(&Local, &naive_date_time))
212    }
213}
214
215#[cfg(all(feature = "__sqlite-shared", feature = "chrono"))]
216impl<TZ: TimeZone> ToSql<TimestamptzSqlite, Sqlite> for DateTime<TZ> {
217    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
218        // Converting to UTC ensures consistency
219        let dt_utc = self.with_timezone(&Utc);
220        out.set_value(dt_utc.format(ENCODE_DATETIME_FORMAT).to_string());
221        Ok(IsNull::No)
222    }
223}
224
225#[cfg(all(test, not(all(target_family = "wasm", target_os = "unknown"))))]
226#[allow(clippy::unwrap_used)]
227mod tests {
228    extern crate chrono;
229    extern crate dotenvy;
230
231    use self::chrono::{
232        DateTime, FixedOffset, NaiveDate, NaiveDateTime, NaiveTime, TimeZone, Timelike, Utc,
233    };
234    // `Duration` and `now` are only used by the tests that compare against the
235    // current time, which Miri cannot read from the host while isolation is
236    // enabled.
237    #[cfg(not(miri))]
238    use self::chrono::Duration;
239
240    #[cfg(not(miri))]
241    use crate::dsl::now;
242    use crate::dsl::sql;
243    use crate::prelude::*;
244    use crate::select;
245    use crate::sql_types::{Text, Time, Timestamp, TimestamptzSqlite};
246    use crate::test_helpers::connection;
247
248    #[declare_sql_function]
249    extern "SQL" {
250        fn datetime(x: Text) -> Timestamp;
251        fn time(x: Text) -> Time;
252        fn date(x: Text) -> Date;
253    }
254
255    #[diesel_test_helper::test]
256    fn unix_epoch_encodes_correctly() {
257        let connection = &mut connection();
258        let time = NaiveDate::from_ymd_opt(1970, 1, 1)
259            .unwrap()
260            .and_hms_opt(0, 0, 0)
261            .unwrap();
262        let query = select(datetime("1970-01-01 00:00:00.000000").eq(time));
263        assert_eq!(Ok(true), query.get_result(connection));
264    }
265
266    // This test decodes query results, which requires reading memory
267    // allocated by the native sqlite library. Miri cannot track the
268    // provenance of that memory when using `-Zmiri-native-lib`.
269    #[cfg(not(miri))]
270    #[diesel_test_helper::test]
271    fn unix_epoch_decodes_correctly_in_all_possible_formats() {
272        let connection = &mut connection();
273        let time = NaiveDate::from_ymd_opt(1970, 1, 1)
274            .unwrap()
275            .and_hms_opt(0, 0, 0)
276            .unwrap();
277        let valid_epoch_formats = vec![
278            "1970-01-01 00:00",
279            "1970-01-01 00:00:00",
280            "1970-01-01 00:00:00.000",
281            "1970-01-01 00:00:00.000000",
282            "1970-01-01T00:00",
283            "1970-01-01T00:00:00",
284            "1970-01-01T00:00:00.000",
285            "1970-01-01T00:00:00.000000",
286            "1970-01-01 00:00Z",
287            "1970-01-01 00:00:00Z",
288            "1970-01-01 00:00:00.000Z",
289            "1970-01-01 00:00:00.000000Z",
290            "1970-01-01T00:00Z",
291            "1970-01-01T00:00:00Z",
292            "1970-01-01T00:00:00.000Z",
293            "1970-01-01T00:00:00.000000Z",
294            "1970-01-01 00:00+00:00",
295            "1970-01-01 00:00:00+00:00",
296            "1970-01-01 00:00:00.000+00:00",
297            "1970-01-01 00:00:00.000000+00:00",
298            "1970-01-01T00:00+00:00",
299            "1970-01-01T00:00:00+00:00",
300            "1970-01-01T00:00:00.000+00:00",
301            "1970-01-01T00:00:00.000000+00:00",
302            "1970-01-01 00:00+01:00",
303            "1970-01-01 00:00:00+01:00",
304            "1970-01-01 00:00:00.000+01:00",
305            "1970-01-01 00:00:00.000000+01:00",
306            "1970-01-01T00:00+01:00",
307            "1970-01-01T00:00:00+01:00",
308            "1970-01-01T00:00:00.000+01:00",
309            "1970-01-01T00:00:00.000000+01:00",
310            "1970-01-01T00:00-01:00",
311            "1970-01-01T00:00:00-01:00",
312            "1970-01-01T00:00:00.000-01:00",
313            "1970-01-01T00:00:00.000000-01:00",
314            "1970-01-01T00:00-01:00",
315            "1970-01-01T00:00:00-01:00",
316            "1970-01-01T00:00:00.000-01:00",
317            "1970-01-01T00:00:00.000000-01:00",
318            "2440587.5",
319        ];
320
321        for s in valid_epoch_formats {
322            let epoch_from_sql = select(sql::<Timestamp>(&format!("'{s}'"))).get_result(connection);
323            assert_eq!(Ok(time), epoch_from_sql, "format {s} failed");
324        }
325    }
326
327    // This test compares against the current time, which Miri cannot read
328    // from the host while isolation is enabled.
329    #[cfg(not(miri))]
330    #[diesel_test_helper::test]
331    fn times_relative_to_now_encode_correctly() {
332        let connection = &mut connection();
333        let time = Utc::now().naive_utc() + Duration::try_seconds(60).unwrap();
334        let query = select(now.lt(time));
335        assert_eq!(Ok(true), query.get_result(connection));
336
337        let time = Utc::now().naive_utc() - Duration::try_seconds(600).unwrap();
338        let query = select(now.gt(time));
339        assert_eq!(Ok(true), query.get_result(connection));
340    }
341
342    #[diesel_test_helper::test]
343    fn times_of_day_encode_correctly() {
344        let connection = &mut connection();
345
346        let midnight = NaiveTime::from_hms_opt(0, 0, 0).unwrap();
347        let query = select(time("00:00:00.000000").eq(midnight));
348        assert!(query.get_result::<bool>(connection).unwrap());
349
350        let noon = NaiveTime::from_hms_opt(12, 0, 0).unwrap();
351        let query = select(time("12:00:00.000000").eq(noon));
352        assert!(query.get_result::<bool>(connection).unwrap());
353
354        let roughly_half_past_eleven = NaiveTime::from_hms_micro_opt(23, 37, 4, 2200).unwrap();
355        let query = select(sql::<Time>("'23:37:04.002200'").eq(roughly_half_past_eleven));
356        assert!(query.get_result::<bool>(connection).unwrap());
357    }
358
359    // This test decodes query results, which requires reading memory
360    // allocated by the native sqlite library. Miri cannot track the
361    // provenance of that memory when using `-Zmiri-native-lib`.
362    #[cfg(not(miri))]
363    #[diesel_test_helper::test]
364    fn times_of_day_decode_correctly() {
365        let connection = &mut connection();
366        let midnight = NaiveTime::from_hms_opt(0, 0, 0).unwrap();
367        let valid_midnight_formats = &[
368            "00:00",
369            "00:00:00",
370            "00:00:00.000",
371            "00:00:00.000000",
372            "00:00Z",
373            "00:00:00Z",
374            "00:00:00.000Z",
375            "00:00:00.000000Z",
376            "00:00+00:00",
377            "00:00:00+00:00",
378            "00:00:00.000+00:00",
379            "00:00:00.000000+00:00",
380            "00:00+01:00",
381            "00:00:00+01:00",
382            "00:00:00.000+01:00",
383            "00:00:00.000000+01:00",
384            "00:00-01:00",
385            "00:00:00-01:00",
386            "00:00:00.000-01:00",
387            "00:00:00.000000-01:00",
388        ];
389        for format in valid_midnight_formats {
390            let query = select(sql::<Time>(&format!("'{format}'")));
391            assert_eq!(
392                Ok(midnight),
393                query.get_result::<NaiveTime>(connection),
394                "format {format} failed"
395            );
396        }
397
398        let noon = NaiveTime::from_hms_opt(12, 0, 0).unwrap();
399        let query = select(sql::<Time>("'12:00:00'"));
400        assert_eq!(Ok(noon), query.get_result::<NaiveTime>(connection));
401
402        let roughly_half_past_eleven = NaiveTime::from_hms_micro_opt(23, 37, 4, 2200).unwrap();
403        let query = select(sql::<Time>("'23:37:04.002200'"));
404        assert_eq!(
405            Ok(roughly_half_past_eleven),
406            query.get_result::<NaiveTime>(connection)
407        );
408    }
409
410    #[diesel_test_helper::test]
411    fn dates_encode_correctly() {
412        let connection = &mut connection();
413        let january_first_2000 = NaiveDate::from_ymd_opt(2000, 1, 1).unwrap();
414        let query = select(date("2000-01-01").eq(january_first_2000));
415        assert!(query.get_result::<bool>(connection).unwrap());
416
417        let distant_past = NaiveDate::from_ymd_opt(0, 4, 11).unwrap();
418        let query = select(date("0000-04-11").eq(distant_past));
419        assert!(query.get_result::<bool>(connection).unwrap());
420
421        let january_first_2018 = NaiveDate::from_ymd_opt(2018, 1, 1).unwrap();
422        let query = select(date("2018-01-01").eq(january_first_2018));
423        assert!(query.get_result::<bool>(connection).unwrap());
424
425        let distant_future = NaiveDate::from_ymd_opt(9999, 1, 8).unwrap();
426        let query = select(date("9999-01-08").eq(distant_future));
427        assert!(query.get_result::<bool>(connection).unwrap());
428    }
429
430    // This test decodes query results, which requires reading memory
431    // allocated by the native sqlite library. Miri cannot track the
432    // provenance of that memory when using `-Zmiri-native-lib`.
433    #[cfg(not(miri))]
434    #[diesel_test_helper::test]
435    fn dates_decode_correctly() {
436        let connection = &mut connection();
437        let january_first_2000 = NaiveDate::from_ymd_opt(2000, 1, 1).unwrap();
438        let query = select(date("2000-01-01"));
439        assert_eq!(
440            Ok(january_first_2000),
441            query.get_result::<NaiveDate>(connection)
442        );
443
444        let distant_past = NaiveDate::from_ymd_opt(0, 4, 11).unwrap();
445        let query = select(date("0000-04-11"));
446        assert_eq!(Ok(distant_past), query.get_result::<NaiveDate>(connection));
447
448        let january_first_2018 = NaiveDate::from_ymd_opt(2018, 1, 1).unwrap();
449        let query = select(date("2018-01-01"));
450        assert_eq!(
451            Ok(january_first_2018),
452            query.get_result::<NaiveDate>(connection)
453        );
454
455        let distant_future = NaiveDate::from_ymd_opt(9999, 1, 8).unwrap();
456        let query = select(date("9999-01-08"));
457        assert_eq!(
458            Ok(distant_future),
459            query.get_result::<NaiveDate>(connection)
460        );
461    }
462
463    // This test decodes query results, which requires reading memory
464    // allocated by the native sqlite library. Miri cannot track the
465    // provenance of that memory when using `-Zmiri-native-lib`.
466    #[cfg(not(miri))]
467    #[diesel_test_helper::test]
468    fn datetimes_decode_correctly() {
469        let connection = &mut connection();
470        let january_first_2000 = NaiveDate::from_ymd_opt(2000, 1, 1)
471            .unwrap()
472            .and_hms_opt(1, 1, 1)
473            .unwrap();
474        let query = select(datetime("2000-01-01 01:01:01.000000"));
475        assert_eq!(
476            Ok(january_first_2000),
477            query.get_result::<NaiveDateTime>(connection)
478        );
479
480        let distant_past = NaiveDate::from_ymd_opt(0, 4, 11)
481            .unwrap()
482            .and_hms_opt(2, 2, 2)
483            .unwrap();
484        let query = select(datetime("0000-04-11 02:02:02.000000"));
485        assert_eq!(
486            Ok(distant_past),
487            query.get_result::<NaiveDateTime>(connection)
488        );
489
490        let january_first_2018 = NaiveDate::from_ymd_opt(2018, 1, 1).unwrap();
491        let query = select(date("2018-01-01"));
492        assert_eq!(
493            Ok(january_first_2018),
494            query.get_result::<NaiveDate>(connection)
495        );
496
497        let distant_future = NaiveDate::from_ymd_opt(9999, 1, 8)
498            .unwrap()
499            .and_hms_opt(23, 59, 59)
500            .unwrap()
501            .with_nanosecond(100_000)
502            .unwrap();
503        let query = select(sql::<Timestamp>("'9999-01-08 23:59:59.000100'"));
504        assert_eq!(
505            Ok(distant_future),
506            query.get_result::<NaiveDateTime>(connection)
507        );
508    }
509
510    #[diesel_test_helper::test]
511    fn datetimes_encode_correctly() {
512        let connection = &mut connection();
513        let january_first_2000 = NaiveDate::from_ymd_opt(2000, 1, 1)
514            .unwrap()
515            .and_hms_opt(0, 0, 0)
516            .unwrap();
517        let query = select(datetime("2000-01-01 00:00:00.000000").eq(january_first_2000));
518        assert!(query.get_result::<bool>(connection).unwrap());
519
520        let distant_past = NaiveDate::from_ymd_opt(0, 4, 11)
521            .unwrap()
522            .and_hms_opt(20, 00, 20)
523            .unwrap();
524        let query = select(datetime("0000-04-11 20:00:20.000000").eq(distant_past));
525        assert!(query.get_result::<bool>(connection).unwrap());
526
527        let january_first_2018 = NaiveDate::from_ymd_opt(2018, 1, 1)
528            .unwrap()
529            .and_hms_opt(12, 00, 00)
530            .unwrap()
531            .with_nanosecond(500_000)
532            .unwrap();
533        let query = select(sql::<Timestamp>("'2018-01-01 12:00:00.000500'").eq(january_first_2018));
534        assert!(query.get_result::<bool>(connection).unwrap());
535
536        let distant_future = NaiveDate::from_ymd_opt(9999, 1, 8)
537            .unwrap()
538            .and_hms_opt(0, 0, 0)
539            .unwrap();
540        let query = select(datetime("9999-01-08 00:00:00.000000").eq(distant_future));
541        assert!(query.get_result::<bool>(connection).unwrap());
542    }
543
544    // This test decodes query results, which requires reading memory
545    // allocated by the native sqlite library. Miri cannot track the
546    // provenance of that memory when using `-Zmiri-native-lib`.
547    #[cfg(not(miri))]
548    #[diesel_test_helper::test]
549    fn insert_timestamptz_into_table_as_text() {
550        crate::table! {
551            #[allow(unused_parens)]
552            test_insert_timestamptz_into_table_as_text(id) {
553                id -> Integer,
554                timestamp_with_tz -> TimestamptzSqlite,
555            }
556        }
557        let conn = &mut connection();
558        crate::sql_query(
559            "CREATE TABLE test_insert_timestamptz_into_table_as_text(id INTEGER PRIMARY KEY, timestamp_with_tz TEXT);",
560        )
561        .execute(conn)
562        .unwrap();
563
564        let time: DateTime<Utc> = Utc.with_ymd_and_hms(1970, 1, 1, 0, 0, 0).single().unwrap();
565
566        crate::insert_into(test_insert_timestamptz_into_table_as_text::table)
567            .values(vec![(
568                test_insert_timestamptz_into_table_as_text::id.eq(1),
569                test_insert_timestamptz_into_table_as_text::timestamp_with_tz.eq(sql::<
570                    TimestamptzSqlite,
571                >(
572                    "'1970-01-01 00:00:00.000000+00:00'",
573                )),
574            )])
575            .execute(conn)
576            .unwrap();
577
578        let result = test_insert_timestamptz_into_table_as_text::table
579            .select(test_insert_timestamptz_into_table_as_text::timestamp_with_tz)
580            .get_result::<DateTime<Utc>>(conn)
581            .unwrap();
582        assert_eq!(result, time);
583    }
584
585    #[diesel_test_helper::test]
586    fn can_query_timestamptz_column_with_between() {
587        crate::table! {
588            #[allow(unused_parens)]
589            test_query_timestamptz_column_with_between(id) {
590                id -> Integer,
591                timestamp_with_tz -> TimestamptzSqlite,
592            }
593        }
594        let conn = &mut connection();
595        crate::sql_query(
596            "CREATE TABLE test_query_timestamptz_column_with_between(id INTEGER PRIMARY KEY, timestamp_with_tz TEXT);",
597        )
598        .execute(conn)
599        .unwrap();
600
601        crate::insert_into(test_query_timestamptz_column_with_between::table)
602            .values(vec![
603                (
604                    test_query_timestamptz_column_with_between::id.eq(1),
605                    test_query_timestamptz_column_with_between::timestamp_with_tz.eq(sql::<
606                        TimestamptzSqlite,
607                    >(
608                        "'1970-01-01 00:00:01.000000+00:00'",
609                    )),
610                ),
611                (
612                    test_query_timestamptz_column_with_between::id.eq(2),
613                    test_query_timestamptz_column_with_between::timestamp_with_tz.eq(sql::<
614                        TimestamptzSqlite,
615                    >(
616                        "'1970-01-01 00:00:02.000000+00:00'",
617                    )),
618                ),
619                (
620                    test_query_timestamptz_column_with_between::id.eq(3),
621                    test_query_timestamptz_column_with_between::timestamp_with_tz.eq(sql::<
622                        TimestamptzSqlite,
623                    >(
624                        "'1970-01-01 00:00:03.000000+00:00'",
625                    )),
626                ),
627                (
628                    test_query_timestamptz_column_with_between::id.eq(4),
629                    test_query_timestamptz_column_with_between::timestamp_with_tz.eq(sql::<
630                        TimestamptzSqlite,
631                    >(
632                        "'1970-01-01 00:00:04.000000+00:00'",
633                    )),
634                ),
635            ])
636            .execute(conn)
637            .unwrap();
638
639        let result = test_query_timestamptz_column_with_between::table
640            .select(test_query_timestamptz_column_with_between::timestamp_with_tz)
641            .filter(
642                test_query_timestamptz_column_with_between::timestamp_with_tz
643                    .gt(Utc.with_ymd_and_hms(1970, 1, 1, 0, 0, 0).single().unwrap()),
644            )
645            .filter(
646                test_query_timestamptz_column_with_between::timestamp_with_tz
647                    .lt(Utc.with_ymd_and_hms(1970, 1, 1, 0, 0, 4).single().unwrap()),
648            )
649            .count()
650            .get_result::<_>(conn);
651        assert_eq!(result, Ok(3));
652    }
653
654    #[diesel_test_helper::test]
655    fn unix_epoch_encodes_correctly_with_timezone() {
656        let connection = &mut connection();
657        // West one hour is negative offset
658        let time = FixedOffset::west_opt(3600)
659            .unwrap()
660            .with_ymd_and_hms(1970, 1, 1, 0, 0, 0)
661            .single()
662            .unwrap()
663            // 1ms
664            .with_nanosecond(1_000_000)
665            .unwrap();
666        let query = select(sql::<TimestamptzSqlite>("'1970-01-01 01:00:00.001+00:00'").eq(time));
667        assert!(query.get_result::<bool>(connection).unwrap());
668    }
669
670    #[diesel_test_helper::test]
671    fn unix_epoch_encodes_correctly_with_utc_timezone() {
672        let connection = &mut connection();
673        let time: DateTime<Utc> = Utc
674            .with_ymd_and_hms(1970, 1, 1, 0, 0, 0)
675            .single()
676            .unwrap()
677            // 1ms
678            .with_nanosecond(1_000_000)
679            .unwrap();
680        let query = select(sql::<TimestamptzSqlite>("'1970-01-01 00:00:00.001+00:00'").eq(time));
681        assert!(query.get_result::<bool>(connection).unwrap());
682
683        // and without millisecond
684        let time: DateTime<Utc> = Utc.with_ymd_and_hms(1970, 1, 1, 0, 0, 0).single().unwrap();
685        let query = select(sql::<TimestamptzSqlite>("'1970-01-01 00:00:00+00:00'").eq(time));
686        assert!(query.get_result::<bool>(connection).unwrap());
687    }
688
689    // This test decodes query results, which requires reading memory
690    // allocated by the native sqlite library. Miri cannot track the
691    // provenance of that memory when using `-Zmiri-native-lib`.
692    #[cfg(not(miri))]
693    #[diesel_test_helper::test]
694    fn unix_epoch_decodes_correctly_with_utc_timezone_in_all_possible_formats() {
695        let connection = &mut connection();
696        let time: DateTime<Utc> = Utc.with_ymd_and_hms(1970, 1, 1, 0, 0, 0).single().unwrap();
697        let valid_epoch_formats = vec![
698            "1970-01-01 00:00Z",
699            "1970-01-01 00:00:00Z",
700            "1970-01-01 00:00:00.000Z",
701            "1970-01-01 00:00:00.000000Z",
702            "1970-01-01T00:00Z",
703            "1970-01-01T00:00:00Z",
704            "1970-01-01T00:00:00.000Z",
705            "1970-01-01T00:00:00.000000Z",
706            "1970-01-01 00:00+00:00",
707            "1970-01-01 00:00:00+00:00",
708            "1970-01-01 00:00:00.000+00:00",
709            "1970-01-01 00:00:00.000000+00:00",
710            "1970-01-01T00:00+00:00",
711            "1970-01-01T00:00:00+00:00",
712            "1970-01-01T00:00:00.000+00:00",
713            "1970-01-01T00:00:00.000000+00:00",
714            "2440587.5",
715        ];
716
717        for s in valid_epoch_formats {
718            let epoch_from_sql =
719                select(sql::<TimestamptzSqlite>(&format!("'{s}'"))).get_result(connection);
720            assert_eq!(Ok(time), epoch_from_sql, "format {s} failed");
721        }
722    }
723}