Postgres timestamp column type

[hf29h8sh321]

Description

Steps to Reproduce

1. Create table with column with type timestamp
2. Use cli to generate struct
3. Insert a value

Behavior

Query Error: error occurred while decoding column "created_at": mismatched types; Rust type `core::option::Option<chrono::datetime::DateTime<chrono::offset::utc::Utc>>` (as SQL type `TIMESTAMPTZ`) is not compatible with SQL type `TIMESTAMP`

Reproduces How Often

Always

Versions

Postgres 14.1

├── sea-orm v0.6.0
│   ├── sea-orm-macros v0.6.0 (proc-macro)
│   ├── sea-query v0.21.0
│   │   ├── sea-query-derive v0.2.0 (proc-macro)
│   ├── sea-strum v0.23.0
│   │   └── sea-strum_macros v0.23.0 (proc-macro)

[billy1624]

Hey @hf29h8sh321, thanks for the report! I guess you are on PostgreSQL database?

[billy1624]

I could not reproduce the error.

1. PostgreSQL 13 table schema

create table satellite
(
    id              bigserial
        primary key,
    satellite_name  varchar                  not null,
    launch_date     timestamp                not null,
    deployment_date timestamp with time zone not null
);

2. SeaORM entity

use sea_orm::entity::prelude::*;

#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
#[sea_orm(table_name = "satellite")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i32,
    pub satellite_name: String,
    #[sea_orm(default_value = "2022-01-26 16:24:00")]
    pub launch_date: DateTimeUtc,
    #[sea_orm(default_value = "2022-01-26 16:24:00")]
    pub deployment_date: DateTimeLocal,
}

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

impl ActiveModelBehavior for ActiveModel {}

3. Insert operation

pub async fn create_satellites_log(db: &DatabaseConnection) -> Result<(), DbErr> {
    let archive = satellite::Model {
        id: 3,
        satellite_name: "Sea-00001-2022".to_owned(),
        launch_date: "2022-01-07T12:11:23Z".parse().unwrap(),
        deployment_date: "2022-01-07T12:11:23Z".parse().unwrap(),
    };

    let res = Satellite::insert(archive.clone().into_active_model())
        .exec(db)
        .await?;

    assert_eq!(archive.id, res.last_insert_id);
    assert_eq!(Satellite::find().one(db).await?, Some(archive.clone()));

    Ok(())
}

[tyt2y3]

It seems like that column type is not compatible with struct field type
Please reopen if this is still a problem

[billy1624]

I've been looking at #586 and I've also encountered this error before, also using Postgres. If I remember correctly, the issue was that I declared the column type as a DateTime using ColumnDef::new(...).date_time(), which in turn threw that error during serialization of the entity. Changing that code to ColumnDef::new(...).timestamp_with_time_zone() fixed that error upon insertion. Could this possibly be related? I'm wondering if the underlying column type that gets created causes errors, either that or a weird bug when using the Option type with a DateTime

Originally posted by Galestrike#8814 on Discord

[billy1624]

ColumnDef::new(...).date_time() will generate column type timestamp without time zone, aka timestamp in SeaQuery

• https://github.com/SeaQL/sea-query/blob/5e72470e022719a3ce5df4f67fc9a8313c67355a/src/backend/postgres/table.rs#L64

Then, SeaSchema discover and write the column type as timestamp

• https://github.com/SeaQL/sea-schema/blob/d4408a0cf4871ba66e6edc27f2528cc38730ab61/src/postgres/writer/column.rs#L108

Finally, SeaORM codegen write Rust type of timestamp column as DateTimeUtc

• sea-orm/sea-orm-codegen/src/entity/column.rs

  Line 46 in 0a26dee

  ColumnType::Timestamp(_) => "DateTimeUtc".to_owned(),

[hf29h8sh321]

I believe the issue is related to a column without not null. Then, the type becomes Option<DateTimeUtc> which is Option<DateTime<Utc>>, which is probably not recognized as a timestamp without time zone.

[pymongo]

I could not reproduce the error.

1. PostgreSQL 13 table schema

create table satellite
(
    id              bigserial
        primary key,
    satellite_name  varchar                  not null,
    launch_date     timestamp                not null,
    deployment_date timestamp with time zone not null
);

2. SeaORM entity

use sea_orm::entity::prelude::*;

#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
#[sea_orm(table_name = "satellite")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i32,
    pub satellite_name: String,
    #[sea_orm(default_value = "2022-01-26 16:24:00")]
    pub launch_date: DateTimeUtc,
    #[sea_orm(default_value = "2022-01-26 16:24:00")]
    pub deployment_date: DateTimeLocal,
}

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

impl ActiveModelBehavior for ActiveModel {}

3. Insert operation

pub async fn create_satellites_log(db: &DatabaseConnection) -> Result<(), DbErr> {
    let archive = satellite::Model {
        id: 3,
        satellite_name: "Sea-00001-2022".to_owned(),
        launch_date: "2022-01-07T12:11:23Z".parse().unwrap(),
        deployment_date: "2022-01-07T12:11:23Z".parse().unwrap(),
    };

    let res = Satellite::insert(archive.clone().into_active_model())
        .exec(db)
        .await?;

    assert_eq!(archive.id, res.last_insert_id);
    assert_eq!(Satellite::find().one(db).await?, Some(archive.clone()));

    Ok(())
}

use timestamp without time zone type with sea-orm-cli would reproduce these problem

here is my reproduce step: SeaQL/sea-schema#50

[ricardoalcantara]

Hello!

I had this same issue:

• I am using Postgres;
• I have fields mapped with ColumnDef::new(..).timespamp();
• The migration generate the correct field in the Database with type timespamp.

But then generated entities places DateTimeUtc and after I face this:

value: Query("error occurred while decoding column \"last_login\": mismatched types; Rust type `core::option::Option<chrono::datetime::DateTime<chrono::offset::utc::Utc>>` (as SQL type `TIMESTAMPTZ`) is not compatible with SQL type `TIMESTAMP`")

And I no longer can query those tables with timestamp.

I also had a LEFT JOIN that was returning Vec[] empty without crashing just because of that timestamp

A workaround I am testing is to change the DateTimeUtc to DateTime which is chrono::NaiveDateTime and I am filling that field with chrono::Utc::now().naive_utc() I still don't know it it causes any unwanted behaviour.

but now I can query those data, even the LEFT JOIN now works.

How is the status of this issue?

[billy1624]

Hey everyone,

The root cause of it comes from SQLx:

• MySQL:
  • Datetime column maps to DateTime in Rust
  • Timestamp column maps to DateTimeUTC in Rust
• PostgreSQL:
  • Both datetime and timestamp columns map to DateTime in Rust

But still we could "fix" it. For PostgreSQL: datetime (timestamp without time zone) and timestamp (timestamp) datatypes are essentially the same.

The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior.
https://www.postgresql.org/docs/current/datatype-datetime.html

I'm going to update PostgreSQL writer on SeaSchema and mapping datetime and timestamp columns to ColumnType::DateTime in SeaQuery create table statement.

[billy1624]

Hey @ricardoalcantara, please check #772