SQLite: Format of DateTimeUtc is wrong about sea-orm HOT 14 OPEN

Any information regarding this — or about a good workaround?
This is a blocker because, e.g., the following code always returns true:

Expr::col(Column::ExpiresAt).gte(Expr::current_timestamp())

from sea-orm.

I'm not sure how to force SeaORM to save model fields in a different format. You can apply a workaround at the level of your query. Something like this (not tested):

Expr::cust_with_expr("unixepoch(?)", Expr::col(Column::ExpiresAt)).gte(Expr::cust("unixepoch('now')"))

According to the documentation you linked, 2024-07-17T17:02:14.727131+00:00 is a valid time value that should be parsed by unixepoch, even though the formatting doesn't match the default SQLite formatting.

from sea-orm.

According to the documentation you linked, 2024-07-17T17:02:14.727131+00:00 is a valid time value that should be parsed by unixepoch, even though the formatting doesn't match the default SQLite formatting.

Where? I cannot find a format with milliseconds and timezones.

The datetime() function returns the date and time formatted as YYYY-MM-DD HH:MM:SS or as YYYY-MM-DD HH:MM:SS.SSS if the subsec modifier is used.

And the problem is, there are other applications which also depend on the same data structure. When now seaORM saves all timestamps in a different format this causes problems or at least confusion.

Where is the format defined? Is it on your side or in sqlx?

from sea-orm.

And the problem is, there are other applications which also depend on the same data structure. When now seaORM saves all timestamps in a different format this causes problems or at least confusion.

Sure, I agree, it would be better if SeaORM used the standard format.

Where is the format defined? Is it on your side or in sqlx?

I'm not a maintainer and I'm not familiar with the lower-level part that interacts with SQLx, so I don't know. I just suggested a quick workaround that doesn't require digging into that. If you need to store the right format, then don't use that workaround. I guess, your options are:

• Waiting for someone to answer your question about the format source.
• Figuring that out yourself.
• Executing additional statements to reformat the dates after SeaORM saves them. ActiveModelBehavior::after_save may be a good place to do that automatically, if N+1s won't be an issue for your use case.

I cannot find a format with milliseconds and timezones.

It's this format with T and milliseconds:

7. YYYY-MM-DDTHH:MM:SS.SSS

The timezone is OK, as the doc says two paragraphs ahead:

Formats 2 through 10 may be optionally followed by a timezone indicator of the form "[+-]HH:MM" or just "Z".

It gives this example:

2013-10-07T08:23:19.120Z

from sea-orm.

Executing additional statements to reformat the dates after SeaORM saves them. ActiveModelBehavior::after_save may be a good place to do that automatically, if N+1s won't be an issue for your use case.

This is not an option, as it only creates preventable load.

I'm not a maintainer and I'm not familiar with the lower-level part that interacts with SQLx, so I don't know.

Ok, thanks anyway and then I hope for an answer from a maintainer or similar.

from sea-orm.