The value of timezone in the datatype of timestamp means the value of timestamp is a reference to the UTC/UNIX time.
https://github.com/apache/arrow/blob/main/format/Schema.fbs#L283

We need to add the timezone config in the CastOptions which can't be configured by other system like datafusion.

The conversion should take the date of the timestamp in the timezone configured on the timestamp data type, we should not need to configure this on CastOptions, we have very carefully avoided introducing the notion of a "local" timezone.

So for example if we had 2019-10-12T07:20:50.52 +10:00, the date returned would be 2019-10-12 even though with reference to the UTC epoch (which is what the timestamp integer encodes) the date would be 2019-10-11.

@tustvold Hi, I have refactored the code to use DateTime, please take a look when you have time. Thank you!

We need to add the timezone config in the CastOptions which can't be configured by other system like datafusion.

The conversion should take the date of the timestamp in the timezone configured on the timestamp data type, we should not need to configure this on CastOptions, we have very carefully avoided introducing the notion of a "local" timezone.

So for example if we had 2019-10-12T07:20:50.52 +10:00, the date returned would be 2019-10-12 even though with reference to the UTC epoch (which is what the timestamp integer encodes) the date would be 2019-10-11.

Hi @tustvold
From the definition of the timestamp in the arrow schema: https://github.com/apache/arrow/blob/main/format/Schema.fbs#L283

It's will cause inconvenient usage, let me give an example:

When I write a parquet file with timestamp column using the spark engine, the timestamp column is encoded by https://github.com/apache/parquet-format/blob/master/LogicalTypes.md#instant-semantics-timestamps-normalized-to-utc and is normalized to UTC.

But when I use arrow-parquet to read the timestamp column, i will just get the column with timestamp(unit, "UTC") data type by this code

It's even possible to use int96 to store the timestamp in the parquet file, we will get the column with timestamp(nanosecond, None) data type by

But i need this to used in the UTC-7 timezone, how did you resolve this issue? @alamb

You will need to explicitly cast the timezone back, unfortunately as parquet has no mechanism for encoding the timezone this will need to be done manually. Arrow writers embed the arrow schema allowing this to be done automatically, but Spark doesn't do this

Maybe we can add some way to the parquet arrow reader to override its choice of data type for certain columns to allow users to specify types for cases where it is not clear from the parquet file itself.

@mapleFU and I have been discussing the need for something similar for deciding what Array type to use when reading strings from Parquet files on #5530 -- see #5530 (comment))

If we had such an API then people using spark created parquet files could specify that the timestamp column should always be UTC (as suggested by @tustvold ) without having to add an explicit cast afterwards

Maybe we can add some way to the parquet arrow reader to override its choice of data type for certain columns to allow users to specify types for cases where it is not clear from the parquet file itself.

@mapleFU and I have been discussing the need for something similar for deciding what Array type to use when reading strings from Parquet files on #5530 -- see #5530 (comment))

If we had such an API then people using spark created parquet files could specify that the timestamp column should always be UTC (as suggested by @tustvold ) without having to add an explicit cast afterwards

I think it's a solution adding the option in the parquet reader, it can help us to resolve some issues.

But many issues can't be resolve smoothly.

As describe in the comment: apache/datafusion#9981 (comment)
The same timestamp column maybe used by different timezone user, they may want to do compute on the same timestamp with different timezone. How to do that? @tustvold Do we need to add or wrap the cast expr explicitly to the target timestamp column?

Do we need to add or wrap the cast expr explicitly to the target timestamp column?

Yes arrow does not have a notion of "local" timezone, users need to be explicit about the timezone they wish to use for a particular array. Theoretically a DF frontend could add such a notion, but I would strongly recommend against it as it is leads to all sorts of peculiar bugs.

The following would be an example DataFusion SQL query that is explicit about timezones

SELECT arrow_cast(time_column, 'Timestamp(Nanosecond, Some("+07:00"))';

Do we need to add or wrap the cast expr explicitly to the target timestamp column?

Yes arrow does not have a notion of "local" timezone, users need to be explicit about the timezone they wish to use for a particular array. Theoretically a DF frontend could add such a notion, but I would strongly recommend against it as it is leads to all sorts of peculiar bugs.

The following would be an example DataFusion SQL query that is explicit about timezones

SELECT arrow_cast(time_column, 'Timestamp(Nanosecond, Some("+07:00"))';

Thanks for your feedback for

Do we need to add or wrap the cast expr explicitly to the target timestamp column?
I also know that adding explicit cast expr is not a good way.

Maybe the suggestion from @alamb is a good solution to resolve this after thinking about it again.
We can specify the timezone which is defined from parquet reader options when read the timestamp column.
what do you think about this method?

Filed #5657