Computing date features using PyArrow on mixed timezone data

I have timeseries data stored as (series_id,timestamp,value) in postgres. The timestamp is stored in UTC and there's a separate metadata table containing (series_id,timezone).

I need to compute date features (i.e. weekday/weekend/holiday etc) that require the timestamp to be converted into local time. I'm investigating options to scale this, one of which is pyarrow. But I'm having issues with the timezone, for example if I have three timestamps in different timezones

import pandas as pd
df = pd.DataFrame.from_dict({
    "timestamp":[
        pd.Timestamp(year=2023, month=3, day=7, tz='Australia/Brisbane'),
        pd.Timestamp(year=2023, month=3, day=7, tz='Australia/Sydney'),
        pd.Timestamp(year=2023, month=3, day=7, tz='Australia/Adelaide'),
    ]
})

Pandas stores this as dtype('O') (i.e. object) and each Timestamp retains it's associated timezone.

But Arrow appears to represent a timestamp column as having a consistent timezone (or none).

table = pa.Table.from_pandas(df)

> pyarrow.Table timestamp: timestamp[us, tz=Australia/Brisbane]
> ---- timestamp: [[2023-03-06 14:00:00.000000,2023-03-06 13:00:00.000000,2023-03-06 13:30:00.000000]]

It's storing everything as UTC but assigning the datatype based on the timezone from the first entry (i.e. Brisbane). The resulting computations are then incorrect as they're all done relative to Australia/Brisbane

pa.compute.day(table['timestamp'])

> <pyarrow.lib.ChunkedArray object at 0x7f381a04e070> [ [
> 7,
> 6,
> 6 ] ]

The correct results should be [7,7,7]

I cannot find much on best practices for performing computation on arrow timestamps - I understand best practice for storage is usually to convert to UTC and store the tzinfo but I even if I have a timestamp and tzinfo array something like pa.compute.day(table['timestamp_utc'], table['tzinfo']) doesn't seem to exist

My pandas solution simply processes the data series by series in a loop. All timestamps for a single series have the same tzinfo so I just convert to localtime. Do I have to do the same with arrow or is there another approach?

I think you're right, that Arrow is not currently able to help much here. Arrow defines timezone at the "column" level (e.g. the data type of the array) and not at the "cell" level. So all values in an array must have the same timestamp.

You could convert all values to UTC as you import them but this would cause the time zone information to be lost. So, if all you wanted to do was sort and compare "instants" then you'd be fine.

However, if you want "features" (e.g. how many events occurred on a Saturday/Sunday) then you will need the time zone for each event. The "correct" (in my opinion, there is no official stance on this as far as I know) way to store this in Arrow would be to have one column of "instants" (UTC timestamps) and one column of time zone strings.

Unfortunately, in order to process this data, you would need a function like get_day_of_week(instants, time_zones) which does not exist in Arrow today (the only function that exists is get_day_of_week(instants) and it uses the time zone of the column).

EDIT: To be clear, my answer was in regards to pyarrow (full disclaimer, I work on pyarrow). There are other Arrow compute resources (e.g. datafusion, duckdb, etc.) and they may offer different capabilities (though there is no Arrow array type for "timestamps with individual time zones" and so any engine that supports this will need at least two columns)