Timestamp decoder fails when decoding timestamptz

[isaacharrisholt]

Trying to decode a Postgres timestamptz value with pog.timestamp_decoder() results in:

UnexpectedResultType([DecodeError("Int", "Array", ["0"])])

I think this is possibly(?) desirable, as we might want to handle timestamptz separately, but given they're ultimately both going to the same Gleam type, I'm not necessarily sure that's the best option.

[lpil]

Given timestampz is timestamp but the client is expected to convert it to calendar time I don't quite understand the goal here. Could you expand a little on the situation please 🙏

[isaacharrisholt]

timestamptz is just timstamp but Postgres doesn't discard timestamp information (e.g. if I insert 2025-01-01T00:00:00+0100 it will correctly store 2024-12-31T23:00:00Z. It would be useful to be able to query that directly in pog so I can just do something like set timezone = 'America/New_York' and just select the timestamptz column. With timestamptz, Postgres will do the conversion from the stored UTC to the DB timezone. With just timestamp, Postgres will return the same value regardless of the timezone of the DB.

I'd like to be able to query timestamps without having to do that conversion myself every time.

[lpil]

timestamptz is just timstamp but Postgres doesn't discard timestamp information

That's not true, the timezone is discarded:

For timestamp with time zone values, an input string that includes an explicit time zone will be converted to UTC (Universal Coordinated Time) using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone. In either case, the value is stored internally as UTC, and the originally stated or assumed time zone is not retained. https://www.postgresql.org/docs/current/datatype-datetime.html

---

It would be useful to be able to query that directly in pog so I can just do something like set timezone = 'America/New_York' and just select the timestamptz column. With timestamptz, Postgres will do the conversion from the stored UTC to the DB timezone. With just timestamp, Postgres will return the same value regardless of the timezone of the DB.

I'd like to be able to query timestamps without having to do that conversion myself every time.

Here you're saying that you want to take a timestamp in the database, have the database client convert it into calendar time, and then you want pog to convert it back into a timestamp. That's entirely wasted work, so I don't understand what your motivation is here.

[chiroptical]

pgo now has support for timestamptz, would you like me to bring it in? The previous comment seems to suggest maybe not?

[lpil]

Cool! What does it decode them to?

We need to think carefully about how to not encourage suboptimal time handling when working with PG