Repository: diesel-rs/diesel (language: rust)

Issue:
Wrong documentation around sqlite datetime / inference not taking tz into account
<!--
If you want to report a bug, we added some points below which help us track down the problem faster.
-->

## Setup

### Versions

- **Rust:1.61**
- **Diesel:2.0.0**
- **Database:Sqlite**
- **Operating System**

### Feature Flags

- **diesel: time**

## Problem Description

It looks like the documentation surrounding timestamps for Sqlite is wrong.
Specially https://github.com/diesel-rs/diesel/blob/e9a951f1dc8ae893c31002ea2e00690c91e86ce4/diesel/src/sql_types/mod.rs#L335 and https://github.com/diesel-rs/diesel/blob/e9a951f1dc8ae893c31002ea2e00690c91e86ce4/diesel/src/sql_types/mod.rs#L342

The `FromSql` to `ToSql` traits are only implemented for `TimestamptzSqlite` for `OffsetDateTime`. Which currently you have to change manually in the schema since there is no inference for it.

### What are you trying to accomplish?

- Allow inference to automatically detect the TZ version
- Change the documentation to reflect that
- (Maybe) Rename `TimestamptzSqlite` since it's pretty ugly, but I guess there is a reason behind that choice

I will do the change if we agree on what the behaviour should be.

## Checklist

- [x] I have already looked over the [issue tracker](https://github.com/diesel-rs/diesel/issues)  and the [disussion forum](https://github.com/diesel-rs/diesel/discussions)  for similar possible closed issues.
<!--
If you are unsure if your issue is a duplicate of an existing issue please link the issue in question here
--> 
- [x] This issue can be reproduced on Rust's stable channel. (Your issue will be
  closed if this is not the case)
- [x] This issue can be reproduced without requiring a third party crate

<!--
Thank you for your submission!  You're helping make Diesel more robust 🎉

We'll try to respond as quickly as possible.
-->



Discussion / hints:
> The FromSql to ToSql traits are only implemented for TimestamptzSqlite for OffsetDateTime.

This is correct. The documentation is wrong there and needs to be changed. PR's welcome.

> Which currently you have to change manually in the schema since there is no inference for it.

That's how it's currently designed as we have no way to infer the correct type here. See the corresponding discussion here for details: https://github.com/diesel-rs/diesel/pull/3087#discussion_r827332271 There is just no straight forward way to automatically infer whether a column supports time zones or not by only knowing the type name. That all written: I'm open for suggestions here.

> (Maybe) Rename TimestamptzSqlite since it's pretty ugly, but I guess there is a reason behind that choice

That's mostly to indicate that this type has different semantics than the `Timestamptz` type from postgres. I'm open to deprecating and renaming it to something better if someone comes up with an obvious better name.
The PR is on my todo, I built a custom type of datetime that uses the offset datetime under the hood so that is also a path that works well (and if you implement Deref then is it basically the same output).

Well for sqlite we can whatever we want in the column type so we could add support for timestamptz? What do you think?

Is there really a need to differentiate? I get the idea but if we can emulate the feature of postgres it could use the same name. The only annoying thing with the name currently is that you have to import in the generated schema file, you can't just type  `TimestamptzSqlite` (though `Timestamptz` is available if postgres is enabled).
> The only annoying thing with the name currently is that you have to import in the generated schema file, you can't just type TimestamptzSqlite (though Timestamptz is available if postgres is enabled).

That's something that should definitively fixed by just adding a reexport for `TimestamtzSqlite` in `diesel::sql_types` (if the sqlite feature is enabled)

Fix patch:
diff --git a/diesel/src/sql_types/mod.rs b/diesel/src/sql_types/mod.rs
index e8f7b666648a..d74b4d0fb968 100644
--- a/diesel/src/sql_types/mod.rs
+++ b/diesel/src/sql_types/mod.rs
@@ -332,14 +332,14 @@ pub struct Time;
 /// - [`std::time::SystemTime`][SystemTime] (PG only)
 /// - [`chrono::NaiveDateTime`][NaiveDateTime] with `feature = "chrono"`
 /// - [`time::PrimitiveDateTime`] with `feature = "time"`
-/// - [`time::OffsetDateTime`] with `feature = "time"`
+/// - [`time::OffsetDateTime`] with `feature = "time"` (MySQL only)
 ///
 /// ### [`FromSql`](crate::deserialize::FromSql) impls
 ///
 /// - [`std::time::SystemTime`][SystemTime] (PG only)
 /// - [`chrono::NaiveDateTime`][NaiveDateTime] with `feature = "chrono"`
 /// - [`time::PrimitiveDateTime`] with `feature = "time"`
-/// - [`time::OffsetDateTime`] with `feature = "time"`
+/// - [`time::OffsetDateTime`] with `feature = "time"` (MySQL only)
 ///
 /// [SystemTime]: std::time::SystemTime
 #[cfg_attr(
diff --git a/diesel/src/sqlite/types/date_and_time/chrono.rs b/diesel/src/sqlite/types/date_and_time/chrono.rs
index 74f859fba0e0..5b57bea681fe 100644
--- a/diesel/src/sqlite/types/date_and_time/chrono.rs
+++ b/diesel/src/sqlite/types/date_and_time/chrono.rs
@@ -1,4 +1,5 @@
 extern crate chrono;
+
 use self::chrono::{DateTime, Local, NaiveDate, NaiveDateTime, NaiveTime, TimeZone, Utc};
 
 use crate::backend::Backend;
@@ -7,13 +8,81 @@ use crate::serialize::{self, IsNull, Output, ToSql};
 use crate::sql_types::{Date, Time, Timestamp, TimestamptzSqlite};
 use crate::sqlite::Sqlite;
 
-const SQLITE_DATE_FORMAT: &str = "%F";
+/// Warning to future editors:
+/// Changes in the following formats need to be kept in sync
+/// with the formats of the ["time"](super::time) module.
+/// We do not need a distinction between whole second and
+/// subsecond since %.f will only print the dot if needed.
+/// We always print as many subsecond as his given to us,
+/// this means the subsecond part can be 3, 6 or 9 digits.
+const DATE_FORMAT: &str = "%F";
+
+const ENCODE_TIME_FORMAT: &str = "%T%.f";
+
+const TIME_FORMATS: [&str; 9] = [
+    // Most likely formats
+    "%T%.f", "%T", // All other valid formats in order of increasing specificity
+    "%R", "%RZ", "%R%:z", "%TZ", "%T%:z", "%T%.fZ", "%T%.f%:z",
+];
+
+const ENCODE_NAIVE_DATETIME_FORMAT: &str = "%F %T%.f";
+
+const ENCODE_DATETIME_FORMAT: &str = "%F %T%.f%:z";
+
+const NAIVE_DATETIME_FORMATS: [&str; 18] = [
+    // Most likely formats
+    "%F %T%.f",
+    "%F %T%.f%:z",
+    "%F %T",
+    "%F %T%:z",
+    // All other formats in order of increasing specificity
+    "%F %R",
+    "%F %RZ",
+    "%F %R%:z",
+    "%F %TZ",
+    "%F %T%.fZ",
+    "%FT%R",
+    "%FT%RZ",
+    "%FT%R%:z",
+    "%FT%T",
+    "%FT%TZ",
+    "%FT%T%:z",
+    "%FT%T%.f",
+    "%FT%T%.fZ",
+    "%FT%T%.f%:z",
+];
+
+const DATETIME_FORMATS: [&str; 12] = [
+    // Most likely formats
+    "%F %T%.f%:z",
+    "%F %T%:z",
+    // All other formats in order of increasing specificity
+    "%F %RZ",
+    "%F %R%:z",
+    "%F %TZ",
+    "%F %T%.fZ",
+    "%FT%RZ",
+    "%FT%R%:z",
+    "%FT%TZ",
+    "%FT%T%:z",
+    "%FT%T%.fZ",
+    "%FT%T%.f%:z",
+];
+
+fn parse_julian(julian_days: f64) -> Option<NaiveDateTime> {
+    const EPOCH_IN_JULIAN_DAYS: f64 = 2_440_587.5;
+    const SECONDS_IN_DAY: f64 = 86400.0;
+    let timestamp = (julian_days - EPOCH_IN_JULIAN_DAYS) * SECONDS_IN_DAY;
+    let seconds = timestamp as i64;
+    let nanos = (timestamp.fract() * 1E9) as u32;
+    NaiveDateTime::from_timestamp_opt(seconds, nanos)
+}
 
 #[cfg(all(feature = "sqlite", feature = "chrono"))]
 impl FromSql<Date, Sqlite> for NaiveDate {
     fn from_sql(value: <Sqlite as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
         value
-            .parse_string(|s| Self::parse_from_str(s, SQLITE_DATE_FORMAT))
+            .parse_string(|s| Self::parse_from_str(s, DATE_FORMAT))
             .map_err(Into::into)
     }
 }
@@ -21,7 +90,7 @@ impl FromSql<Date, Sqlite> for NaiveDate {
 #[cfg(all(feature = "sqlite", feature = "chrono"))]
 impl ToSql<Date, Sqlite> for NaiveDate {
     fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
-        out.set_value(self.format(SQLITE_DATE_FORMAT).to_string());
+        out.set_value(self.format(DATE_FORMAT).to_string());
         Ok(IsNull::No)
     }
 }
@@ -30,13 +99,7 @@ impl ToSql<Date, Sqlite> for NaiveDate {
 impl FromSql<Time, Sqlite> for NaiveTime {
     fn from_sql(value: <Sqlite as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
         value.parse_string(|text| {
-            let valid_time_formats = &[
-                // Most likely
-                "%T%.f", // All other valid formats in order of documentation
-                "%R", "%RZ", "%T%.fZ", "%R%:z", "%T%.f%:z",
-            ];
-
-            for format in valid_time_formats {
+            for format in TIME_FORMATS {
                 if let Ok(time) = Self::parse_from_str(text, format) {
                     return Ok(time);
                 }
@@ -50,7 +113,7 @@ impl FromSql<Time, Sqlite> for NaiveTime {
 #[cfg(all(feature = "sqlite", feature = "chrono"))]
 impl ToSql<Time, Sqlite> for NaiveTime {
     fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
-        out.set_value(self.format("%T%.f").to_string());
+        out.set_value(self.format(ENCODE_TIME_FORMAT).to_string());
         Ok(IsNull::No)
     }
 }
@@ -59,36 +122,14 @@ impl ToSql<Time, Sqlite> for NaiveTime {
 impl FromSql<Timestamp, Sqlite> for NaiveDateTime {
     fn from_sql(value: <Sqlite as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
         value.parse_string(|text| {
-            let sqlite_datetime_formats = &[
-                // Most likely format
-                "%F %T%.f",
-                // Other formats in order of appearance in docs
-                "%F %R",
-                "%F %RZ",
-                "%F %R%:z",
-                "%F %T%.fZ",
-                "%F %T%.f%:z",
-                "%FT%R",
-                "%FT%RZ",
-                "%FT%R%:z",
-                "%FT%T%.f",
-                "%FT%T%.fZ",
-                "%FT%T%.f%:z",
-            ];
-
-            for format in sqlite_datetime_formats {
+            for format in NAIVE_DATETIME_FORMATS {
                 if let Ok(dt) = Self::parse_from_str(text, format) {
                     return Ok(dt);
                 }
             }
 
             if let Ok(julian_days) = text.parse::<f64>() {
-                let epoch_in_julian_days = 2_440_587.5;
-                let seconds_in_day = 86400.0;
-                let timestamp = (julian_days - epoch_in_julian_days) * seconds_in_day;
-                let seconds = timestamp as i64;
-                let nanos = (timestamp.fract() * 1E9) as u32;
-                if let Some(timestamp) = Self::from_timestamp_opt(seconds, nanos) {
+                if let Some(timestamp) = parse_julian(julian_days) {
                     return Ok(timestamp);
                 }
             }
@@ -101,7 +142,7 @@ impl FromSql<Timestamp, Sqlite> for NaiveDateTime {
 #[cfg(all(feature = "sqlite", feature = "chrono"))]
 impl ToSql<Timestamp, Sqlite> for NaiveDateTime {
     fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
-        out.set_value(self.format("%F %T%.f").to_string());
+        out.set_value(self.format(ENCODE_NAIVE_DATETIME_FORMAT).to_string());
         Ok(IsNull::No)
     }
 }
@@ -110,34 +151,14 @@ impl ToSql<Timestamp, Sqlite> for NaiveDateTime {
 impl FromSql<TimestamptzSqlite, Sqlite> for NaiveDateTime {
     fn from_sql(value: <Sqlite as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
         value.parse_string(|text| {
-            let sqlite_datetime_formats = &[
-                "%F %RZ",
-                "%F %R%:z",
-                "%F %TZ",
-                "%F %T%:z",
-                "%F %T%.fZ",
-                "%F %T%.f%:z",
-                "%FT%RZ",
-                "%FT%R%:z",
-                "%FT%TZ",
-                "%FT%T%:z",
-                "%FT%T%.fZ",
-                "%FT%T%.f%:z",
-            ];
-
-            for format in sqlite_datetime_formats {
+            for format in NAIVE_DATETIME_FORMATS {
                 if let Ok(dt) = Self::parse_from_str(text, format) {
                     return Ok(dt);
                 }
             }
 
             if let Ok(julian_days) = text.parse::<f64>() {
-                let epoch_in_julian_days = 2_440_587.5;
-                let seconds_in_day = 86400.0;
-                let timestamp = (julian_days - epoch_in_julian_days) * seconds_in_day;
-                let seconds = timestamp as i64;
-                let nanos = (timestamp.fract() * 1E9) as u32;
-                if let Some(timestamp) = Self::from_timestamp_opt(seconds, nanos) {
+                if let Some(timestamp) = parse_julian(julian_days) {
                     return Ok(timestamp);
                 }
             }
@@ -150,7 +171,7 @@ impl FromSql<TimestamptzSqlite, Sqlite> for NaiveDateTime {
 #[cfg(all(feature = "sqlite", feature = "chrono"))]
 impl ToSql<TimestamptzSqlite, Sqlite> for NaiveDateTime {
     fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
-        out.set_value(self.format("%F %T%.f+00:00").to_string());
+        out.set_value(self.format(ENCODE_NAIVE_DATETIME_FORMAT).to_string());
         Ok(IsNull::No)
     }
 }
@@ -158,6 +179,20 @@ impl ToSql<TimestamptzSqlite, Sqlite> for NaiveDateTime {
 #[cfg(all(feature = "sqlite", feature = "chrono"))]
 impl FromSql<TimestamptzSqlite, Sqlite> for DateTime<Utc> {
     fn from_sql(value: <Sqlite as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
+        // First try to parse the timezone
+        if let Ok(dt) = value.parse_string(|text| {
+            for format in DATETIME_FORMATS {
+                if let Ok(dt) = DateTime::parse_from_str(text, format) {
+                    return Ok(dt.with_timezone(&Utc));
+                }
+            }
+
+            Err(())
+        }) {
+            return Ok(dt);
+        }
+
+        // Fallback on assuming Utc
         let naive_date_time =
             <NaiveDateTime as FromSql<TimestamptzSqlite, Sqlite>>::from_sql(value)?;
         Ok(DateTime::from_utc(naive_date_time, Utc))
@@ -167,6 +202,20 @@ impl FromSql<TimestamptzSqlite, Sqlite> for DateTime<Utc> {
 #[cfg(all(feature = "sqlite", feature = "chrono"))]
 impl FromSql<TimestamptzSqlite, Sqlite> for DateTime<Local> {
     fn from_sql(value: <Sqlite as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
+        // First try to parse the timezone
+        if let Ok(dt) = value.parse_string(|text| {
+            for format in DATETIME_FORMATS {
+                if let Ok(dt) = DateTime::parse_from_str(text, format) {
+                    return Ok(dt.with_timezone(&Local));
+                }
+            }
+
+            Err(())
+        }) {
+            return Ok(dt);
+        }
+
+        // Fallback on assuming Local
         let naive_date_time =
             <NaiveDateTime as FromSql<TimestamptzSqlite, Sqlite>>::from_sql(value)?;
         Ok(Local::from_utc_datetime(&Local, &naive_date_time))
@@ -176,7 +225,9 @@ impl FromSql<TimestamptzSqlite, Sqlite> for DateTime<Local> {
 #[cfg(all(feature = "sqlite", feature = "chrono"))]
 impl<TZ: TimeZone> ToSql<TimestamptzSqlite, Sqlite> for DateTime<TZ> {
     fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
-        out.set_value(self.naive_utc().format("%F %T%.f+00:00").to_string());
+        // Converting to UTC ensures consistency
+        let dt_utc = self.with_timezone(&Utc);
+        out.set_value(dt_utc.format(ENCODE_DATETIME_FORMAT).to_string());
         Ok(IsNull::No)
     }
 }
@@ -636,22 +687,6 @@ mod tests {
             "1970-01-01T00:00:00+00:00",
             "1970-01-01T00:00:00.000+00:00",
             "1970-01-01T00:00:00.000000+00:00",
-            "1970-01-01 00:00+01:00",
-            "1970-01-01 00:00:00+01:00",
-            "1970-01-01 00:00:00.000+01:00",
-            "1970-01-01 00:00:00.000000+01:00",
-            "1970-01-01T00:00+01:00",
-            "1970-01-01T00:00:00+01:00",
-            "1970-01-01T00:00:00.000+01:00",
-            "1970-01-01T00:00:00.000000+01:00",
-            "1970-01-01T00:00-01:00",
-            "1970-01-01T00:00:00-01:00",
-            "1970-01-01T00:00:00.000-01:00",
-            "1970-01-01T00:00:00.000000-01:00",
-            "1970-01-01T00:00-01:00",
-            "1970-01-01T00:00:00-01:00",
-            "1970-01-01T00:00:00.000-01:00",
-            "1970-01-01T00:00:00.000000-01:00",
             "2440587.5",
         ];
 
diff --git a/diesel/src/sqlite/types/date_and_time/mod.rs b/diesel/src/sqlite/types/date_and_time/mod.rs
index 98facfd4387c..3b4a0617873e 100644
--- a/diesel/src/sqlite/types/date_and_time/mod.rs
+++ b/diesel/src/sqlite/types/date_and_time/mod.rs
@@ -92,3 +92,312 @@ impl ToSql<sql_types::TimestamptzSqlite, Sqlite> for String {
         <str as ToSql<sql_types::TimestamptzSqlite, Sqlite>>::to_sql(self as &str, out)
     }
 }
+
+#[cfg(all(test, feature = "chrono", feature = "time"))]
+mod tests {
+    extern crate chrono;
+    extern crate time;
+
+    use chrono::{
+        DateTime, Datelike, FixedOffset, NaiveDate, NaiveDateTime, NaiveTime, Timelike, Utc,
+    };
+    use time::{
+        macros::{date, datetime, offset, time},
+        Date, OffsetDateTime, PrimitiveDateTime, Time,
+    };
+
+    use crate::insert_into;
+    use crate::prelude::*;
+    use crate::test_helpers::connection;
+
+    crate::table! {
+        table_timestamp_tz(id) {
+            id -> Integer,
+            timestamp_with_tz -> TimestamptzSqlite,
+        }
+    }
+    crate::table! {
+      table_timestamp(id) {
+          id -> Integer,
+          timestamp -> Timestamp
+      }
+    }
+    crate::table! {
+        table_date(id) {
+            id -> Integer,
+            date -> Date
+        }
+    }
+    crate::table! {
+        table_time(id) {
+            id -> Integer,
+            time -> Time
+        }
+    }
+
+    fn eq_date(left: Date, right: NaiveDate) -> bool {
+        left.year() == right.year()
+            && left.month() as u8 == right.month() as u8
+            && left.day() == right.day() as u8
+    }
+
+    fn eq_time(left: Time, right: NaiveTime) -> bool {
+        left.hour() == right.hour() as u8
+            && left.minute() == right.minute() as u8
+            && left.second() == right.second() as u8
+            && left.nanosecond() == right.nanosecond()
+    }
+
+    fn eq_datetime(left: PrimitiveDateTime, right: NaiveDateTime) -> bool {
+        eq_date(left.date(), right.date()) && eq_time(left.time(), right.time())
+    }
+
+    fn eq_datetime_utc(left: OffsetDateTime, right: DateTime<Utc>) -> bool {
+        left.unix_timestamp_nanos() == right.timestamp_nanos() as i128
+    }
+
+    fn eq_datetime_offset(left: OffsetDateTime, right: DateTime<FixedOffset>) -> bool {
+        left.unix_timestamp_nanos() == right.timestamp_nanos() as i128
+    }
+
+    fn create_tables(conn: &mut SqliteConnection) {
+        crate::sql_query(
+            "CREATE TABLE table_timestamp_tz(id INTEGER PRIMARY KEY, timestamp_with_tz TEXT);",
+        )
+        .execute(conn)
+        .unwrap();
+
+        crate::sql_query("CREATE TABLE table_timestamp(id INTEGER PRIMARY KEY, timestamp TEXT);")
+            .execute(conn)
+            .unwrap();
+
+        crate::sql_query("CREATE TABLE table_date(id INTEGER PRIMARY KEY, date TEXT);")
+            .execute(conn)
+            .unwrap();
+
+        crate::sql_query("CREATE TABLE table_time(id INTEGER PRIMARY KEY, time TEXT);")
+            .execute(conn)
+            .unwrap();
+    }
+
+    #[test]
+    fn time_to_chrono_date() {
+        let conn = &mut connection();
+        create_tables(conn);
+
+        let original = date!(2000 - 1 - 1);
+
+        insert_into(table_date::table)
+            .values(vec![(table_date::id.eq(1), table_date::date.eq(original))])
+            .execute(conn)
+            .unwrap();
+
+        let translated = table_date::table
+            .select(table_date::date)
+            .get_result::<NaiveDate>(conn)
+            .unwrap();
+
+        assert!(eq_date(original, translated))
+    }
+
+    #[test]
+    fn chrono_to_time_date() {
+        let conn = &mut connection();
+        create_tables(conn);
+
+        let original = NaiveDate::from_ymd_opt(2000, 1, 1).unwrap();
+
+        insert_into(table_date::table)
+            .values(vec![(table_date::id.eq(1), table_date::date.eq(original))])
+            .execute(conn)
+            .unwrap();
+
+        let translated = table_date::table
+            .select(table_date::date)
+            .get_result::<Date>(conn)
+            .unwrap();
+
+        assert!(eq_date(translated, original))
+    }
+
+    #[test]
+    fn time_to_chrono_time() {
+        let conn = &mut connection();
+        create_tables(conn);
+
+        let original = time!(1:1:1.001);
+
+        insert_into(table_time::table)
+            .values(vec![(table_time::id.eq(1), table_time::time.eq(original))])
+            .execute(conn)
+            .unwrap();
+
+        let translated = table_time::table
+            .select(table_time::time)
+            .get_result::<NaiveTime>(conn)
+            .unwrap();
+
+        assert!(eq_time(original, translated))
+    }
+
+    #[test]
+    fn chrono_to_time_time() {
+        let conn = &mut connection();
+        create_tables(conn);
+
+        let original = NaiveTime::from_hms_milli_opt(1, 1, 1, 1).unwrap();
+
+        insert_into(table_time::table)
+            .values(vec![(table_time::id.eq(1), table_time::time.eq(original))])
+            .execute(conn)
+            .unwrap();
+
+        let translated = table_time::table
+            .select(table_time::time)
+            .get_result::<Time>(conn)
+            .unwrap();
+
+        assert!(eq_time(translated, original))
+    }
+
+    #[test]
+    fn time_to_chrono_datetime() {
+        let conn = &mut connection();
+        create_tables(conn);
+
+        let original = datetime!(2000-1-1 1:1:1.001);
+
+        insert_into(table_timestamp::table)
+            .values(vec![(
+                table_timestamp::id.eq(1),
+                table_timestamp::timestamp.eq(original),
+            )])
+            .execute(conn)
+            .unwrap();
+
+        let translated = table_timestamp::table
+            .select(table_timestamp::timestamp)
+            .get_result::<NaiveDateTime>(conn)
+            .unwrap();
+
+        assert!(eq_datetime(original, translated))
+    }
+
+    #[test]
+    fn chrono_to_time_datetime() {
+        let conn = &mut connection();
+        create_tables(conn);
+
+        let original = NaiveDate::from_ymd_opt(2000, 1, 1)
+            .unwrap()
+            .and_hms_milli_opt(1, 1, 1, 1)
+            .unwrap();
+
+        insert_into(table_timestamp::table)
+            .values(vec![(
+                table_timestamp::id.eq(1),
+                table_timestamp::timestamp.eq(original),
+            )])
+            .execute(conn)
+            .unwrap();
+
+        let translated = table_timestamp::table
+            .select(table_timestamp::timestamp)
+            .get_result::<PrimitiveDateTime>(conn)
+            .unwrap();
+
+        assert!(eq_datetime(translated, original))
+    }
+
+    #[test]
+    fn chrono_to_time_datetime_utc() {
+        let conn = &mut connection();
+        create_tables(conn);
+
+        let original = Utc::now();
+
+        insert_into(table_timestamp_tz::table)
+            .values(vec![(
+                table_timestamp_tz::id.eq(1),
+                table_timestamp_tz::timestamp_with_tz.eq(original),
+            )])
+            .execute(conn)
+            .unwrap();
+
+        let translated = table_timestamp_tz::table
+            .select(table_timestamp_tz::timestamp_with_tz)
+            .get_result::<OffsetDateTime>(conn)
+            .unwrap();
+
+        assert!(eq_datetime_utc(translated, original))
+    }
+
+    #[test]
+    fn time_to_chrono_datetime_utc() {
+        let conn = &mut connection();
+        create_tables(conn);
+
+        let original = OffsetDateTime::now_utc();
+
+        insert_into(table_timestamp_tz::table)
+            .values(vec![(
+                table_timestamp_tz::id.eq(1),
+                table_timestamp_tz::timestamp_with_tz.eq(original),
+            )])
+            .execute(conn)
+            .unwrap();
+
+        let translated = table_timestamp_tz::table
+            .select(table_timestamp_tz::timestamp_with_tz)
+            .get_result::<DateTime<Utc>>(conn)
+            .unwrap();
+
+        assert!(eq_datetime_utc(original, translated))
+    }
+
+    #[test]
+    fn chrono_to_time_datetime_timezone() {
+        let conn = &mut connection();
+        create_tables(conn);
+
+        let original = Utc::now().with_timezone(&FixedOffset::east_opt(5 * 3600).unwrap());
+
+        insert_into(table_timestamp_tz::table)
+            .values(vec![(
+                table_timestamp_tz::id.eq(1),
+                table_timestamp_tz::timestamp_with_tz.eq(original),
+            )])
+            .execute(conn)
+            .unwrap();
+
+        let translated = table_timestamp_tz::table
+            .select(table_timestamp_tz::timestamp_with_tz)
+            .get_result::<OffsetDateTime>(conn)
+            .unwrap();
+
+        assert!(eq_datetime_offset(translated, original))
+    }
+
+    #[test]
+    fn time_to_chrono_datetime_offset() {
+        let conn = &mut connection();
+        create_tables(conn);
+
+        let original = OffsetDateTime::now_utc().to_offset(offset!(+5));
+
+        insert_into(table_timestamp_tz::table)
+            .values(vec![(
+                table_timestamp_tz::id.eq(1),
+                table_timestamp_tz::timestamp_with_tz.eq(original),
+            )])
+            .execute(conn)
+            .unwrap();
+
+        let translated = table_timestamp_tz::table
+            .select(table_timestamp_tz::timestamp_with_tz)
+            .get_result::<DateTime<Utc>>(conn)
+            .unwrap();
+
+        assert!(eq_datetime_utc(original, translated))
+    }
+}
diff --git a/diesel/src/sqlite/types/date_and_time/time.rs b/diesel/src/sqlite/types/date_and_time/time.rs
index be71b7f266c3..41c993f6bb92 100644
--- a/diesel/src/sqlite/types/date_and_time/time.rs
+++ b/diesel/src/sqlite/types/date_and_time/time.rs
@@ -14,23 +14,30 @@ use crate::serialize::{self, IsNull, Output, ToSql};
 use crate::sql_types::{Date, Time, Timestamp, TimestamptzSqlite};
 use crate::sqlite::Sqlite;
 
+/// Warning to future editors:
+/// Changes in the following formats need to be kept in sync
+/// with the formats of the ["chrono"](super::chrono) module.
+/// We need a distinction between whole second and subsecond
+/// since there is no format option to forgo the dot.
+/// We always print as many subsecond as his given to us,
+/// this means the subsecond part can be between 1 and 9 digits.
 const DATE_FORMAT: &[FormatItem<'_>] = format_description!("[year]-[month]-[day]");
+
 const ENCODE_TIME_FORMAT_WHOLE_SECOND: &[FormatItem<'_>] =
     format_description!("[hour]:[minute]:[second]");
 const ENCODE_TIME_FORMAT_SUBSECOND: &[FormatItem<'_>] =
-    format_description!("[hour]:[minute]:[second].[subsecond digits:6]");
+    format_description!("[hour]:[minute]:[second].[subsecond]");
 
 const TIME_FORMATS: [&[FormatItem<'_>]; 9] = [
-    // Most likely
+    // Most likely formats
     format_description!("[hour]:[minute]:[second].[subsecond]"),
+    format_description!("[hour]:[minute]:[second]"),
     // All other valid formats in order of increasing specificity
     format_description!("[hour]:[minute]"),
     format_description!("[hour]:[minute]Z"),
     format_description!("[hour]:[minute][offset_hour sign:mandatory]:[offset_minute]"),
-    format_description!("[hour]:[minute]:[second]"),
     format_description!("[hour]:[minute]:[second]Z"),
     format_description!("[hour]:[minute]:[second][offset_hour sign:mandatory]:[offset_minute]"),
-    // here is where the most likely format would go according to the pattern
     format_description!("[hour]:[minute]:[second].[subsecond]Z"),
     format_description!(
         "[hour]:[minute]:[second].[subsecond][offset_hour sign:mandatory]:[offset_minute]"
@@ -40,28 +47,26 @@ const TIME_FORMATS: [&[FormatItem<'_>]; 9] = [
 const ENCODE_PRIMITIVE_DATETIME_FORMAT_WHOLE_SECOND: &[FormatItem<'_>] =
     format_description!("[year]-[month]-[day] [hour]:[minute]:[second]");
 const ENCODE_PRIMITIVE_DATETIME_FORMAT_SUBSECOND: &[FormatItem<'_>] =
-    format_description!("[year]-[month]-[day] [hour]:[minute]:[second].[subsecond digits:6]");
+    format_description!("[year]-[month]-[day] [hour]:[minute]:[second].[subsecond]");
 
 const ENCODE_DATETIME_FORMAT_WHOLE_SECOND: &[FormatItem<'_>] = format_description!(
     "[year]-[month]-[day] [hour]:[minute]:[second][offset_hour sign:mandatory]:[offset_minute]"
 );
 const ENCODE_DATETIME_FORMAT_SUBSECOND: &[FormatItem<'_>] =
-    format_description!("[year]-[month]-[day] [hour]:[minute]:[second].[subsecond digits:3][offset_hour sign:mandatory]:[offset_minute]");
+    format_description!("[year]-[month]-[day] [hour]:[minute]:[second].[subsecond][offset_hour sign:mandatory]:[offset_minute]");
 
-const DATETIME_FORMATS: [&[FormatItem<'_>]; 18] = [
-    // Most likely format
+const PRIMITIVE_DATETIME_FORMATS: [&[FormatItem<'_>]; 18] = [
+    // Most likely formats
     format_description!("[year]-[month]-[day] [hour]:[minute]:[second].[subsecond]"),
-    // Other formats in order of increasing specificity
+    format_description!("[year]-[month]-[day] [hour]:[minute]:[second].[subsecond][offset_hour sign:mandatory]:[offset_minute]"),
+    format_description!("[year]-[month]-[day] [hour]:[minute]:[second]"),
+    format_description!("[year]-[month]-[day] [hour]:[minute]:[second][offset_hour sign:mandatory]:[offset_minute]"),
+    // All other formats in order of increasing specificity
     format_description!("[year]-[month]-[day] [hour]:[minute]"),
     format_description!("[year]-[month]-[day] [hour]:[minute]Z"),
     format_description!("[year]-[month]-[day] [hour]:[minute][offset_hour sign:mandatory]:[offset_minute]"),
-    format_description!("[year]-[month]-[day] [hour]:[minute]:[second]"),
     format_description!("[year]-[month]-[day] [hour]:[minute]:[second]Z"),
-    format_description!("[year]-[month]-[day] [hour]:[minute]:[second][offset_hour sign:mandatory]:[offset_minute]"),
-    // here is where the most likely format would go according to the pattern
     format_description!("[year]-[month]-[day] [hour]:[minute]:[second].[subsecond]Z"),
-    format_description!("[year]-[month]-[day] [hour]:[minute]:[second].[subsecond][offset_hour sign:mandatory]:[offset_minute]"),
-    // now the same thing, with T
     format_description!("[year]-[month]-[day]T[hour]:[minute]"),
     format_description!("[year]-[month]-[day]T[hour]:[minute]Z"),
     format_description!("[year]-[month]-[day]T[hour]:[minute][offset_hour sign:mandatory]:[offset_minute]"),
@@ -73,6 +78,23 @@ const DATETIME_FORMATS: [&[FormatItem<'_>]; 18] = [
     format_description!("[year]-[month]-[day]T[hour]:[minute]:[second].[subsecond][offset_hour sign:mandatory]:[offset_minute]"),
 ];
 
+const DATETIME_FORMATS: [&[FormatItem<'_>]; 12] = [
+    // Most likely formats
+    format_description!("[year]-[month]-[day] [hour]:[minute]:[second].[subsecond][offset_hour sign:mandatory]:[offset_minute]"),
+    format_description!("[year]-[month]-[day] [hour]:[minute]:[second][offset_hour sign:mandatory]:[offset_minute]"),
+    // All other formats in order of increasing specificity
+    format_description!("[year]-[month]-[day] [hour]:[minute]Z"),
+    format_description!("[year]-[month]-[day] [hour]:[minute][offset_hour sign:mandatory]:[offset_minute]"),
+    format_description!("[year]-[month]-[day] [hour]:[minute]:[second]Z"),
+    format_description!("[year]-[month]-[day] [hour]:[minute]:[second].[subsecond]Z"),
+    format_description!("[year]-[month]-[day]T[hour]:[minute]Z"),
+    format_description!("[year]-[month]-[day]T[hour]:[minute][offset_hour sign:mandatory]:[offset_minute]"),
+    format_description!("[year]-[month]-[day]T[hour]:[minute]:[second]Z"),
+    format_description!("[year]-[month]-[day]T[hour]:[minute]:[second][offset_hour sign:mandatory]:[offset_minute]"),
+    format_description!("[year]-[month]-[day]T[hour]:[minute]:[second].[subsecond]Z"),
+    format_description!("[year]-[month]-[day]T[hour]:[minute]:[second].[subsecond][offset_hour sign:mandatory]:[offset_minute]"),
+];
+
 fn naive_utc(dt: OffsetDateTime) -> PrimitiveDateTime {
     let dt = dt.to_offset(UtcOffset::UTC);
     PrimitiveDateTime::new(dt.date(), dt.time())
@@ -134,7 +156,7 @@ impl ToSql<Time, Sqlite> for NaiveTime {
 impl FromSql<Timestamp, Sqlite> for PrimitiveDateTime {
     fn from_sql(value: <Sqlite as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
         value.parse_string(|text| {
-            for format in DATETIME_FORMATS {
+            for format in PRIMITIVE_DATETIME_FORMATS {
                 if let Ok(dt) = Self::parse(text, format) {
                     return Ok(dt);
                 }
@@ -154,7 +176,7 @@ impl FromSql<Timestamp, Sqlite> for PrimitiveDateTime {
 #[cfg(all(feature = "sqlite", feature = "time"))]
 impl ToSql<Timestamp, Sqlite> for PrimitiveDateTime {
     fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
-        let format = if self.microsecond() == 0 {
+        let format = if self.nanosecond() == 0 {
             ENCODE_PRIMITIVE_DATETIME_FORMAT_WHOLE_SECOND
         } else {
             ENCODE_PRIMITIVE_DATETIME_FORMAT_SUBSECOND
@@ -168,7 +190,7 @@ impl ToSql<Timestamp, Sqlite> for PrimitiveDateTime {
 impl FromSql<TimestamptzSqlite, Sqlite> for PrimitiveDateTime {
     fn from_sql(value: <Sqlite as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
         value.parse_string(|text| {
-            for format in DATETIME_FORMATS {
+            for format in PRIMITIVE_DATETIME_FORMATS {
                 if let Ok(dt) = Self::parse(text, format) {
                     return Ok(dt);
                 }
@@ -188,7 +210,7 @@ impl FromSql<TimestamptzSqlite, Sqlite> for PrimitiveDateTime {
 #[cfg(all(feature = "sqlite", feature = "time"))]
 impl ToSql<TimestamptzSqlite, Sqlite> for PrimitiveDateTime {
     fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
-        let format = if self.microsecond() == 0 {
+        let format = if self.nanosecond() == 0 {
             ENCODE_PRIMITIVE_DATETIME_FORMAT_WHOLE_SECOND
         } else {
             ENCODE_PRIMITIVE_DATETIME_FORMAT_SUBSECOND
@@ -201,6 +223,20 @@ impl ToSql<TimestamptzSqlite, Sqlite> for PrimitiveDateTime {
 #[cfg(all(feature = "sqlite", feature = "time"))]
 impl FromSql<TimestamptzSqlite, Sqlite> for OffsetDateTime {
     fn from_sql(value: <Sqlite as Backend>::RawValue<'_>) -> deserialize::Result<Self> {
+        // First try to parse the timezone
+        if let Ok(dt) = value.parse_string(|text| {
+            for format in DATETIME_FORMATS {
+                if let Ok(dt) = OffsetDateTime::parse(text, format) {
+                    return Ok(dt);
+                }
+            }
+
+            Err(())
+        }) {
+            return Ok(dt);
+        }
+
+        // Fallback on assuming UTC
         let primitive_date_time =
             <PrimitiveDateTime as FromSql<TimestamptzSqlite, Sqlite>>::from_sql(value)?;
         Ok(primitive_date_time.assume_utc())
@@ -210,8 +246,9 @@ impl FromSql<TimestamptzSqlite, Sqlite> for OffsetDateTime {
 #[cfg(all(feature = "sqlite", feature = "time"))]
 impl ToSql<TimestamptzSqlite, Sqlite> for OffsetDateTime {
     fn to_sql<'b>(&'b self, out: &mut Output<'b, '_, Sqlite>) -> serialize::Result {
+        // Converting to UTC ensures consistency
         let dt_utc = self.to_offset(UtcOffset::UTC);
-        let format = if self.millisecond() == 0 {
+        let format = if self.nanosecond() == 0 {
             ENCODE_DATETIME_FORMAT_WHOLE_SECOND
         } else {
             ENCODE_DATETIME_FORMAT_SUBSECOND
@@ -322,15 +359,15 @@ mod tests {
         let connection = &mut connection();
 
         let midnight = NaiveTime::from_hms(0, 0, 0).unwrap();
-        let query = select(time("00:00:00.000000").eq(midnight));
+        let query = select(time("00:00:00").eq(midnight));
         assert_eq!(query.get_result::<bool>(connection).unwrap(), true);
 
         let noon = NaiveTime::from_hms(12, 0, 0).unwrap();
-        let query = select(time("12:00:00.000000").eq(noon));
+        let query = select(time("12:00:00").eq(noon));
         assert_eq!(query.get_result::<bool>(connection).unwrap(), true);
 
         let roughly_half_past_eleven = NaiveTime::from_hms_micro(23, 37, 4, 2200).unwrap();
-        let query = select(sql::<Time>("'23:37:04.002200'").eq(roughly_half_past_eleven));
+        let query = select(sql::<Time>("'23:37:04.0022'").eq(roughly_half_past_eleven));
         assert_eq!(query.get_result::<bool>(connection).unwrap(), true);
     }
 
@@ -467,19 +504,19 @@ mod tests {
     fn datetimes_encode_correctly() {
         let connection = &mut connection();
         let january_first_2000 = datetime!(2000-1-1 0:0:0);
-        let query = select(datetime("2000-01-01 00:00:00.000000").eq(january_first_2000));
+        let query = select(datetime("2000-01-01 00:00:00").eq(january_first_2000));
         assert!(query.get_result::<bool>(connection).unwrap());
 
         let distant_past = datetime!(0-4-11 20:00:20);
-        let query = select(datetime("0000-04-11 20:00:20.000000").eq(distant_past));
+        let query = select(datetime("0000-04-11 20:00:20").eq(distant_past));
         assert!(query.get_result::<bool>(connection).unwrap());
 
         let january_first_2018 = datetime!(2018 - 1 - 1 12:00:00.0005);
-        let query = select(sql::<Timestamp>("'2018-01-01 12:00:00.000500'").eq(january_first_2018));
+        let query = select(sql::<Timestamp>("'2018-01-01 12:00:00.0005'").eq(january_first_2018));
         assert!(query.get_result::<bool>(connection).unwrap());
 
         let distant_future = datetime!(9999-1-8 0:0:0);
-        let query = select(datetime("9999-01-08 00:00:00.000000").eq(distant_future));
+        let query = select(datetime("9999-01-08 00:00:00").eq(distant_future));
         assert!(query.get_result::<bool>(connection).unwrap());
     }
 
@@ -632,22 +669,6 @@ mod tests {
             "1970-01-01T00:00:00+00:00",
             "1970-01-01T00:00:00.000+00:00",
             "1970-01-01T00:00:00.000000+00:00",
-            "1970-01-01 00:00+01:00",
-            "1970-01-01 00:00:00+01:00",
-            "1970-01-01 00:00:00.000+01:00",
-            "1970-01-01 00:00:00.000000+01:00",
-            "1970-01-01T00:00+01:00",
-            "1970-01-01T00:00:00+01:00",
-            "1970-01-01T00:00:00.000+01:00",
-            "1970-01-01T00:00:00.000000+01:00",
-            "1970-01-01T00:00-01:00",
-            "1970-01-01T00:00:00-01:00",
-            "1970-01-01T00:00:00.000-01:00",
-            "1970-01-01T00:00:00.000000-01:00",
-            "1970-01-01T00:00-01:00",
-            "1970-01-01T00:00:00-01:00",
-            "1970-01-01T00:00:00.000-01:00",
-            "1970-01-01T00:00:00.000000-01:00",
             "2440587.5",
         ];
 


Test patch: