Repository: diesel-rs/diesel
File: diesel/src/sqlite/types/date_and_time/chrono.rs (Rust)

Code before:

        let january_first_2018 = NaiveDate::from_ymd(2018, 1, 1)
            .and_hms(12, 00, 00)
            .with_nanosecond(500_000)
            .unwrap();
        let query = select(sql::<Timestamp>("'2018-01-01 12:00:00.000500'").eq(january_first_2018));
        assert!(query.get_result::<bool>(connection).unwrap());

        let distant_future = NaiveDate::from_ymd(9999, 1, 8).and_hms(0, 0, 0);
        let query = select(datetime("9999-01-08 00:00:00.000000").eq(distant_future));
        assert!(query.get_result::<bool>(connection).unwrap());
    }

    #[test]
    fn insert_timestamptz_into_table_as_text() {
        crate::table! {
            #[allow(unused_parens)]
            test_insert_timestamptz_into_table_as_text(id) {
                id -> Integer,
                timestamp_with_tz -> Text,
            }
        }
        let conn = &mut connection();
        crate::sql_query(
            "CREATE TABLE test_insert_timestamptz_into_table_as_text(id INTEGER PRIMARY KEY, timestamp_with_tz TEXT);",
        )
        .execute(conn)
        .unwrap();

        let time: DateTime<Utc> = Utc.ymd(1970, 1, 1).and_hms_milli(0, 0, 0, 0);

        crate::insert_into(test_insert_timestamptz_into_table_as_text::table)
            .values(vec![
                (test_insert_timestamptz_into_table_as_text::timestamp_with_tz
                    .eq("'1970-01-01 00:00:00+00:00'")),
            ])
            .execute(conn)
            .unwrap();

        let result = test_insert_timestamptz_into_table_as_text::table
            .select(test_insert_timestamptz_into_table_as_text::timestamp_with_tz)
            .get_result::<DateTime<Utc>>(conn)
            .unwrap();
        assert_eq!(result, time);
    }

    #[test]
    fn can_query_timestamptz_column_with_between() {
        crate::table! {
            #[allow(unused_parens)]
            test_query_timestamptz_column_with_between(id) {

Review comment (bug):
The idea behind this and the below test was to use `diesel` in its "canonical" form -- that is to have typed native Rust objects like `test_insert_timestamptz_into_table_as_text` that I could then manipulate with all the safety that Rust gurantees.

But these two tests are now giving me grief, very likely because I am still learning about the internals of `diesel` and how exactly it's intended to be used. I know that we're mixing metaphors a bit in trying to store datetime (w/ TZ) as text but still I thought this would've worked 😅 

Anyway pushing up to see if someone else can see what's obviously wrong with either the code or how I'm trying to use `diesel`; will keep poking away at it on my own too

```
@ diesel [stevenchu]$ cargo test --no-default-features --features 'extras sqlite'
   Compiling libsqlite3-sys v0.23.2
   Compiling diesel v2.0.0 (/Users/stevenchu/personal_projects/rust/diesel/diesel)
error[E0277]: the trait bound `type_impls::date_and_time::chrono::chrono::DateTime<Utc>: FromSql<sql_types::Text, Sqlite>` is not satisfied
   --> diesel/src/sqlite/types/date_and_time/chrono.rs:468:14
    |
468 |             .get_result::<DateTime<Utc>>(conn)
    |              ^^^^^^^^^^ the trait `FromSql<sql_types::Text, Sqlite>` is not implemented for `type_impls::date_and_time::chrono::chrono::DateTime<Utc>`
    |
    = help: the following implementations were found:
              <type_impls::date_and_time::chrono::chrono::DateTime<Local> as FromSql<Timestamptz, Sqlite>>
              <type_impls::date_and_time::chrono::chrono::DateTime<Utc> as FromSql<Timestamptz, Sqlite>>
note: required because of the requirements on the impl of `deserialize::Queryable<sql_types::Text, Sqlite>` for `type_impls::date_and_time::chrono::chrono::DateTime<Utc>`
   --> diesel/src/type_impls/date_and_time.rs:40:14
    |
40  |     #[derive(FromSqlRow)]
    |              ^^^^^^^^^^
...
51  |     struct DateTimeProxy<Tz: TimeZone>(DateTime<Tz>);
    |                                        ^^^^^^^^^^^^
    = note: required because of the requirements on the impl of `FromSqlRow<sql_types::Text, Sqlite>` for `type_impls::date_and_time::chrono::chrono::DateTime<Utc>`
note: required because of the requirements on the impl of `CompatibleType<type_impls::date_and_time::chrono::chrono::DateTime<Utc>, Sqlite>` for `sql_types::Text`
   --> diesel/src/query_dsl/load_dsl.rs:205:21
    |
205 |     impl<ST, U, DB> CompatibleType<U, DB> for ST
    |                     ^^^^^^^^^^^^^^^^^^^^^     ^^
note: required because of the requirements on the impl of `LoadQuery<'_, _, type_impls::date_and_time::chrono::chrono::DateTime<Utc>>` for `select_statement::SelectStatement<from_clause::FromClause<test_insert_timestamptz_into_table_as_text::table>, select_clause::SelectClause<test_insert_timestamptz_into_table_as_text::columns::timestamp_with_tz>>`
   --> diesel/src/query_dsl/load_dsl.rs:58:30
    |
58  | impl<'query, Conn, T, U, DB> LoadQuery<'query, Conn, U> for T
    |                              ^^^^^^^^^^^^^^^^^^^^^^^^^^     ^
    = note: this error originates in the derive macro `FromSqlRow` (in Nightly builds, run with -Z macro-backtrace for more info)

error[E0599]: the method `gt` exists for struct `select_statement::SelectStatement<from_clause::FromClause<test_query_timestamptz_column_with_between::table>, select_clause::SelectClause<test_query_timestamptz_column_with_between::columns::timestamp_with_tz>>`, but its trait bounds were not satisfied
   --> diesel/src/sqlite/types/date_and_time/chrono.rs:507:14
    |
507 |               .gt(Utc.ymd(1970, 1, 1).and_hms_milli(0, 0, 0, 0))
    |                ^^ method cannot be called on `select_statement::SelectStatement<from_clause::FromClause<test_query_timestamptz_column_with_between::table>, select_clause::SelectClause<test_query_timestamptz_column_with_between::columns::timestamp_with_tz>>` due to unsatisfied trait bounds
    |
   ::: diesel/src/query_builder/select_statement/mod.rs:45:1
    |
45  | / pub struct SelectStatement<
46  | |     From,
47  | |     Select = DefaultSelectClause<From>,
48  | |     Distinct = NoDistinctClause,
...   |
64  | |     pub(crate) locking: Locking,
65  | | }
    | | -
    | | |
    | | method `gt` not found for this
    | | doesn't satisfy `_: Iterator`
    | |_doesn't satisfy `_: expression::Expression`
    |   doesn't satisfy `_: global_expression_methods::ExpressionMethods`
    |
    = note: the following trait bounds were not satisfied:
            `select_statement::SelectStatement<from_clause::FromClause<test_query_timestamptz_column_with_between::table>, select_clause::SelectClause<test_query_timestamptz_column_with_between::columns::timestamp_with_tz>>: expression::Expression`
            which is required by `select_statement::SelectStatement<from_clause::FromClause<test_query_timestamptz_column_with_between::table>, select_clause::SelectClause<test_query_timestamptz_column_with_between::columns::timestamp_with_tz>>: global_expression_methods::ExpressionMethods`
            `&select_statement::SelectStatement<from_clause::FromClause<test_query_timestamptz_column_with_between::table>, select_clause::SelectClause<test_query_timestamptz_column_with_between::columns::timestamp_with_tz>>: expression::Expression`
            which is required by `&select_statement::SelectStatement<from_clause::FromClause<test_query_timestamptz_column_with_between::table>, select_clause::SelectClause<test_query_timestamptz_column_with_between::columns::timestamp_with_tz>>: global_expression_methods::ExpressionMethods`
            `select_statement::SelectStatement<from_clause::FromClause<test_query_timestamptz_column_with_between::table>, select_clause::SelectClause<test_query_timestamptz_column_with_between::columns::timestamp_with_tz>>: expression::Expression`
            which is required by `&select_statement::SelectStatement<from_clause::FromClause<test_query_timestamptz_column_with_between::table>, select_clause::SelectClause<test_query_timestamptz_column_with_between::columns::timestamp_with_tz>>: global_expression_methods::ExpressionMethods`
            `&mut select_statement::SelectStatement<from_clause::FromClause<test_query_timestamptz_column_with_between::table>, select_clause::SelectClause<test_query_timestamptz_column_with_between::columns::timestamp_with_tz>>: expression::Expression`
            which is required by `&mut select_statement::SelectStatement<from_clause::FromClause<test_query_timestamptz_column_with_between::table>, select_clause::SelectClause<test_query_timestamptz_column_with_between::columns::timestamp_with_tz>>: global_expression_methods::ExpressionMethods`
            `select_statement::SelectStatement<from_clause::FromClause<test_query_timestamptz_column_with_between::table>, select_clause::SelectClause<test_query_timestamptz_column_with_between::columns::timestamp_with_tz>>: Iterator`
            which is required by `&mut select_statement::SelectStatement<from_clause::FromClause<test_query_timestamptz_column_with_between::table>, select_clause::SelectClause<test_query_timestamptz_column_with_between::columns::timestamp_with_tz>>: Iterator`

error: aborting due to 2 previous errors

Some errors have detailed explanations: E0277, E0599.
For more information about an error, try `rustc --explain E0277`.
error: could not compile `diesel`

To learn more, run the command again with --verbose.
```

Code after:

    #[test]
    fn datetimes_encode_correctly() {
        let connection = &mut connection();
        let january_first_2000 = NaiveDate::from_ymd(2000, 1, 1).and_hms(0, 0, 0);
        let query = select(datetime("2000-01-01 00:00:00.000000").eq(january_first_2000));
        assert!(query.get_result::<bool>(connection).unwrap());

        let distant_past = NaiveDate::from_ymd(0, 4, 11).and_hms(20, 00, 20);
        let query = select(datetime("0000-04-11 20:00:20.000000").eq(distant_past));
        assert!(query.get_result::<bool>(connection).unwrap());

        let january_first_2018 = NaiveDate::from_ymd(2018, 1, 1)
            .and_hms(12, 00, 00)
            .with_nanosecond(500_000)
            .unwrap();
        let query = select(sql::<Timestamp>("'2018-01-01 12:00:00.000500'").eq(january_first_2018));
        assert!(query.get_result::<bool>(connection).unwrap());

        let distant_future = NaiveDate::from_ymd(9999, 1, 8).and_hms(0, 0, 0);
        let query = select(datetime("9999-01-08 00:00:00.000000").eq(distant_future));
        assert!(query.get_result::<bool>(connection).unwrap());
    }

    #[test]
    fn insert_timestamptz_into_table_as_text() {
        crate::table! {
            #[allow(unused_parens)]
            test_insert_timestamptz_into_table_as_text(id) {
                id -> Integer,
                timestamp_with_tz -> TimestamptzSqlite,
            }
        }
        let conn = &mut connection();
        crate::sql_query(
            "CREATE TABLE test_insert_timestamptz_into_table_as_text(id INTEGER PRIMARY KEY, timestamp_with_tz TEXT);",
        )
        .execute(conn)
        .unwrap();

        let time: DateTime<Utc> = Utc.ymd(1970, 1, 1).and_hms_milli(0, 0, 0, 0);

        crate::insert_into(test_insert_timestamptz_into_table_as_text::table)
            .values(vec![(
                test_insert_timestamptz_into_table_as_text::id.eq(1),
                test_insert_timestamptz_into_table_as_text::timestamp_with_tz.eq(sql::<
                    TimestamptzSqlite,
                >(
                    "'1970-01-01 00:00:00.000000+00:00'",
                )),
            )])