Set timezone based on other column

[gabrielecalvo]

I've tried to look at the docs (especially the temporal ones) as well as the issues and discussions here on GitHub but can't seem to find an answer.

I'd like set the timezone of a column of timestamps (ts) based on values in another column (zone).

import ibis

t = ibis.memtable(
    {
        "ts": ["2000-01-01", "2000-01-01T12:00"],
        "zone": ["America/New_York", "Europe/Berlin"],
    }
).cast({"ts": "timestamp"})

I know that cast can be used for an entire column, but just for a single fixed timezone.

Does anyone know of a good way to do that?

[cpcloud]

If you're okay with the results being in a single timezone, which is the only possibility with all the backends that Ibis supports, then you can use a scalar builtin UDF:

In [23]: from ibis.interactive import *

In [24]: t = ibis.memtable(
    ...:     {
    ...:         "ts": ["2000-01-01", "2000-01-01T12:00"],
    ...:         "zone": ["America/New_York", "Europe/Berlin"],
    ...:     }
    ...: )

In [25]: @udf.scalar.builtin
    ...: def convert_timezone(tz: str, ts) -> dt.Timestamp(timezone="UTC"):
    ...:     pass
    ...:

In [26]: convert_timezone(t.zone, t.ts.cast("timestamp"))
Out[26]:
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ convert_timezone_5(zone, Cast(ts, timestamp)) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ timestamp('UTC')                              │
├───────────────────────────────────────────────┤
│ 2000-01-01 05:00:00+00:00                     │
│ 2000-01-01 11:00:00+00:00                     │
└───────────────────────────────────────────────┘

[gabrielecalvo]

Brilliant! thank you very much 😄