[C++][Python] Cannot filter dataset with a timestamp (with timezone) column

[brokenjacobs]

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

Pyarrow: 12.0.1

This is similar to #32366 but not exactly the same. I have a dataset where I am trying to filter by a column in my parquet files that is a timestamp with timezone type. Every way that I try to do the filter, pyarrow coerces the type to a non-timestamp type and fails to convert to a table. For example:

(ds is my dataset)
>>> dt
datetime.datetime(2023, 6, 29, 4, 0, tzinfo=<UTC>)
>>> dcast = pa.scalar(dt, type=pa.timestamp('ms', tz='UTC')
>>> filter = (ds.field('readout_time')>dcast)
>>> df = dataset.to_table(filter=filter,columns=['readout_time', 'acceleration_x', 'acceleration_y', 'acceleration_z']).to_pandas()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "pyarrow/_dataset.pyx", line 546, in pyarrow._dataset.Dataset.to_table
  File "pyarrow/_dataset.pyx", line 3449, in pyarrow._dataset.Scanner.to_table
  File "pyarrow/error.pxi", line 144, in pyarrow.lib.pyarrow_internal_check_status
  File "pyarrow/error.pxi", line 100, in pyarrow.lib.check_status
pyarrow.lib.ArrowInvalid: Cannot compare timestamp with timezone to timestamp without timezone, got: timestamp[ms, tz=UTC] and timestamp[ms]

This was just my latest attempt to use an actual arrow scaler with timezone, and it still changes the type to a non timestamp type in the comparison. It also fails using a datetime in the filter directly.

Component(s)

Python

[mapleFU]

ds.schema.to_string would you mind print this?

[brokenjacobs]

>>> dataset.schema.to_string
<built-in method to_string of pyarrow.lib.Schema object at 0x12f357570>

But I think you wanted this?:

source_id: string
site_id: string
readout_time: timestamp[ms, tz=UTC]
packet_counter: uint16
roll: float
pitch: float
yaw: float
acceleration_x: float
acceleration_y: float
acceleration_z: float
free_acceleration_x: float
free_acceleration_y: float
free_acceleration_z: float
gyroscope_x: float
gyroscope_y: float
gyroscope_z: float
status_word: int32
kafka_key: string
kakfa_ts_type: uint8
kafka_ts: timestamp[ms]
kafka_partition: uint8
kafka_offset: uint64
kafka_topic: string
ds: string
ms: string
-- schema metadata --
parquet.avro.schema: '{"type": "record", "name": "mti300ahrs", "namespace' + 3061
writer.model.name: 'avro'

or

>>> dataset.schema.to_string()
'source_id: string\nsite_id: string\nreadout_time: timestamp[ms, tz=UTC]\npacket_counter: uint16\nroll: float\npitch: float\nyaw: float\nacceleration_x: float\nacceleration_y: float\nacceleration_z: float\nfree_acceleration_x: float\nfree_acceleration_y: float\nfree_acceleration_z: float\ngyroscope_x: float\ngyroscope_y: float\ngyroscope_z: float\nstatus_word: int32\nkafka_key: string\nkakfa_ts_type: uint8\nkafka_ts: timestamp[ms]\nkafka_partition: uint8\nkafka_offset: uint64\nkafka_topic: string\nds: string\nms: string\n-- schema metadata --\nparquet.avro.schema: \'{"type": "record", "name": "mti300ahrs", "namespace\' + 3061\nwriter.model.name: \'avro\''

The code that created this file set the timezone in the schema.

[mapleFU]

>>> dataset_ts.schema.to_string()
'DATE_OF_BIRTH: timestamp[ns, tz=UTC]'
>>> date
datetime.datetime(1990, 1, 1, 0, 0, tzinfo=datetime.timezone.utc)
>>> dcast = pa.scalar(dt, type=pa.timestamp('ms', tz='UTC')
>>> dcast = pa.scalar(date, type=pa.timestamp('ms', tz='UTC'))
>>> filter = (ds.field('DATE_OF_BIRTH')>dcast)
>>> df = dataset_ts.to_table(filter=filter, columns=['DATE_OF_BIRTH'])
>>> df

Emm I've try to use this in 9.0, seems it works, but in 12.0.1 it would failed. Let me checkout whats changed here

[mapleFU]

>>> filter = (ds.field('DATE_OF_BIRTH') > pa.compute.scalar(date).cast(type=pa.timestamp('ms', 'UTC')))
>>> dataset.to_table(filter=filter)

Can you try like this to workaround first?

[mapleFU]

( I guess it might related to b56b91e )

[brokenjacobs]

Sorry just got back from vacation, do you still need more testing done on this?

[mapleFU]

@brokenjacobs I've fixed this. but this is not in 13.0.0 release...