diesel/sqlite/types/date_and_time/
time.rs

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

diesel/sqlite/types/date_and_time/
time.rs
