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    define_sql_function!(fn datetime(x: Text) -> Timestamp);
307    define_sql_function!(fn time(x: Text) -> Time);
308    define_sql_function!(fn date(x: Text) -> Date);
309
310    #[test]
311    fn unix_epoch_encodes_correctly() {
312        let connection = &mut connection();
313        let time = datetime!(1970-1-1 0:0:0);
314        let query = select(datetime("1970-01-01 00:00:00.000000").eq(time));
315        assert_eq!(Ok(true), query.get_result(connection));
316    }
317
318    #[test]
319    fn unix_epoch_decodes_correctly_in_all_possible_formats() {
320        let connection = &mut connection();
321        let time = datetime!(1970-1-1 0:0:0);
322        let valid_epoch_formats = vec![
323            "1970-01-01 00:00",
324            "1970-01-01 00:00:00",
325            "1970-01-01 00:00:00.000",
326            "1970-01-01 00:00:00.000000",
327            "1970-01-01T00:00",
328            "1970-01-01T00:00:00",
329            "1970-01-01T00:00:00.000",
330            "1970-01-01T00:00:00.000000",
331            "1970-01-01 00:00Z",
332            "1970-01-01 00:00:00Z",
333            "1970-01-01 00:00:00.000Z",
334            "1970-01-01 00:00:00.000000Z",
335            "1970-01-01T00:00Z",
336            "1970-01-01T00:00:00Z",
337            "1970-01-01T00:00:00.000Z",
338            "1970-01-01T00:00:00.000000Z",
339            "1970-01-01 00:00+00:00",
340            "1970-01-01 00:00:00+00:00",
341            "1970-01-01 00:00:00.000+00:00",
342            "1970-01-01 00:00:00.000000+00:00",
343            "1970-01-01T00:00+00:00",
344            "1970-01-01T00:00:00+00:00",
345            "1970-01-01T00:00:00.000+00:00",
346            "1970-01-01T00:00:00.000000+00:00",
347            "1970-01-01 00:00+01:00",
348            "1970-01-01 00:00:00+01:00",
349            "1970-01-01 00:00:00.000+01:00",
350            "1970-01-01 00:00:00.000000+01:00",
351            "1970-01-01T00:00+01:00",
352            "1970-01-01T00:00:00+01:00",
353            "1970-01-01T00:00:00.000+01:00",
354            "1970-01-01T00:00:00.000000+01:00",
355            "1970-01-01T00:00-01:00",
356            "1970-01-01T00:00:00-01:00",
357            "1970-01-01T00:00:00.000-01:00",
358            "1970-01-01T00:00:00.000000-01:00",
359            "1970-01-01T00:00-01:00",
360            "1970-01-01T00:00:00-01:00",
361            "1970-01-01T00:00:00.000-01:00",
362            "1970-01-01T00:00:00.000000-01:00",
363            "2440587.5",
364        ];
365
366        for s in valid_epoch_formats {
367            let epoch_from_sql =
368                select(sql::<Timestamp>(&format!("'{}'", s))).get_result(connection);
369            assert_eq!(Ok(time), epoch_from_sql, "format {} failed", s);
370        }
371    }
372
373    #[test]
374    fn times_relative_to_now_encode_correctly() {
375        let connection = &mut connection();
376        let time = naive_utc(OffsetDateTime::now_utc()) + Duration::seconds(60);
377        let query = select(now.lt(time));
378        assert_eq!(Ok(true), query.get_result(connection));
379
380        let time = naive_utc(OffsetDateTime::now_utc()) - Duration::seconds(600);
381        let query = select(now.gt(time));
382        assert_eq!(Ok(true), query.get_result(connection));
383    }
384
385    #[test]
386    fn times_of_day_encode_correctly() {
387        let connection = &mut connection();
388
389        let midnight = NaiveTime::from_hms(0, 0, 0).unwrap();
390        let query = select(time("00:00:00").eq(midnight));
391        assert!(query.get_result::<bool>(connection).unwrap());
392
393        let noon = NaiveTime::from_hms(12, 0, 0).unwrap();
394        let query = select(time("12:00:00").eq(noon));
395        assert!(query.get_result::<bool>(connection).unwrap());
396
397        let roughly_half_past_eleven = NaiveTime::from_hms_micro(23, 37, 4, 2200).unwrap();
398        let query = select(sql::<Time>("'23:37:04.0022'").eq(roughly_half_past_eleven));
399        assert!(query.get_result::<bool>(connection).unwrap());
400    }
401
402    #[test]
403    fn times_of_day_decode_correctly() {
404        let connection = &mut connection();
405        let midnight = NaiveTime::from_hms(0, 0, 0).unwrap();
406        let valid_midnight_formats = &[
407            "00:00",
408            "00:00:00",
409            "00:00:00.000",
410            "00:00:00.000000",
411            "00:00Z",
412            "00:00:00Z",
413            "00:00:00.000Z",
414            "00:00:00.000000Z",
415            "00:00+00:00",
416            "00:00:00+00:00",
417            "00:00:00.000+00:00",
418            "00:00:00.000000+00:00",
419            "00:00+01:00",
420            "00:00:00+01:00",
421            "00:00:00.000+01:00",
422            "00:00:00.000000+01:00",
423            "00:00-01:00",
424            "00:00:00-01:00",
425            "00:00:00.000-01:00",
426            "00:00:00.000000-01:00",
427        ];
428        for format in valid_midnight_formats {
429            let query = select(sql::<Time>(&format!("'{}'", format)));
430            assert_eq!(
431                Ok(midnight),
432                query.get_result::<NaiveTime>(connection),
433                "format {} failed",
434                format
435            );
436        }
437
438        let noon = NaiveTime::from_hms(12, 0, 0).unwrap();
439        let query = select(sql::<Time>("'12:00:00'"));
440        assert_eq!(Ok(noon), query.get_result::<NaiveTime>(connection));
441
442        let roughly_half_past_eleven = NaiveTime::from_hms_micro(23, 37, 4, 2200).unwrap();
443        let query = select(sql::<Time>("'23:37:04.002200'"));
444        assert_eq!(
445            Ok(roughly_half_past_eleven),
446            query.get_result::<NaiveTime>(connection)
447        );
448    }
449
450    #[test]
451    fn dates_encode_correctly() {
452        let connection = &mut connection();
453        let january_first_2000 = date!(2000 - 1 - 1);
454        let query = select(date("2000-01-01").eq(january_first_2000));
455        assert!(query.get_result::<bool>(connection).unwrap());
456
457        let distant_past = date!(0 - 4 - 11);
458        let query = select(date("0000-04-11").eq(distant_past));
459        assert!(query.get_result::<bool>(connection).unwrap());
460
461        let january_first_2018 = date!(2018 - 1 - 1);
462        let query = select(date("2018-01-01").eq(january_first_2018));
463        assert!(query.get_result::<bool>(connection).unwrap());
464
465        let distant_future = date!(9999 - 1 - 8);
466        let query = select(date("9999-01-08").eq(distant_future));
467        assert!(query.get_result::<bool>(connection).unwrap());
468    }
469
470    #[test]
471    fn dates_decode_correctly() {
472        let connection = &mut connection();
473        let january_first_2000 = date!(2000 - 1 - 1);
474        let query = select(date("2000-01-01"));
475        assert_eq!(
476            Ok(january_first_2000),
477            query.get_result::<NaiveDate>(connection)
478        );
479
480        let distant_past = date!(0 - 4 - 11);
481        let query = select(date("0000-04-11"));
482        assert_eq!(Ok(distant_past), query.get_result::<NaiveDate>(connection));
483
484        let january_first_2018 = date!(2018 - 1 - 1);
485        let query = select(date("2018-01-01"));
486        assert_eq!(
487            Ok(january_first_2018),
488            query.get_result::<NaiveDate>(connection)
489        );
490
491        let distant_future = date!(9999 - 1 - 8);
492        let query = select(date("9999-01-08"));
493        assert_eq!(
494            Ok(distant_future),
495            query.get_result::<NaiveDate>(connection)
496        );
497    }
498
499    #[test]
500    fn datetimes_decode_correctly() {
501        let connection = &mut connection();
502        let january_first_2000 = datetime!(2000-1-1 1:1:1);
503        let query = select(datetime("2000-01-01 01:01:01.000000"));
504        assert_eq!(
505            Ok(january_first_2000),
506            query.get_result::<PrimitiveDateTime>(connection)
507        );
508
509        let distant_past = datetime!(0-4-11 2:2:2);
510        let query = select(datetime("0000-04-11 02:02:02.000000"));
511        assert_eq!(
512            Ok(distant_past),
513            query.get_result::<PrimitiveDateTime>(connection)
514        );
515
516        let january_first_2018 = date!(2018 - 1 - 1);
517        let query = select(date("2018-01-01"));
518        assert_eq!(
519            Ok(january_first_2018),
520            query.get_result::<NaiveDate>(connection)
521        );
522
523        let distant_future = datetime!(9999 - 1 - 8 23:59:59.0001);
524        let query = select(sql::<Timestamp>("'9999-01-08 23:59:59.000100'"));
525        assert_eq!(
526            Ok(distant_future),
527            query.get_result::<PrimitiveDateTime>(connection)
528        );
529    }
530
531    #[test]
532    fn datetimes_encode_correctly() {
533        let connection = &mut connection();
534        let january_first_2000 = datetime!(2000-1-1 0:0:0);
535        let query = select(datetime("2000-01-01 00:00:00").eq(january_first_2000));
536        assert!(query.get_result::<bool>(connection).unwrap());
537
538        let distant_past = datetime!(0-4-11 20:00:20);
539        let query = select(datetime("0000-04-11 20:00:20").eq(distant_past));
540        assert!(query.get_result::<bool>(connection).unwrap());
541
542        let january_first_2018 = datetime!(2018 - 1 - 1 12:00:00.0005);
543        let query = select(sql::<Timestamp>("'2018-01-01 12:00:00.0005'").eq(january_first_2018));
544        assert!(query.get_result::<bool>(connection).unwrap());
545
546        let distant_future = datetime!(9999-1-8 0:0:0);
547        let query = select(datetime("9999-01-08 00:00:00").eq(distant_future));
548        assert!(query.get_result::<bool>(connection).unwrap());
549    }
550
551    #[test]
552    fn insert_timestamptz_into_table_as_text() {
553        crate::table! {
554            #[allow(unused_parens)]
555            test_insert_timestamptz_into_table_as_text(id) {
556                id -> Integer,
557                timestamp_with_tz -> TimestamptzSqlite,
558            }
559        }
560        let conn = &mut connection();
561        crate::sql_query(
562            "CREATE TABLE test_insert_timestamptz_into_table_as_text(id INTEGER PRIMARY KEY, timestamp_with_tz TEXT);",
563        )
564        .execute(conn)
565        .unwrap();
566
567        let time: OffsetDateTime = datetime!(1970-1-1 0:0:0.0 utc);
568
569        crate::insert_into(test_insert_timestamptz_into_table_as_text::table)
570            .values(vec![(
571                test_insert_timestamptz_into_table_as_text::id.eq(1),
572                test_insert_timestamptz_into_table_as_text::timestamp_with_tz.eq(sql::<
573                    TimestamptzSqlite,
574                >(
575                    "'1970-01-01 00:00:00.000000+00:00'",
576                )),
577            )])
578            .execute(conn)
579            .unwrap();
580
581        let result = test_insert_timestamptz_into_table_as_text::table
582            .select(test_insert_timestamptz_into_table_as_text::timestamp_with_tz)
583            .get_result::<OffsetDateTime>(conn)
584            .unwrap();
585        assert_eq!(result, time);
586    }
587
588    #[test]
589    fn can_query_timestamptz_column_with_between() {
590        crate::table! {
591            #[allow(unused_parens)]
592            test_query_timestamptz_column_with_between(id) {
593                id -> Integer,
594                timestamp_with_tz -> TimestamptzSqlite,
595            }
596        }
597        let conn = &mut connection();
598        crate::sql_query(
599            "CREATE TABLE test_query_timestamptz_column_with_between(id INTEGER PRIMARY KEY, timestamp_with_tz TEXT);",
600        )
601        .execute(conn)
602        .unwrap();
603
604        crate::insert_into(test_query_timestamptz_column_with_between::table)
605            .values(vec![
606                (
607                    test_query_timestamptz_column_with_between::id.eq(1),
608                    test_query_timestamptz_column_with_between::timestamp_with_tz.eq(sql::<
609                        TimestamptzSqlite,
610                    >(
611                        "'1970-01-01 00:00:01.000000+00:00'",
612                    )),
613                ),
614                (
615                    test_query_timestamptz_column_with_between::id.eq(2),
616                    test_query_timestamptz_column_with_between::timestamp_with_tz.eq(sql::<
617                        TimestamptzSqlite,
618                    >(
619                        "'1970-01-01 00:00:02.000000+00:00'",
620                    )),
621                ),
622                (
623                    test_query_timestamptz_column_with_between::id.eq(3),
624                    test_query_timestamptz_column_with_between::timestamp_with_tz.eq(sql::<
625                        TimestamptzSqlite,
626                    >(
627                        "'1970-01-01 00:00:03.000000+00:00'",
628                    )),
629                ),
630                (
631                    test_query_timestamptz_column_with_between::id.eq(4),
632                    test_query_timestamptz_column_with_between::timestamp_with_tz.eq(sql::<
633                        TimestamptzSqlite,
634                    >(
635                        "'1970-01-01 00:00:04.000000+00:00'",
636                    )),
637                ),
638            ])
639            .execute(conn)
640            .unwrap();
641
642        let result = test_query_timestamptz_column_with_between::table
643            .select(test_query_timestamptz_column_with_between::timestamp_with_tz)
644            .filter(
645                test_query_timestamptz_column_with_between::timestamp_with_tz
646                    .gt(datetime!(1970-1-1 0:0:0.0 utc)),
647            )
648            .filter(
649                test_query_timestamptz_column_with_between::timestamp_with_tz
650                    .lt(datetime!(1970-1-1 0:0:4.0 utc)),
651            )
652            .count()
653            .get_result::<_>(conn);
654        assert_eq!(result, Ok(3));
655    }
656
657    #[test]
658    fn unix_epoch_encodes_correctly_with_timezone() {
659        let connection = &mut connection();
660        // West one hour is negative offset
661        let time = datetime!(1970-1-1 0:00:00.001 -1:00);
662        let query = select(sql::<TimestamptzSqlite>("'1970-01-01 01:00:00.001+00:00'").eq(time));
663        assert!(query.get_result::<bool>(connection).unwrap());
664    }
665
666    #[test]
667    fn unix_epoch_encodes_correctly_with_utc_timezone() {
668        let connection = &mut connection();
669        let time: OffsetDateTime = datetime!(1970-1-1 0:0:0.001 utc);
670        let query = select(sql::<TimestamptzSqlite>("'1970-01-01 00:00:00.001+00:00'").eq(time));
671        assert!(query.get_result::<bool>(connection).unwrap());
672
673        // and without millisecond
674        let time: OffsetDateTime = datetime!(1970-1-1 0:0:0 utc);
675        let query = select(sql::<TimestamptzSqlite>("'1970-01-01 00:00:00+00:00'").eq(time));
676        assert!(query.get_result::<bool>(connection).unwrap());
677    }
678
679    #[test]
680    fn unix_epoch_decodes_correctly_with_utc_timezone_in_all_possible_formats() {
681        let connection = &mut connection();
682        let time: OffsetDateTime = datetime!(1970-1-1 0:0:0 utc);
683        let valid_epoch_formats = vec![
684            "1970-01-01 00:00Z",
685            "1970-01-01 00:00:00Z",
686            "1970-01-01 00:00:00.000Z",
687            "1970-01-01 00:00:00.000000Z",
688            "1970-01-01T00:00Z",
689            "1970-01-01T00:00:00Z",
690            "1970-01-01T00:00:00.000Z",
691            "1970-01-01T00:00:00.000000Z",
692            "1970-01-01 00:00+00:00",
693            "1970-01-01 00:00:00+00:00",
694            "1970-01-01 00:00:00.000+00:00",
695            "1970-01-01 00:00:00.000000+00:00",
696            "1970-01-01T00:00+00:00",
697            "1970-01-01T00:00:00+00:00",
698            "1970-01-01T00:00:00.000+00:00",
699            "1970-01-01T00:00:00.000000+00:00",
700            "2440587.5",
701        ];
702
703        for s in valid_epoch_formats {
704            let epoch_from_sql =
705                select(sql::<TimestamptzSqlite>(&format!("'{}'", s))).get_result(connection);
706            assert_eq!(Ok(time), epoch_from_sql, "format {} failed", s);
707        }
708    }
709}
diesel/sqlite/types/date_and_time/time.rs

diesel/sqlite/types/date_and_time/
time.rs
