[C++][Python][R] PrettyPrint ignores timezone

[asfimport]

When printing TimestampArray in pyarrow the timezone information is ignored by PrettyPrint (str  calls to_string() in array.pxi).

import pyarrow as pa

a = pa.array([0], pa.timestamp('s', tz='+02:00'))

print(a) # representation not correct?
# <pyarrow.lib.TimestampArray object at 0x7f834c7cb9a8>
# [
#  1970-01-01 00:00:00
# ]

Reporter: Alenka Frim / @AlenkaF
Watchers: Rok Mihevc / @rok

Related issues:

• PrettyPrint Improvements (is a child of)
• [Python] datetime shifted when using pyarrow.Table.from_pandas to load a pandas DateFrame containing datetime with timezone (causes)
• [R] fix behaviour when converting timestamps with "" as tzone (relates to)

_{Note: This issue was originally created as ARROW-14567. Please see the migration documentation for further details.}

[asfimport]

Joris Van den Bossche / @jorisvandenbossche:
The PrettyPrint for Timestamp type is implemented in StringFormatter<TimestampType> at formatting.h (

arrow/cpp/src/arrow/util/formatting.h

Lines 431 to 473 in bf67ec7

	class StringFormatter<TimestampType> {
	public:
	using value_type = int64_t;
	explicit StringFormatter(const std::shared_ptr<DataType>& type)
	: unit_(checked_cast<const TimestampType&>(*type).unit()) {}
	template <typename Duration, typename Appender>
	Return<Appender> operator()(Duration, value_type value, Appender&& append) {
	using arrow_vendored::date::days;
	const Duration since_epoch{value};
	if (!ARROW_PREDICT_TRUE(detail::IsDateTimeInRange(since_epoch))) {
	return detail::FormatOutOfRange(value, append);
	}
	const auto timepoint = detail::kEpoch + since_epoch;
	// Round days towards zero
	// (the naive approach of using arrow_vendored::date::floor() would
	// result in UB for very large negative timestamps, similarly as
	// https://github.com/HowardHinnant/date/issues/696)
	auto timepoint_days = std::chrono::time_point_cast<days>(timepoint);
	Duration since_midnight;
	if (timepoint_days <= timepoint) {
	// Year >= 1970
	since_midnight = timepoint - timepoint_days;
	} else {
	// Year < 1970
	since_midnight = days(1) - (timepoint_days - timepoint);
	timepoint_days -= days(1);
	}
	constexpr size_t buffer_size =
	detail::BufferSizeYYYY_MM_DD() + 1 + detail::BufferSizeHH_MM_SS<Duration>();
	std::array<char, buffer_size> buffer;
	char* cursor = buffer.data() + buffer_size;
	detail::FormatHH_MM_SS(arrow_vendored::date::make_time(since_midnight), &cursor);
	detail::FormatOneChar(' ', &cursor);
	detail::FormatYYYY_MM_DD(timepoint_days, &cursor);
	return append(detail::ViewDigitBuffer(buffer, cursor));
	}

).

Currently this simply does not take into account any timezone information of the type, and formats the stored UTC epoch. Personally, I find this confusing as the current repr for the array has no indication whatsoever how to interpret the printed time values (and without any indication, my first expectation would be local time in the timezone of the type, which isn't the case).

The formatting code already uses the date.h utilities (and which we have vendored anyway), so in principle we could use date.h to first localize the epoch value. However, that makes printing dependent on finding a timezone database (eg not yet supported on Windows at the moment).

An alternative could be to keep the printed value in UTC but add a +00:00 indication to make it it clear this are the UTC times that are printed (and not the wall clock time in the timezone of the type).

cc @rok @pitrou @lidavidm

[asfimport]

Antoine Pitrou / @pitrou:
I think it should be a Z at the end of the string?

[asfimport]

David Li / @lidavidm:
Agreed with Antoine. It may confuse end users slightly but represents what the actual stored value is.

[asfimport]

Joris Van den Bossche / @jorisvandenbossche:
Indeed, Z is more correct than +00:00 (which could still indicate any timezone that just happens to have no offset to UTC at that point in time)

Personally I would still prefer actual localized times, but adding the UTC indication would already help a lot.

[asfimport]

Antoine Pitrou / @pitrou:
If we want to allow outputting localized timestamps, it should probably be an option, to avoid breaking existing uses.

[asfimport]

Rok Mihevc / @rok:
Z and an option to localize sounds good.

What would the final (once timezone database is always present) default be? Z or localized?

[asfimport]

Jonathan Keane / @jonkeane:
Agreed that at the very least falling back to a timestamp with Z is good (e.g. when we can't find the timezone database). But IMO the least confusing to users would be to default to localized.

Separately from the default, we should also indicate in some way that though that the type does include a (non-UTC) timezone. If I only saw a string of timestamps like below (+ Z), I would assume the array was set to UTC (and try casting it to a different TZ, and be confused/annoyed that it 'didn't seem to work')

arr <- Array$create(ts)
arr
#> Array
#> <timestamp[us]>                     <------ maybe add timezone information here?
#> [
#>   2020-01-01 09:00:00.000000,
#>   2020-01-01 10:00:00.000000,
#>   2020-01-01 11:00:00.000000,
#>   2020-01-01 12:00:00.000000,
#>   2020-01-01 13:00:00.000000,
#>   2020-01-01 14:00:00.000000,
#>   2020-01-01 15:00:00.000000,
#>   2020-01-01 16:00:00.000000,
#>   2020-01-01 17:00:00.000000,
#>   2020-01-01 18:00:00.000000
#> ]

[asfimport]

Antoine Pitrou / @pitrou:
Yes, I think adding the timezone information in the type output is the primary fix here. It would be nice to fix the values to add a "Z" as well (only if the timezone is set on the type).

[asfimport]

Joris Van den Bossche / @jorisvandenbossche:
I agree with @jonkeane that IMO the least confusing display would be to use localized strings (with timezone offset indication, so like "1970-01-01 02:00:00+02:00"). Adding "Z" is certainly better than the current situation, but it still doesn't give a quick idea about the local time that the timestamp actually represents.

[asfimport]

Rok Mihevc / @rok:
Agreed! Offset ("1970-01-01 02:00:00+02:00") per value plus timezone string (<timestamp[us, "Pacific/Marquesas"]>) in the header would be great.

[asfimport]

Rok Mihevc / @rok:
Users do seem to be expecting local times: https://stackoverflow.com/questions/73275465/how-to-keep-original-datatime-in-pyarrow-table

[AlenkaF]

I created a PR for the simple version of the fix which is adding a "Z" to the end of the string in case timezone is defined.

>>> import pyarrow as pa

>>> # tz defined
>>> pa.array([0], pa.timestamp('s', tz='+02:00'))
<pyarrow.lib.TimestampArray object at 0x131399fc0>
[
  1970-01-01 00:00:00Z
]

>>> # tz not defined
>>> pa.array([0], pa.timestamp('s'))
<pyarrow.lib.TimestampArray object at 0x13139a020>
[
  1970-01-01 00:00:00
]

I haven't added any change in the type output as the timezone information is already included:

>>> a = pa.array([0], pa.timestamp('s', tz='+02:00'))
>>> a.type
TimestampType(timestamp[s, tz=+02:00])

or do we want info about the timezone in array object print?

[rok]

Thanks for doing this @AlenkaF !
Adding Z is definitely an improvement and removes current ambiguity.

Did you consider adding an option to print in local time as per ISO8601 convention displaying local time + offset from UTC?
This could be done with logic from local_timestamp, what's missing is logic to add timezone offset information. This is currently won't work for cases where timezone is given as a fixed offset (see #12865) but those cases could be handled as exceptions. Given that we could also make a separate issue.

[AlenkaF]

Printing in local time would definitely be good to have. What I was thinking is to have the simple fix asap and then do a follow-up with localized strings when possible.