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", 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", 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", 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(format!("Invalid time {text}").into())
113        })
114    }
115}
116
117#[cfg(all(feature = "sqlite", 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", 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                if let Some(timestamp) = parse_julian(julian_days) {
137                    return Ok(timestamp);
138                }
139            }
140
141            Err(format!("Invalid datetime {text}").into())
142        })
143    }
144}
145
146#[cfg(all(feature = "sqlite", 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#[cfg(all(feature = "sqlite", feature = "chrono"))]
155impl FromSql<TimestamptzSqlite, Sqlite> for NaiveDateTime {
156    fn from_sql(mut value: <Sqlite as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
157        value.parse_string(|text| {
158            for format in NAIVE_DATETIME_FORMATS {
159                if let Ok(dt) = Self::parse_from_str(text, format) {
160                    return Ok(dt);
161                }
162            }
163
164            if let Ok(julian_days) = text.parse::<f64>() {
165                if let Some(timestamp) = parse_julian(julian_days) {
166                    return Ok(timestamp);
167                }
168            }
169
170            Err(format!("Invalid datetime {text}").into())
171        })
172    }
173}
174
175#[cfg(all(feature = "sqlite", feature = "chrono"))]
176impl ToSql<TimestamptzSqlite, Sqlite> for NaiveDateTime {
177    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
178        out.set_value(self.format(ENCODE_NAIVE_DATETIME_FORMAT).to_string());
179        Ok(IsNull::No)
180    }
181}
182
183#[cfg(all(feature = "sqlite", feature = "chrono"))]
184impl FromSql<TimestamptzSqlite, Sqlite> for DateTime<Utc> {
185    fn from_sql(mut value: <Sqlite as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
186        // First try to parse the timezone
187        if let Ok(dt) = value.parse_string(|text| {
188            for format in DATETIME_FORMATS {
189                if let Ok(dt) = DateTime::parse_from_str(text, format) {
190                    return Ok(dt.with_timezone(&Utc));
191                }
192            }
193
194            Err(())
195        }) {
196            return Ok(dt);
197        }
198
199        // Fallback on assuming Utc
200        let naive_date_time =
201            <NaiveDateTime as FromSql<TimestamptzSqlite, Sqlite>>::from_sql(value)?;
202        Ok(Utc.from_utc_datetime(&naive_date_time))
203    }
204}
205
206#[cfg(all(feature = "sqlite", feature = "chrono"))]
207impl FromSql<TimestamptzSqlite, Sqlite> for DateTime<Local> {
208    fn from_sql(mut value: <Sqlite as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
209        // First try to parse the timezone
210        if let Ok(dt) = value.parse_string(|text| {
211            for format in DATETIME_FORMATS {
212                if let Ok(dt) = DateTime::parse_from_str(text, format) {
213                    return Ok(dt.with_timezone(&Local));
214                }
215            }
216
217            Err(())
218        }) {
219            return Ok(dt);
220        }
221
222        // Fallback on assuming Local
223        let naive_date_time =
224            <NaiveDateTime as FromSql<TimestamptzSqlite, Sqlite>>::from_sql(value)?;
225        Ok(Local::from_utc_datetime(&Local, &naive_date_time))
226    }
227}
228
229#[cfg(all(feature = "sqlite", feature = "chrono"))]
230impl<TZ: TimeZone> ToSql<TimestamptzSqlite, Sqlite> for DateTime<TZ> {
231    fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
232        // Converting to UTC ensures consistency
233        let dt_utc = self.with_timezone(&Utc);
234        out.set_value(dt_utc.format(ENCODE_DATETIME_FORMAT).to_string());
235        Ok(IsNull::No)
236    }
237}
238
239#[cfg(test)]
240#[allow(clippy::unwrap_used)]
241mod tests {
242    extern crate chrono;
243    extern crate dotenvy;
244
245    use self::chrono::{
246        DateTime, Duration, FixedOffset, NaiveDate, NaiveDateTime, NaiveTime, TimeZone, Timelike,
247        Utc,
248    };
249
250    use crate::dsl::{now, sql};
251    use crate::prelude::*;
252    use crate::select;
253    use crate::sql_types::{Text, Time, Timestamp, TimestamptzSqlite};
254    use crate::test_helpers::connection;
255
256    #[declare_sql_function]
257    extern "SQL" {
258        fn datetime(x: Text) -> Timestamp;
259        fn time(x: Text) -> Time;
260        fn date(x: Text) -> Date;
261    }
262
263    #[diesel_test_helper::test]
264    fn unix_epoch_encodes_correctly() {
265        let connection = &mut connection();
266        let time = NaiveDate::from_ymd_opt(1970, 1, 1)
267            .unwrap()
268            .and_hms_opt(0, 0, 0)
269            .unwrap();
270        let query = select(datetime("1970-01-01 00:00:00.000000").eq(time));
271        assert_eq!(Ok(true), query.get_result(connection));
272    }
273
274    #[diesel_test_helper::test]
275    fn unix_epoch_decodes_correctly_in_all_possible_formats() {
276        let connection = &mut connection();
277        let time = NaiveDate::from_ymd_opt(1970, 1, 1)
278            .unwrap()
279            .and_hms_opt(0, 0, 0)
280            .unwrap();
281        let valid_epoch_formats = vec![
282            "1970-01-01 00:00",
283            "1970-01-01 00:00:00",
284            "1970-01-01 00:00:00.000",
285            "1970-01-01 00:00:00.000000",
286            "1970-01-01T00:00",
287            "1970-01-01T00:00:00",
288            "1970-01-01T00:00:00.000",
289            "1970-01-01T00:00:00.000000",
290            "1970-01-01 00:00Z",
291            "1970-01-01 00:00:00Z",
292            "1970-01-01 00:00:00.000Z",
293            "1970-01-01 00:00:00.000000Z",
294            "1970-01-01T00:00Z",
295            "1970-01-01T00:00:00Z",
296            "1970-01-01T00:00:00.000Z",
297            "1970-01-01T00:00:00.000000Z",
298            "1970-01-01 00:00+00:00",
299            "1970-01-01 00:00:00+00:00",
300            "1970-01-01 00:00:00.000+00:00",
301            "1970-01-01 00:00:00.000000+00:00",
302            "1970-01-01T00:00+00:00",
303            "1970-01-01T00:00:00+00:00",
304            "1970-01-01T00:00:00.000+00:00",
305            "1970-01-01T00:00:00.000000+00:00",
306            "1970-01-01 00:00+01:00",
307            "1970-01-01 00:00:00+01:00",
308            "1970-01-01 00:00:00.000+01:00",
309            "1970-01-01 00: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            "1970-01-01T00:00-01:00",
319            "1970-01-01T00:00:00-01:00",
320            "1970-01-01T00:00:00.000-01:00",
321            "1970-01-01T00:00:00.000000-01:00",
322            "2440587.5",
323        ];
324
325        for s in valid_epoch_formats {
326            let epoch_from_sql =
327                select(sql::<Timestamp>(&format!("'{}'", s))).get_result(connection);
328            assert_eq!(Ok(time), epoch_from_sql, "format {} failed", s);
329        }
330    }
331
332    #[diesel_test_helper::test]
333    fn times_relative_to_now_encode_correctly() {
334        let connection = &mut connection();
335        let time = Utc::now().naive_utc() + Duration::try_seconds(60).unwrap();
336        let query = select(now.lt(time));
337        assert_eq!(Ok(true), query.get_result(connection));
338
339        let time = Utc::now().naive_utc() - Duration::try_seconds(600).unwrap();
340        let query = select(now.gt(time));
341        assert_eq!(Ok(true), query.get_result(connection));
342    }
343
344    #[diesel_test_helper::test]
345    fn times_of_day_encode_correctly() {
346        let connection = &mut connection();
347
348        let midnight = NaiveTime::from_hms_opt(0, 0, 0).unwrap();
349        let query = select(time("00:00:00.000000").eq(midnight));
350        assert!(query.get_result::<bool>(connection).unwrap());
351
352        let noon = NaiveTime::from_hms_opt(12, 0, 0).unwrap();
353        let query = select(time("12:00:00.000000").eq(noon));
354        assert!(query.get_result::<bool>(connection).unwrap());
355
356        let roughly_half_past_eleven = NaiveTime::from_hms_micro_opt(23, 37, 4, 2200).unwrap();
357        let query = select(sql::<Time>("'23:37:04.002200'").eq(roughly_half_past_eleven));
358        assert!(query.get_result::<bool>(connection).unwrap());
359    }
360
361    #[diesel_test_helper::test]
362    fn times_of_day_decode_correctly() {
363        let connection = &mut connection();
364        let midnight = NaiveTime::from_hms_opt(0, 0, 0).unwrap();
365        let valid_midnight_formats = &[
366            "00:00",
367            "00:00:00",
368            "00:00:00.000",
369            "00:00:00.000000",
370            "00:00Z",
371            "00:00:00Z",
372            "00:00:00.000Z",
373            "00:00:00.000000Z",
374            "00:00+00:00",
375            "00:00:00+00:00",
376            "00:00:00.000+00:00",
377            "00:00:00.000000+00:00",
378            "00:00+01:00",
379            "00:00:00+01:00",
380            "00:00:00.000+01:00",
381            "00:00:00.000000+01:00",
382            "00:00-01:00",
383            "00:00:00-01:00",
384            "00:00:00.000-01:00",
385            "00:00:00.000000-01:00",
386        ];
387        for format in valid_midnight_formats {
388            let query = select(sql::<Time>(&format!("'{}'", format)));
389            assert_eq!(
390                Ok(midnight),
391                query.get_result::<NaiveTime>(connection),
392                "format {} failed",
393                format
394            );
395        }
396
397        let noon = NaiveTime::from_hms_opt(12, 0, 0).unwrap();
398        let query = select(sql::<Time>("'12:00:00'"));
399        assert_eq!(Ok(noon), query.get_result::<NaiveTime>(connection));
400
401        let roughly_half_past_eleven = NaiveTime::from_hms_micro_opt(23, 37, 4, 2200).unwrap();
402        let query = select(sql::<Time>("'23:37:04.002200'"));
403        assert_eq!(
404            Ok(roughly_half_past_eleven),
405            query.get_result::<NaiveTime>(connection)
406        );
407    }
408
409    #[diesel_test_helper::test]
410    fn dates_encode_correctly() {
411        let connection = &mut connection();
412        let january_first_2000 = NaiveDate::from_ymd_opt(2000, 1, 1).unwrap();
413        let query = select(date("2000-01-01").eq(january_first_2000));
414        assert!(query.get_result::<bool>(connection).unwrap());
415
416        let distant_past = NaiveDate::from_ymd_opt(0, 4, 11).unwrap();
417        let query = select(date("0000-04-11").eq(distant_past));
418        assert!(query.get_result::<bool>(connection).unwrap());
419
420        let january_first_2018 = NaiveDate::from_ymd_opt(2018, 1, 1).unwrap();
421        let query = select(date("2018-01-01").eq(january_first_2018));
422        assert!(query.get_result::<bool>(connection).unwrap());
423
424        let distant_future = NaiveDate::from_ymd_opt(9999, 1, 8).unwrap();
425        let query = select(date("9999-01-08").eq(distant_future));
426        assert!(query.get_result::<bool>(connection).unwrap());
427    }
428
429    #[diesel_test_helper::test]
430    fn dates_decode_correctly() {
431        let connection = &mut connection();
432        let january_first_2000 = NaiveDate::from_ymd_opt(2000, 1, 1).unwrap();
433        let query = select(date("2000-01-01"));
434        assert_eq!(
435            Ok(january_first_2000),
436            query.get_result::<NaiveDate>(connection)
437        );
438
439        let distant_past = NaiveDate::from_ymd_opt(0, 4, 11).unwrap();
440        let query = select(date("0000-04-11"));
441        assert_eq!(Ok(distant_past), query.get_result::<NaiveDate>(connection));
442
443        let january_first_2018 = NaiveDate::from_ymd_opt(2018, 1, 1).unwrap();
444        let query = select(date("2018-01-01"));
445        assert_eq!(
446            Ok(january_first_2018),
447            query.get_result::<NaiveDate>(connection)
448        );
449
450        let distant_future = NaiveDate::from_ymd_opt(9999, 1, 8).unwrap();
451        let query = select(date("9999-01-08"));
452        assert_eq!(
453            Ok(distant_future),
454            query.get_result::<NaiveDate>(connection)
455        );
456    }
457
458    #[diesel_test_helper::test]
459    fn datetimes_decode_correctly() {
460        let connection = &mut connection();
461        let january_first_2000 = NaiveDate::from_ymd_opt(2000, 1, 1)
462            .unwrap()
463            .and_hms_opt(1, 1, 1)
464            .unwrap();
465        let query = select(datetime("2000-01-01 01:01:01.000000"));
466        assert_eq!(
467            Ok(january_first_2000),
468            query.get_result::<NaiveDateTime>(connection)
469        );
470
471        let distant_past = NaiveDate::from_ymd_opt(0, 4, 11)
472            .unwrap()
473            .and_hms_opt(2, 2, 2)
474            .unwrap();
475        let query = select(datetime("0000-04-11 02:02:02.000000"));
476        assert_eq!(
477            Ok(distant_past),
478            query.get_result::<NaiveDateTime>(connection)
479        );
480
481        let january_first_2018 = NaiveDate::from_ymd_opt(2018, 1, 1).unwrap();
482        let query = select(date("2018-01-01"));
483        assert_eq!(
484            Ok(january_first_2018),
485            query.get_result::<NaiveDate>(connection)
486        );
487
488        let distant_future = NaiveDate::from_ymd_opt(9999, 1, 8)
489            .unwrap()
490            .and_hms_opt(23, 59, 59)
491            .unwrap()
492            .with_nanosecond(100_000)
493            .unwrap();
494        let query = select(sql::<Timestamp>("'9999-01-08 23:59:59.000100'"));
495        assert_eq!(
496            Ok(distant_future),
497            query.get_result::<NaiveDateTime>(connection)
498        );
499    }
500
501    #[diesel_test_helper::test]
502    fn datetimes_encode_correctly() {
503        let connection = &mut connection();
504        let january_first_2000 = NaiveDate::from_ymd_opt(2000, 1, 1)
505            .unwrap()
506            .and_hms_opt(0, 0, 0)
507            .unwrap();
508        let query = select(datetime("2000-01-01 00:00:00.000000").eq(january_first_2000));
509        assert!(query.get_result::<bool>(connection).unwrap());
510
511        let distant_past = NaiveDate::from_ymd_opt(0, 4, 11)
512            .unwrap()
513            .and_hms_opt(20, 00, 20)
514            .unwrap();
515        let query = select(datetime("0000-04-11 20:00:20.000000").eq(distant_past));
516        assert!(query.get_result::<bool>(connection).unwrap());
517
518        let january_first_2018 = NaiveDate::from_ymd_opt(2018, 1, 1)
519            .unwrap()
520            .and_hms_opt(12, 00, 00)
521            .unwrap()
522            .with_nanosecond(500_000)
523            .unwrap();
524        let query = select(sql::<Timestamp>("'2018-01-01 12:00:00.000500'").eq(january_first_2018));
525        assert!(query.get_result::<bool>(connection).unwrap());
526
527        let distant_future = NaiveDate::from_ymd_opt(9999, 1, 8)
528            .unwrap()
529            .and_hms_opt(0, 0, 0)
530            .unwrap();
531        let query = select(datetime("9999-01-08 00:00:00.000000").eq(distant_future));
532        assert!(query.get_result::<bool>(connection).unwrap());
533    }
534
535    #[diesel_test_helper::test]
536    fn insert_timestamptz_into_table_as_text() {
537        crate::table! {
538            #[allow(unused_parens)]
539            test_insert_timestamptz_into_table_as_text(id) {
540                id -> Integer,
541                timestamp_with_tz -> TimestamptzSqlite,
542            }
543        }
544        let conn = &mut connection();
545        crate::sql_query(
546            "CREATE TABLE test_insert_timestamptz_into_table_as_text(id INTEGER PRIMARY KEY, timestamp_with_tz TEXT);",
547        )
548        .execute(conn)
549        .unwrap();
550
551        let time: DateTime<Utc> = Utc.with_ymd_and_hms(1970, 1, 1, 0, 0, 0).single().unwrap();
552
553        crate::insert_into(test_insert_timestamptz_into_table_as_text::table)
554            .values(vec![(
555                test_insert_timestamptz_into_table_as_text::id.eq(1),
556                test_insert_timestamptz_into_table_as_text::timestamp_with_tz.eq(sql::<
557                    TimestamptzSqlite,
558                >(
559                    "'1970-01-01 00:00:00.000000+00:00'",
560                )),
561            )])
562            .execute(conn)
563            .unwrap();
564
565        let result = test_insert_timestamptz_into_table_as_text::table
566            .select(test_insert_timestamptz_into_table_as_text::timestamp_with_tz)
567            .get_result::<DateTime<Utc>>(conn)
568            .unwrap();
569        assert_eq!(result, time);
570    }
571
572    #[diesel_test_helper::test]
573    fn can_query_timestamptz_column_with_between() {
574        crate::table! {
575            #[allow(unused_parens)]
576            test_query_timestamptz_column_with_between(id) {
577                id -> Integer,
578                timestamp_with_tz -> TimestamptzSqlite,
579            }
580        }
581        let conn = &mut connection();
582        crate::sql_query(
583            "CREATE TABLE test_query_timestamptz_column_with_between(id INTEGER PRIMARY KEY, timestamp_with_tz TEXT);",
584        )
585        .execute(conn)
586        .unwrap();
587
588        crate::insert_into(test_query_timestamptz_column_with_between::table)
589            .values(vec![
590                (
591                    test_query_timestamptz_column_with_between::id.eq(1),
592                    test_query_timestamptz_column_with_between::timestamp_with_tz.eq(sql::<
593                        TimestamptzSqlite,
594                    >(
595                        "'1970-01-01 00:00:01.000000+00:00'",
596                    )),
597                ),
598                (
599                    test_query_timestamptz_column_with_between::id.eq(2),
600                    test_query_timestamptz_column_with_between::timestamp_with_tz.eq(sql::<
601                        TimestamptzSqlite,
602                    >(
603                        "'1970-01-01 00:00:02.000000+00:00'",
604                    )),
605                ),
606                (
607                    test_query_timestamptz_column_with_between::id.eq(3),
608                    test_query_timestamptz_column_with_between::timestamp_with_tz.eq(sql::<
609                        TimestamptzSqlite,
610                    >(
611                        "'1970-01-01 00:00:03.000000+00:00'",
612                    )),
613                ),
614                (
615                    test_query_timestamptz_column_with_between::id.eq(4),
616                    test_query_timestamptz_column_with_between::timestamp_with_tz.eq(sql::<
617                        TimestamptzSqlite,
618                    >(
619                        "'1970-01-01 00:00:04.000000+00:00'",
620                    )),
621                ),
622            ])
623            .execute(conn)
624            .unwrap();
625
626        let result = test_query_timestamptz_column_with_between::table
627            .select(test_query_timestamptz_column_with_between::timestamp_with_tz)
628            .filter(
629                test_query_timestamptz_column_with_between::timestamp_with_tz
630                    .gt(Utc.with_ymd_and_hms(1970, 1, 1, 0, 0, 0).single().unwrap()),
631            )
632            .filter(
633                test_query_timestamptz_column_with_between::timestamp_with_tz
634                    .lt(Utc.with_ymd_and_hms(1970, 1, 1, 0, 0, 4).single().unwrap()),
635            )
636            .count()
637            .get_result::<_>(conn);
638        assert_eq!(result, Ok(3));
639    }
640
641    #[diesel_test_helper::test]
642    fn unix_epoch_encodes_correctly_with_timezone() {
643        let connection = &mut connection();
644        // West one hour is negative offset
645        let time = FixedOffset::west_opt(3600)
646            .unwrap()
647            .with_ymd_and_hms(1970, 1, 1, 0, 0, 0)
648            .single()
649            .unwrap()
650            // 1ms
651            .with_nanosecond(1_000_000)
652            .unwrap();
653        let query = select(sql::<TimestamptzSqlite>("'1970-01-01 01:00:00.001+00:00'").eq(time));
654        assert!(query.get_result::<bool>(connection).unwrap());
655    }
656
657    #[diesel_test_helper::test]
658    fn unix_epoch_encodes_correctly_with_utc_timezone() {
659        let connection = &mut connection();
660        let time: DateTime<Utc> = Utc
661            .with_ymd_and_hms(1970, 1, 1, 0, 0, 0)
662            .single()
663            .unwrap()
664            // 1ms
665            .with_nanosecond(1_000_000)
666            .unwrap();
667        let query = select(sql::<TimestamptzSqlite>("'1970-01-01 00:00:00.001+00:00'").eq(time));
668        assert!(query.get_result::<bool>(connection).unwrap());
669
670        // and without millisecond
671        let time: DateTime<Utc> = Utc.with_ymd_and_hms(1970, 1, 1, 0, 0, 0).single().unwrap();
672        let query = select(sql::<TimestamptzSqlite>("'1970-01-01 00:00:00+00:00'").eq(time));
673        assert!(query.get_result::<bool>(connection).unwrap());
674    }
675
676    #[diesel_test_helper::test]
677    fn unix_epoch_decodes_correctly_with_utc_timezone_in_all_possible_formats() {
678        let connection = &mut connection();
679        let time: DateTime<Utc> = Utc.with_ymd_and_hms(1970, 1, 1, 0, 0, 0).single().unwrap();
680        let valid_epoch_formats = vec![
681            "1970-01-01 00:00Z",
682            "1970-01-01 00:00:00Z",
683            "1970-01-01 00:00:00.000Z",
684            "1970-01-01 00:00:00.000000Z",
685            "1970-01-01T00:00Z",
686            "1970-01-01T00:00:00Z",
687            "1970-01-01T00:00:00.000Z",
688            "1970-01-01T00:00:00.000000Z",
689            "1970-01-01 00:00+00:00",
690            "1970-01-01 00:00:00+00:00",
691            "1970-01-01 00:00:00.000+00:00",
692            "1970-01-01 00:00:00.000000+00:00",
693            "1970-01-01T00:00+00:00",
694            "1970-01-01T00:00:00+00:00",
695            "1970-01-01T00:00:00.000+00:00",
696            "1970-01-01T00:00:00.000000+00:00",
697            "2440587.5",
698        ];
699
700        for s in valid_epoch_formats {
701            let epoch_from_sql =
702                select(sql::<TimestamptzSqlite>(&format!("'{}'", s))).get_result(connection);
703            assert_eq!(Ok(time), epoch_from_sql, "format {} failed", s);
704        }
705    }
706}
diesel/sqlite/types/date_and_time/chrono.rs

diesel/sqlite/types/date_and_time/
chrono.rs
