DATE columns set values with timezone offsets, producing conflicts when db server is on a different timezone. about postgres-nio HOT 5 OPEN

I believe this is ultimately a side effect of your use of .date as the field type used for the birthday field in the Fluent migration - the Fluent Postgres driver maps .date to Postgres's DATE type. However, the Date encoding logic in PostgresNIO unconditionally sends Date values to the server encoded as binary-format TIMESTAMP WITH TIME ZONE (aka timestamptz) values. Although the database type says "with time zone", the format is actually a count of seconds since 2000-01-01T00:00:00Z, always in UTC. Postgres then performs the appropriate time zone adjustment when implicitly coercing the timestamptz input to the column's date type. The reverse does not take place when the values are loaded from the database because Postgres will send it as a DATE (reasonably enough), which does not include the time zone offset needed to perform the inverse adjustment (nor is there, for that matter, any reliable indicator that such an adjustment is necessary by that point).

In short, Fluent and PostgresNIO both act, of necessity, on the assumption that Dates are associated with TIMESTAMPTZ columns (in your migration, change .date to .datetime). In fact, it is safe to say as a general statement that Fluent is not guaranteed to handle either DATE or TIME correctly regardless of which database driver one is using (for example, with MySQL, TIME columns just flat-out don't work at all).

from postgres-nio.

(At the end of the day, it's just another "ORMs are the reason anyone who doesn't work with either audio equipment or electronics has ever even heard of an impedance mismatch" thing, and the last-second choice to shoehorn support for MongoDB into Fluent 4 immediately before the GM release elevated the mismatch to what I'd call almost a flat-out incompatibility after 3 or 4 years of fighting with it 😆. At least I can blame that particular call on @tanner0101, I was against it at the time 🤣.)

from postgres-nio.

Thank you for lightning-fast response. I now better understand where the issue is, and also recognize that changing Fluent 4 to make DATE (i.e., .date) work would also probably mean breaking existing applications that already found their own workarounds.

As to why the importance of DATE (you mention you find this rare in practice)... I agree, but only if we assume we create the schemas ourselves and have the power to choose the field type. Sometimes we need to work with existing databases for which we can only use DML, but no DDL.

In any case, I appreciate your very detailed reply. You have given me many options to work with! Thank you! 😀

from postgres-nio.

Let's just say that this sort of issue, and the difficulty in addressing it in any helpful fashion even if Fluent 4's API could be more freely changed, is a notable entry on the long list of reasons that the very preliminary design work I've finally started for Fluent 5 centers around a much more SQLKit-like API than what we have now 😅.

Oh, and I forgot - there is one other option you could go with:

• Define a new type - or, in fact, for this example, let's use Foundation's DateComponents type, which is the closest thing Swift or Foundation has to a "date-only" or "time-only" representation in any case. The intent is to import PostgresNIO, and conform the type (whether a custom one or not) to the PostgresDecodable and PostgresNonThrowingEncodable protocols. Those implementations can then do what the same conformances that exist for Date do not: recognize Postgres's PostgresDataType.date, .time, .timestamp, and .timestamptz types in PostgresDataFormat.binary format during decoding and choosing the most appropriate one to use during encoding. (Be careful - these are NOT the same as Fluent's DatabaseSchema.DataType.time, .date, .datetime etc. types; the Postgres ones directly refer to Postgres type OIDs, as exchanged via the wire protocol.) How you would handle the time zone adjustment for .date. and .timestamp is up to you - you can't perform a query during encoding or decoding, nor can you add a typecast to the query (although you could tweak RequiredTimestampProperty's AnyDatabaseProperty conformance so that the DatabaseQuery.Values saved to DatabaseInput and read from DatabaseOutput used the .custom() case with an appropriate SQLExpression... but that gets even more complicated 😅). Once you have a type conforming to the PostgresCodable protocols, you can just use that type as the value of an @Field property, and Fluent (via FluentPostgresDriver, then PostgresKit) will eventually call your implementations at the appropriate times 😅. Have a look at Date+PostgresCodable.swift in PostgresNIO for an idea of what's needed.

(Or, you know, just use TIMESTAMPTZ 😂 😂 😂 But you're absolutely right that that isn't always a viable option.)

from postgres-nio.

I really like this new option. Probably too much for my project, because I can really go with TIMESTAMPTZ or simply change the timezone of the database. However, I still want to try PostgresDecodable, if only as an excercise to better understand the whole process. Thanks again! 😁

from postgres-nio.