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