Use DateTime<Utc> for Postgres TIMESTAMPTZ

[tqwewe]

I have a model with a DB column called created_at which is of type TIMESTAMPTZ.
My model uses the type DateTime<Utc> for the timestamp, but I get an error saying:

   | #[derive(Clone, Debug, PartialEq, DeriveModel, DeriveActiveModel)]
   |                                                ^^^^^^^^^^^^^^^^^ the trait `From<chrono::DateTime<Utc>>` is not implemented for `sea_orm::Value`
 pub struct Model {
     pub id: Uuid,
     pub created_at: DateTime<Utc>,
 }

I have the with-chrono flag enabled.

How should I be querying a TIMESTAMPTZ column? I tried making the Rust type just a String but get an error when querying saying:

Error: Query Error: error occurred while decoding column "created_at": mismatched types; Rust type alloc::string::String (as SQL type TEXT) is not compatible with SQL type TIMESTAMPTZ

[tyt2y3]

TIMESTAMPTZ is not yet supported. But AFAIK DateTime<Utc> is equivalent to NaiveDateTime which is aliased to DateTime in SeaORM

[tqwewe]

Okay I'd love for SeaORM to support TIMESTAMPTZ, I'll look into the code to see if I can figure it out

[tyt2y3]

SeaQuery had basic support for DateTimeWithTimeZone, but that did not extend to SeaORM yet

[tyt2y3]

I added support for the type chrono::DateTime<chrono::FixedOffset> which is aliased to DateTimeWithTimeZone in SeaORM.
#71

[tqwewe]

Legend! I'll pull down master branch and try it out

[tqwewe]

@tyt2y3 I tried with the latest code from master with:

#[derive(Clone, Debug, PartialEq, DeriveModel, DeriveActiveModel)]
pub struct Model {
    pub id: Uuid,
    pub created_at: DateTimeWithTimeZone,

And with:

#[derive(Clone, Debug, PartialEq, DeriveModel, DeriveActiveModel)]
pub struct Model {
    pub id: Uuid,
    pub created_at: DateTime<Utc>,

But both give me an error for DeriveActiveModel:

the trait `From<chrono::DateTime<FixedOffset>>` is not implemented for `sea_orm::Value`

[tyt2y3]

I think you should cargo update to update sea-query to 0.12.9 too.

[tqwewe]

That seems to have worked!

[brecht-derooms]

shouldn't this be chrono::DateTime rather?
At least sqlx expects chrono::DateTime so it makes the model incompatible if you use it both with sqlx as with seaorm.
Timestamp with timezone in postgres is notoriously confusing, it's however the recommended type to store utc timestmaps as well as far as I recall.

[marlon-sousa]

Hello,

I have kind of the same problem but need orientation to solve that.

First things first

1. My postgres table has a column called created_at with type "timestamp with time zone"
2. I use sea-orm-cli to generate entity (set DATABASE_URL environment variable then sea-orm-cli generate entity -o some_dir)
3. I notice that the columns generated have the NaiveDateTime type (I see pub created_at: DateTime, declared on the generated model).

Environment

• sea-cli-orm v 2.0.0
• In my project, I have sea-orm = { version = "^0", features = ["sqlx-postgres", "runtime-tokio-native-tls", "macros"], default-features = false }

Error

Queries run show the following error:

mismatched types; Rust type core::option::Option<chrono::naive::datetime::NaiveDateTime> (as SQL type TIMESTAMP) is not compatible with SQL type TIMESTAMPTZ"

If I try to use DateTime (by changing the generated entities by hand) I have a trait error.

Without waiting for the cli to correctly recognize the type (if it isn't still doing so), how can I fix that?

Thanks,
Marlon

[tyt2y3]

It should be DateTimeWithTimeZone https://github.com/SeaQL/sea-orm/blob/master/src/entity/prelude.rs

…

________________________________ From: Marlon Brandão de Sousa ***@***.***> Sent: Friday, September 17, 2021 1:53:50 AM To: SeaQL/sea-orm ***@***.***> Cc: Chris Tsang ***@***.***>; Mention ***@***.***> Subject: Re: [SeaQL/sea-orm] Use DateTime<Utc> for Postgres TIMESTAMPTZ (Discussion #68) Hello, I have kind of the same problem but need orientation to solve that. First things first 1. My postgres table has a column called created_at with type "timestamp with time zone" 2. I use sea-orm-cli to generate entity (set DATABASE_URL environment variable then sea-orm-cli generate entity -o some_dir) 3. I notice that the columns generated have the NaiveDateTime type (I see pub created_at: DateTime, declared on the generated model). Environment * sea-cli-orm v 2.0.0 * In my project, I have sea-orm = { version = "^0", features = ["sqlx-postgres", "runtime-tokio-native-tls", "macros"], default-features = false } Error Queries run show the following error: mismatched types; Rust type core::option::Option<chrono::naive::datetime::NaiveDateTime> (as SQL type TIMESTAMP) is not compatible with SQL type TIMESTAMPTZ" If I try to use DateTime (by changing the generated entities by hand) I have a trait error. Without waiting for the cli to correctly recognize the type (if it isn't still doing so), how can I fix that? Thanks, Marlon — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<https://apac01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FSeaQL%2Fsea-orm%2Fdiscussions%2F68%23discussioncomment-1343661&data=04%7C01%7C%7Cd91ceba564044cd7e5ac08d9793af16a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637674116334283229%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=HN22eUnvkR%2F8WXTzYpND5fHv%2FPZ%2FpWVpFdqcLJjqHh4%3D&reserved=0>, or unsubscribe<https://apac01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAANTHCBLDM3FS65IGO5RRV3UCIVK5ANCNFSM5BYMCKMA&data=04%7C01%7C%7Cd91ceba564044cd7e5ac08d9793af16a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637674116334293186%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=0Ls%2F4GAkdUJeG3un%2BfbmbbY87AP07Uz4W%2FFIU45i%2FME%3D&reserved=0>.

[marlon-sousa]

Hello,

Changing to DateTimeWithTimeZone worked, at least somehow.

I see that this translates to chrono::DateTime<FixedOffset>. How does this relate to chrono::DateTime<Utc>? I ask this because I kit francly couldn't find the difference and the way these columns are treatened by other orms is using Utc...

[JohnKinyanjui]

Hello not sure if it will wot=rk for anyone but worked for me

struct Test{
    pub created_at: Option<chrono::NaiveDateTime>,
}