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