[C++] Casting a zoned timestamp to string (and the CSV writer) truncates UTC offsets that have seconds, so the written instant is wrong #54534

Describe the bug, including details regarding any error messages, version, and platform.

cast(timestamp[unit, tz] -> string) formats a zoned timestamp as local wall time plus a %z offset. The wall time is computed with the zone's exact UTC offset, but %z prints only hours and minutes. When the offset has a seconds part, the text therefore names a different instant than the stored value, off by up to 59 seconds. Examples of such offsets: Africa/Monrovia until 1972 (−0:44:30), Europe/Dublin until 1916, Europe/Paris until 1911, and the local mean time (LMT) of every IANA zone before it adopted standard time. pyarrow.csv.write_csv (and write_dataset(format="csv")) converts columns with this cast, so CSV exports of such values are wrong, and every reader of the file, pyarrow included, loads the shifted instant. No error or warning is raised.

Reproducer

import io
from datetime import datetime, timezone
import pyarrow as pa
import pyarrow.compute as pc
import pyarrow.csv as csv

# 1960-06-01 12:00:00 UTC; Africa/Monrovia was at UTC-00:44:30 then
utc = pa.array([datetime(1960, 6, 1, 12, tzinfo=timezone.utc)], pa.timestamp("us", "UTC"))
ts = utc.cast(pa.timestamp("us", "Africa/Monrovia"))
print(ts[0].as_py())                        # 1960-06-01 11:15:30-00:44:30 (correct)

print(pc.cast(ts, pa.string())[0])          # cast to string

buf = io.BytesIO()
csv.write_csv(pa.table({"ts": ts}), buf)
print(buf.getvalue().decode())

back = csv.read_csv(io.BytesIO(buf.getvalue()),
                    convert_options=csv.ConvertOptions(column_types={"ts": pa.timestamp("us", "UTC")}))
print(back["ts"][0], "!=", utc[0])

Output on main:

1960-06-01 11:15:30-00:44:30
1960-06-01 11:15:30.000000-0044
"ts"
1960-06-01 11:15:30.000000-0044

1960-06-01 11:59:30+00:00 != 1960-06-01 12:00:00+00:00

11:15:30 is the correct local time for offset −00:44:30, but it is printed next to -0044. Together they mean 11:59:30 UTC, 30 seconds off. DuckDB and Polars read the CSV as the same shifted instant. Other examples on main:

• Europe/Dublin 1900-06-01 12:00 UTC → 11:34:39-0025 (−21 s)
• Europe/Paris 1900-06-01 12:00 UTC → 12:09:21+0009 (+21 s)
• America/New_York 1880-06-01 12:00 UTC → 07:03:58-0456 (−2 s)

All units (s/ms/us/ns) are affected. Offsets that are whole minutes, and the "UTC" zone (formatted with Z), are correct.

Expected behavior: the string names the same instant as the stored value. Two ways to get there:

• Print the offset with its seconds (e.g. -00:44:30, as Python's isoformat() does).
• Format in UTC (...Z) when the offset is not a whole number of minutes, or always for the CSV writer.

Versions: pyarrow 27.0.0.dev13 (nightly built from main at 9f03413), Python 3.12, Linux x86_64 (Ubuntu 22.04). The released pyarrow 21.0.0 through 26.0.0 wheels behave the same.

Where it comes from

• CastFunctor<StringType, TimestampType>::ConvertZoned formats with "%Y-%m-%d %H:%M:%S%z" for every zone other than "UTC" (scalar_cast_string.cc#L196-L215). It formats through TimestampFormatter, i.e. chrono::to_stream on a zoned time (temporal_internal.h#L167-L195).
• The vendored date library implements %z as duration_cast<minutes>(*offset_sec) and prints ±hhmm, dropping the seconds of the offset (vendored/datetime/date.h#L6012-L6043). The %H:%M:%S fields, by contrast, use the exact local time.
• The CSV writer converts each column with compute::Cast(data, utf8()) (csv/writer.cc#L131-L144), so it inherits the problem.

Impact: historical, genealogical or astronomical timestamps stored in a local zone are exported to CSV (or cast to string) with instants that differ from the table by up to 59 seconds, without any error. Tables loaded back from such files hold the shifted values. Found by differential testing of CSV round trips.

Component(s)

C++, Python

Component(s)

C++