How to define rust type of TIMESTAMP?

[billy1624]

Discussed in #341

^{Originally posted by lz1998 December 1, 2021}
I have tried 4 types, never of them works. NaiveDateTime, i64, DateTime<Utc> and DateTime<FixedOffset>

I use MySQL 8. I create table by myself without automigrate, and I define rust struct by myself.

    `created_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

#[derive(Clone, Debug, PartialEq, DeriveEntityModel, Serialize, Deserialize)]
#[sea_orm(table_name = "product")]
pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub id: i64,
    pub name: String,
    pub operator: String,
    pub status: i32,
    pub created_at: DateTime<FixedOffset>,
    pub updated_at: DateTime<FixedOffset>,
}

#[derive(Copy, Clone, Debug, EnumIter)]
pub enum Relation {}

impl RelationTrait for Relation {
    fn def(&self) -> RelationDef {
        panic!("No RelationDef")
    }
}

impl ActiveModelBehavior for ActiveModel {}

NaiveDateTime and i64

Err(Query("error occurred while decoding column \"created_at\": mismatched types; Rust type `core::option::Option<chrono::naive::datetime::NaiveDateTime>` (as SQL type `DATETIME`) is not compatible with SQL type `TIMESTAMP`"))

DateTime<Utc> and DateTime<FixedOffset>

error: proc-macro derive panicked
 --> src/database/product.rs:8:35
  |
8 | #[derive(Clone, Debug, PartialEq, DeriveEntityModel, Serialize, Deserialize)]
  |                                   ^^^^^^^^^^^^^^^^^
  |
  = help: message: `"DateTime<Utc>"` is not a valid identifier

```</div>

[tyt2y3]

We can use sea_orm::prelude::DateTime, which is actually NaiveDateTime.
Whether it can be converted to DateTime<Local> or DateTime<Utc> is dependent on system configuration.

[billy1624]

We can use sea_orm::prelude::DateTime, which is actually NaiveDateTime.

Yes, it should. I discover that for MySQL, it's blocked by SQLx. See launchbadge/sqlx#1581

[tyt2y3]

I think the reason for SQLx not supporting NaiveDateTime is because of the slightly strange behavior of MySQL's timestamp.

https://medium.com/@kenny_7143/time-zone-in-mysql-e7b73c70fd4e

In short, “ MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)”

They usually are DateTime<Utc> but can be DateTime<Local> as well depending on MySQL's configuration.

[tyt2y3]

I think we should support DateTime<Utc> natively in SeaORM soon

[tyt2y3]

0.4.2