Confusion converting DateTime<Utc> value to BigInteger for SQLite

[zebrapurring]

Hi all, I'm trying to add column timestamp for my SQLite entity where I would like:

• The Rust data type to be chrono::DateTime<chrono::Utc>
• The SQLite data type to be a 64-bit integer BIGINTEGER containing the date time value in milliseconds (UNIX time)

So far I tried the following:

#[sea_orm(primary_key, column_type = "BigInteger")]
#[serde(with = "chrono::serde::ts_milliseconds")]
pub timestamp: DateTime<Utc>,

However when I insert an entity I see the generated timestamp value is a string in format YYYY-mm-dd HH:MM:SS +00:00. How can I map the DateTime<Utc> value to milliseconds as i64 (and viceversa) when interfacing with the database?

[For8stt]

#[serde(with = "chrono::serde::ts_milliseconds")] only affects JSON/serde serialization. SeaORM does not use that when binding values to SQL.

So this field:

pub timestamp: DateTime<Utc>

will still be handled by SeaORM as a datetime value, not as an integer timestamp.

The simple option is to store it as i64 in the DB model:

#[sea_orm(primary_key, column_type = "BigInteger")]
pub timestamp: i64,

and convert at the boundary:

let dt = DateTime::<Utc>::from_timestamp_millis(model.timestamp)
    .expect("valid timestamp millis");

let millis = dt.timestamp_millis();

If you want your public/API type to use DateTime<Utc>, I’d keep a separate DTO/domain struct:

struct MyDomainModel {
    timestamp: DateTime<Utc>,
}

and convert between the SeaORM model and the domain model.

So imo:

• DB model: i64
• domain/API model: DateTime<Utc>
• conversion: timestamp_millis() / from_timestamp_millis()

That keeps the SQLite storage exactly as BIGINTEGER and avoids fighting SeaORM’s built-in chrono mapping.