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

diesel/sqlite/types/date_and_time/
time.rs
