jiff_diesel: default serialization format for sqlite is especially error-prone

[kyegupov]

Sqlite uses stringly-typed datetimes, and that causes a world of hurt if you are not careful.

In this particular occasion: jiff-diesel serializes DateTime as YYYY-MM-DDTHH:MM:SS.
But built-in Sqlite functions, including current_timestamp (which is emitted by diesel::dsl::now), produce YYYY-MM-DD HH:MM:SS format (space instead of T).

Thus, if you use naive comparisons, you can get stuff like 2025-06-01T00:00:00 > 2025-06-01 10:00:00. Ouch.

Another pain point that current_timestamp does not include timezone information, so you cannot parse the values it produces at jiff_diesel::Timestamp

What jiff_diesel (and maybe jiff_sqlx) crate can do to mitigate the problem:

• add a notice in the documentation (e.g. "Database support" section) that Sqlite is a special beast, you need to be extra careful about your time representation, and that current_timestamp/dsl::now would produce DateTime, not Timestamp values.
• make the format for DateTime YYYY-MM-DD HH:MM:SS because that matches the format Sqlite functions output
• maybe make jiff_diesel's Timestamp also serialize as YYYY-MM-DD HH:MM:SS in UTC?

[kyegupov]

(personally, how I am mitigating the problem for now: getting rid of jiff_diesel, and writing my own Timestamp wrapper with custom ToSql / FromSql implementations that use YYYY-MM-DD HH:MM:SS in UTC)

[BurntSushi]

and writing my own Timestamp wrapper with custom ToSql / FromSql implementations that use YYYY-MM-DD HH:MM:SS in UTC

This is its own footgun. It is extremely common to incorrectly assume that an unadorned datetime string is UTC when it is actually local time.

From what I can tell here, these are problems with SQLite that aren't really possible for Jiff to work around? Maybe you can offer some specific remedies to the situation? (Althrough what you're doing may work for you, it is off the table more generally.)

[kyegupov]

You are correct in that it's fundamentally impossible to make SQLite validate what you're putting into it.

But at least we can make an effort to use their default format and thus make jiff_diesel::Timestamp format-compatible with diesel::dsl::now, which is currently not the case.

I ended up with this:

// An adapter for Timestamp that converts it to the default format of SQLite.
//
// WARNING: SQLite infamously allows any text string to be stored in a timestamp column.
// To ensure correct handling, the user should use its date and time functions:
// https://www.sqlite.org/lang_datefunc.html
//
// Thus, it is fundamentally unsafe to compare SQLite "timestamps" as raw values (e.g. via Diesel's
// .eq() or .lt() DSL operators); one should use `timediff()` instead.
//
// However, the default format that Sqlite's `current_timestamp` and `datetime()` functions emit is
// `YYYY-MM-DD HH:MM:SS` in UTC time zone. This adapter uses the same format.
//
// Therefore, if you only ever produce timestamps using this type or the functions mentioned above
// (including `diesel::dsl::now`, which emits `current_timestamp`, lexicographic comparisons of
// those timestamps would work correctly.
#[derive(Debug, Clone, Copy, PartialEq, FromSqlRow, AsExpression)]
#[diesel(sql_type = diesel::sql_types::Timestamp)]
pub struct SqliteTimestamp(pub Timestamp);

impl ToSql<diesel::sql_types::Timestamp, Sqlite> for SqliteTimestamp {
    fn to_sql<'b>(
        &'b self,
        out: &mut diesel::serialize::Output<'b, '_, Sqlite>,
    ) -> diesel::serialize::Result {
        out.set_value(
            self.0
                .to_zoned(TimeZone::UTC)
                .datetime()
                .strftime("%Y-%m-%d %H:%M:%S")
                .to_string(),
        );
        Ok(IsNull::No)
    }
}
impl FromSql<diesel::sql_types::Timestamp, Sqlite> for SqliteTimestamp {
    fn from_sql(bytes: <Sqlite as Backend>::RawValue<'_>) -> diesel::deserialize::Result<Self> {
        let s = <String as FromSql<diesel::sql_types::Timestamp, Sqlite>>::from_sql(bytes)?;
        let dt = DateTime::strptime("%Y-%m-%d %H:%M:%S", &s)?;
        Ok(SqliteTimestamp(dt.to_zoned(TimeZone::UTC)?.timestamp()))
    }
}

impl From<Timestamp> for SqliteTimestamp {
    fn from(value: Timestamp) -> Self {
        SqliteTimestamp(value)
    }
}

impl From<Zoned> for SqliteTimestamp {
    fn from(value: Zoned) -> Self {
        SqliteTimestamp(value.timestamp())
    }
}

impl Into<Timestamp> for SqliteTimestamp {
    fn into(self) -> Timestamp {
        self.0
    }
}

[BurntSushi]

But at least we can make an effort to use their default format and thus make jiff_diesel::Timestamp format-compatible with diesel::dsl::now, which is currently not the case.

This is exactly what I'm saying is a giant footgun. Dropping the Z from the timestamp completely changes its meaning outside of any non-SQLite context.

I get that you might want to do this. And that it might make sense for you and your application. In which case, you can have your own adapter or convert to civil time or whatever. But I don't think it makes sense to do this automatically and by default.

[kyegupov]

Dropping the Z from the timestamp completely changes its meaning outside of any non-SQLite context.

Yeah but... this serialization will happen specifically only in SQLite context, wouldn't it?

The main problem, sadly, is not with Z, but with T versus as a separator. jiff_diesel would produce 2025-01-01T10:00:00 and it will be compared incorrectly with 2025-01-01 11:00:00 produced by diesel::dsl::now.

This problem is apparently known to the Diesel team, and they actually handled it gracefully when implementing Chrono integration:

https://docs.diesel.rs/2.1.x/src/diesel/sqlite/types/date_and_time/chrono.rs.html#226-234

They:

• use whitespace as a separator
• normalize to UTC

A bit sad that this is not properly documented, but at least their implementation strives to do the working thing by default: the produced timestamp representation is lexicographically comparable with the output of current_timestamp.

So, I would not have encountered the problem I'm complaining about with diesel[chrono], but jiff_diesel out-of-the box creates a subtle bug in my code (dates were compared correctly, but times weren't).

I think having subtle bugs by default is something that should be avoided if possible.

[BurntSushi]

OK, I see what you're saying now. I thought you were talking about Diesel does, but actually, it's rooted in what SQLite does. I guess you said this, but I got confused by current_timestamp, which... isn't a thing? In the future, it helps to be more concrete. For example, this shows the issue:

sqlite> SELECT datetime('now');
datetime('now')
-------------------
2025-06-03 18:23:29

The issue here is that if Jiff serializes with a T, then as you say, lexicographic comparison breaks. And since SQLite doesn't seem to have datetime specific comparison routines, you're just supposed to rely on lexicographic comparisons. That is pretty wild. So wild that maybe Jiff should indeed match SQLite's default serialization format.

This still seems like a massive footgun to me. Dropping the Z and "just assuming UTC" is an extremely common problem that Jiff goes out of its way to prevent. SQLite very specifically perpetuates that problem. But I guess if we're going to provide an integration with SQLite, we should probably do what it does?

I'm still very unsure about this personally.

[kyegupov]

SQLite does in fact have routine for comparison (timediff) - but it's icky and it's not what ORMs would use by default when comparing dates.

current_timestamp is a thing, both in SQLite (https://sqlite.org/lang_createtable.html#the_default_clause - but can be also used as a value) and in Diesel (https://github.com/diesel-rs/diesel/blob/master/diesel/src/expression/functions/date_and_time.rs#L19-L24)

Yeah, no solution would be perfect. diesel[chrono] approach is: they serialize zoned/timestamp formats with Z, but allow parsing from strings without timezones, by assuming UTC (which matches the assumption SQLite functions make).

[BurntSushi]

Parsing from strings without time zones is another thing Jiff specifically goes out of its way to prevent. I don't know if I can sign off on that in a library that I maintain.

If all I do here is use instead of T, but keep the trailing Z for Timestamp, would that help at all?

[kyegupov]

That would help for comparing the timestamps for sure!

The remaining downside will be if you define a field created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP field, define it as Timestamptzsqlite in your schema, then deserializing into jiff_diesel::Timestamp would fail for auto-inserted timestamps.

I'll leave it to you to decide what's best. Might be it's worth to create something like StrictTimestamp and RelaxedTimestamp, document the difference and let users choose. I think most would opt for the "relaxed" one, tbh, given the practicalities.

[BurntSushi]

Yeah I do feel a little better about adding another type so that users can opt into this sort of behavior.