search

will / crystal-pg

a postgres driver for crystal
BSD 3-Clause "New" or "Revised" License
462 stars 77 forks source link

Add ability to automatically return `TIMESTAMPTZ`->`Time` in the Postgres session's time zone #265

Closed jgaskins closed 1 year ago

jgaskins commented 1 year ago

TIMESTAMPTZ columns are decoded without time zones, so I was trying to separate TIMESTAMP from TIMESTAMPTZ in TimeDecoder but discovered that Postgres transmits them over the wire identically, without time zone info, and found this comment with a source that corroborates that.

The psql client automatically transforms timestamps into the correct time zone based on the session/transaction's TIME ZONE setting. My thought was that we could do the same here. Maybe after connecting we could run something like @time_zone = query_one("SHOW TIME ZONE", as: String), and then TimeDecoder would run time.in(time_zone) for TSTZ columns, which would require passing the timezone somehow.

I'm not sure how we'd handle SET LOCAL TIME ZONE to set it for a transaction, though. We could maybe provide a Transaction#time_zone=(location : Time::Location) method, but I don't think the crystal-db shard supports driver-specific transactions yet so that would have to be a monkeypatch if we did it. If we don't provide a method for it, I don't know if we have a good way to detect the change.