Bug in TimeStamp with Time Zones for Arrow

[EricFecteau]

Issue

The Connector-X code converts timezone data using UTC around here. But it does not seem to work correctly:

This is because, according to arrow docs, you have to have the chrono-tz feature enable to do this and Connector-X does not have this enabled anywhere.

When feature chrono-tz is not enabled, allowed timezone strings are fixed offsets of the form “+09:00”, “-09” or “+0930”. When feature chrono-tz is enabled, additional strings supported by chrono_tz are also allowed, which include IANA database timezones.

Fix

I am currently writing tests for all the types (at least all the types in PostgreSQL at this time) and I can fix this by either using +00:00 instead of UTC (I confirmed it works) or by enabling the chrono-tz in my next PR.

Verification of understanding

Could you please also confirm my understanding.

The Timestamp (without time zone) is mapped to None time zone in Arrow (through the NaiveDateTime in Chrono), because it is not meaningfully useful to think of it as UTC time, as explained in the Arrow docs:

Therefore, timestamp values without a timezone cannot be meaningfully interpreted as physical points in time, but only as calendar / clock indications (“wall clock time”) in an unspecified timezone.

For example, the timestamp value 0 with an empty timezone string corresponds to “January 1st 1970, 00h00” in an unknown timezone: there is not enough information to interpret it as a well-defined physical point in time.

One consequence is that timestamp values without a timezone cannot be reliably compared or ordered, since they may have different points of reference. In particular, it is not possible to interpret an unset or empty timezone as the same as “UTC”.

And while the PostgreSQL docs don't explicitly mention this, they also don't call their Timestamp as having UTC time, but they definitely call their TimestampTz time UTC

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

So it makes sense to have the Naive time for Timestamp and the Time Zone time for TimeStampTz!

[wangxiaoying]

Hi @EricFecteau , thanks for opening the issue!

I think you are correct. I think we should distinguish naive time and time zone time!

[EricFecteau]

Thanks for the confirmation! To be clear, the implementation is already all there with Timestamp, just had a bug!