How to handle timestamps

[david-waterworth]

I have a Postgres table containing a timestamp column in UTC (i.e. the Postgres type is timestamptz - timestamp with timezone)

If I query using connector-x the pyarrow.Table has type timezone[ns, tz=UTC] - this is sortable but doesn't allow me to create timezone features such as the number of events per day without converting to say timezone[ns, tz=Australia/Sydney]

import connectorx as cx
cx.read_sql(DSN, "select ts as ts_utc from mytable limit 1", return_type="arrow2")

pyarrow.Table
ts_utc: timestamp[ns, tz=UTC]
\----
ts_utc: [[2022-03-19 21:18:10.348000000]]

If I perform the timezone conversion on the server, postgres converts to a returns a timestamp - timestamp without timezone) meaning I can create timezone features but cannot correctly sort. The arrow type is timezone[ns]

import connectorx as cx
cx.read_sql(DSN, "select ts at time zone 'Australia/Sydney' as ts_local from mytable limit 1", return_type="arrow2")

pyarrow.Table
ts_local: timestamp[ns]
----
ts_local: [[2022-03-20 08:18:10.348000000]]

It seems that in order to return a postgres timestamptz in a specified timezone requires

SET timezone 'Australia/Sydney';
select select ts as ts_local from mytable limit 1;

But connector-x doesn't appear to allow this?

Otherwise I have to either return both the ts_utc as timestamp[ns, tz=UTC] and ts_local as timestamp[ns] in order to support both sorting and calendar based features, or return ts_utc as timestamp[ns, tz=UTC] and replace it with ts_local as timestamp[ns, tz=Australia/Sydney] on the client.

Are there better options?