timezone operations

[kbzsl]

Hi,

Can you recommend any examples or ideas for timezone operations?
I new to ibis and I am a bit stuck with this matter.

I would like to do the following timezone operations, on a bunch of csv files and having duckdb as backend.

• add timezone to a naïve timestamp (~ dt.tz_localize)
• convert the timezone (~ dt.tz_convert)
• remove the timezone (~ dt.tz_localize(None))

Thank you.

[gforsyth]

Hey @kbzsl -- sorry for the long silence here, we forget to look at discussions and we missed this. Can I recommend that you join our Zulip? That's a great place to ask questions like this.
https://ibis-project.zulipchat.com/join/aikedtwz34uvn55jegjadxqn/

[cpcloud]

The operations you're asking for can all be done with the .cast method in Ibis.

In [9]: from ibis.interactive import *

In [10]: naive = ibis.timestamp('2010-01-01 00:01:02.333')

In [11]: less_naive = naive.cast("timestamp('UTC')")  # add a timezone to a naive timestamp

In [12]: less_naive
Out[12]:
┌────────────────────────────────────────────────────────┐
│ Timestamp('2010-01-01 00:01:02.333000+0000', tz='UTC') │
└────────────────────────────────────────────────────────┘

In [13]: less_naive.cast("timestamp('America/New_York')")  # with a specific timezone
Out[13]:
┌─────────────────────────────────────────────────────────────────────┐
│ Timestamp('2009-12-31 19:01:02.333000-0500', tz='America/New_York') │
└─────────────────────────────────────────────────────────────────────┘

In [14]: less_naive.cast("timestamp('America/New_York')").cast("timestamp")  # remove the timezone
Out[14]:
┌─────────────────────────────────────────┐
│ Timestamp('2010-01-01 00:01:02.333000') │
└─────────────────────────────────────────┘

[kbzsl]

Hi @cpcloud,

Thank you for your answer.

Sorry, I am afraid that this code example is not preforming the task that I am looking for: converting from one timezone to another both in naive formats.
In case of UTC -> US, the expected result should be:
Timestamp('2010-01-01 00:01:02.333000+0000') -> Timestamp('2009-12-31 19:01:02.333000')

The last step ([14]) converts back to the original (naive) timestamp value and not to a naive format (without TZ) in the new timezone (US).

Anyhow, I will look more into the .cast() method to try to solve it.

[kbzsl]

In meantime as a workaround, I was using a duckdb UDF based custom function in mutate().

@ibis.udf.scalar.builtin
def timezone(text, timestamp) -> str:
  ...

def dbb_timezone_convert(
  timestamp,
  from_tz = 'UTC',
  to_tz = 'America/New_York'):

  return(timezone(to_tz, timezone(from_tz, timestamp)))

But I am not sure which is a "better" solution.
I reckon that the .cast() method could provide a more general/universal solution.