hanke580 opened a new issue, #54534:
URL: https://github.com/apache/arrow/issues/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**
```python
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](https://github.com/apache/arrow/blob/9f0341314ca96eb58081d4132c77b460d880b2b0/cpp/src/arrow/compute/kernels/scalar_cast_string.cc#L196-L215)).
It formats through `TimestampFormatter`, i.e. `chrono::to_stream` on a zoned
time
([temporal_internal.h#L167-L195](https://github.com/apache/arrow/blob/9f0341314ca96eb58081d4132c77b460d880b2b0/cpp/src/arrow/compute/kernels/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](https://github.com/apache/arrow/blob/9f0341314ca96eb58081d4132c77b460d880b2b0/cpp/src/arrow/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](https://github.com/apache/arrow/blob/9f0341314ca96eb58081d4132c77b460d880b2b0/cpp/src/arrow/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++
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
