[Python] error raised for `TimestampScalar.__repr__()` when timezone is set using offset `"+00:00"`

[zilto]

Summary

A valid pyarrow.TimestampScalar value throws an error when its .__repr__() is called. This is specific to when timezones are specified as offsets +07:30. It doesn't occur with IANA "America/New_York" and UTC-like "UTC".

from datetime import datetime
import pyarrow

py_value = datetime(2012, 1, 1)

no_tz = pyarrow.scalar(py_value, type=pyarrow.timestamp("s"))
no_tz.__str__() # '2012-01-01 00:00:00'
no_tz.__repr__() # "<pyarrow.TimestampScalar: '2012-01-01T00:00:00'>"
no_tz.as_py().__repr__() # datetime(2012, 1, 1)

utc_tz = pyarrow.scalar(py_value, type=pyarrow.timestamp("s", tz="UTC"))
utc_tz.__str__() # '2012-01-01 00:00:00'
utc_tz.__repr__() # "<pyarrow.TimestampScalar: '2012-01-01T00:00:00'>"
utc_tz.as_py().__repr__() # datetime(2012, 1, 1, tzinfo=<UTC>)

offset_tz = pyarrow.scalar(py_value, type=pyarrow.timestamp("s", tz="+00:00"))
offset_tz.__str__() # '2012-01-01 00:00:00'
offset_tz.__repr__()
# pyarrow.lib.ArrowInvalid: Cannot locate timezone '+00:00': +00:00 not found in timezone database
offset_tz.as_py().__repr__() # datetime(2012, 1, 1, tzinfo=<UTC>) # no error

note: I have limited understanding of cdef, .pyx, .pixi, etc.

Resources

Useful link and resources in my investigation:

• cdef of TimestampScalar overrides the Scalar.__repr__(). It uses pyarrow.compute.strftime()
• pyarrow.compute.strftime is delegated to the kernel
• The kernel definition of strftime
• The source of the raised error
• Asking the docs chatbot What are the valid ways to specify the timezone of a timestamp value? suggests that IANA, UTC, and offsets are valid options. They are the supported conventiosn in C++ TimestampType and Python pyarrow.timestamp

Background

• This bug was initially found via pyarrow.csv.CSVWriter().write() in the library Python dlt.
• I initially thought it was related to the Python library ConnectorX which recently changed Rust backend from arrow2 to arrow (issue)
• Pinned down the bug to Connectorx's change from specifying timezone via "UTC" to "+00:00" (why the change was made, the change)
• Collaborating with Connectorx maintainers, we noticed that TimestampScalar.__str__() and TimestampScalar.__repr__() behaved differently

With this new knowledge, I will have to go back to dlt code for debugging. It's possible that there's no bug with the CSVWriter and what I encountered was the product of using the Python debugger to do the connector-x upgrade.

Component(s)

Python

[rok]

Hi @zilto, thanks for the generous research and report!
This behavior is due to date.h (upstream library we use for timezone logic) not supporting offsets.

As a workaroud you can use "Etc/GMT+1" format that is a valid TZ identifier (this will cover only full hour offsets AFAIK).

We discussed and agreed Arrow should support offsets ([1] [2]). I seem to have a stale PR for this that I could complete at some point or hand it over to someone.

[zilto]

@rok Thanks for the quick reply! Indeed, supporting offsets would be useful.

I'm a maintainer for the Python library dlt and my practical goal is to be able to use connector-x as a dependency. This library uses the Rust crate arrow and brings significant performance improvements.

However, connector-x was dealing with a bug with UTC timezones which appear to be unsupported in the Rust crate.

The time zone is a string indicating the name of a time zone, one of:

• As used in the Olson time zone database (the “tz database” or “tzdata”), such as “America/New_York”
• An absolute time zone offset of the form +XX:XX or -XX:XX, such as +07:30

(i.e., it doesn't support UTC)

They switched to offset-based timezones, which solved their bug, but created downstream issues for some dlt users.

Given what you know, do you think that connector-x adopting tzdata format instead of offsets or UTC could be a lasting solution? For instance, Africa/Bissau seems to match UTC or +00:00 (could be facing daylight saving time issues?). Alternatively, I could modify dlt code to any +00:00 offsets into UTC

Many thanks!

[rok]

I'm a maintainer for the Python library dlt and my practical goal is to be able to use connector-x as a dependency. This library uses the Rust crate arrow and brings significant performance improvements.

Please note arrow-rs has a separate issue tracker, while here we now mostly track C++ implementation and it's wrappers. I am not intimately familiar with the rust implementation so perhaps you want to ask there too.

Given what you know, do you think that connector-x adopting tzdata format instead of offsets or UTC could be a lasting solution? For instance, Africa/Bissau seems to match UTC or +00:00 (could be facing daylight saving time issues?). Alternatively, I could modify dlt code to any +00:00 offsets into UTC

As far as I know UTC, Etc/UCT, Etc/GMT, Etc/GMT+X, Etc/GMT-X are all valid timezones that are equivalent to +/-XX:00 offsets. As long as you don't need +/-XX:30 offsets (I don't know why this is a thing) and logic for mapping offset strings into offset timezones (e.g. -10:00 => Etc/GMT-10:00) can be put somewhere this should imo be a lasting solution. Probably it'd be better if we didn't use offsets at all, but that is probably not a realistic option.

[zilto]

Thank you so much! That was very helpful.

Feel free to close this issue as duplicate or keep it open :)

[zilto]

Summary

TL;DR for people stumbling onto this issue:

• timezones can be specified in various ways (offset, iana); the Arrow specs intend to support all of them
• all formats supported in pyarrow, but some specific features don't support offset-based time zones (e.g., "+01:00").
  • For example, pyarrow._csv.CSVWriter(...).write() or pyarrow.TimeScalar(...).__repr__()
• You're unlikely to face these issues when using pyarrow (Arrow C) because it doesn't default to offset-based timestamps. However, other arrow implementations (e.g., Arrow Rust) may default to using them

Issue

For follow-up, I pinned down my issue to this problem:

import datetime
import pyarrow
from pyarrow._csv import CSVWriter

datetimes = [datetime.datetime(2020, 1, 1, 8, 0, 0), datetime.datetime(2020, 1, 2, 9, 30, 0)]
array = pyarrow.array(datetimes, type=pyarrow.timestamp('s', tz='+00:00'))
table = pyarrow.Table.from_arrays([array], names=["col_timestamptz"])

writer = CSVWriter("file.csv", table.schema)
writer.write(table)
# raises pyarrow.lib.ArrowInvalid: Cannot locate timezone '+00:00': +00:00 not found in timezone database

Manual solution

import pyarrow as pa

def _offset_to_iana(offset: str) -> str:
 """
 Note that UTC+05:00 == Etc/GMT-5
 ref: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones
 """
 hours, _, _ = offset_tzinfo[1:].partition(":")
 sign = "-" if offset_tzinfo[0] == "+" and hours != "00" else "+"
 return f"Etc/GMT{sign}{int(hours)}"


def _replace_offset_tz(item: pa.Table) -> pa.Table:
 new_fields = []
 for field in item.schema:
 if not pa.types.is_timestamp(field.type):
 new_fields.append(field)
 continue
 
 tzinfo = getattr(field.type, "tz", None)
 if isinstance(tzinfo, str):
 if tzinfo.startswith(("+", "-")):
 new_tzinfo = _offset_to_iana(tzinfo)
 new_type = pa.timestamp(field.type.unit, tz=new_tzinfo)
 new_field = pa.field(field.name, type=new_type)
 new_fields.append(new_field)
 
 new_schema = pa.schema(new_fields)
 return item.cast(new_schema)

[zilto]

Solved by #12865