Handling of timestamps with time zone

Driver Development
#1

When querying our Metabase driver, a query such as:

SELECT TIMESTAMP '2010-10-31 01:00 UTC' AT TIME ZONE 'America/Los_Angeles',
       TIMESTAMP '2010-10-31 01:00 UTC' AT TIME ZONE 'UTC'

returns twice the same datetime, adjusted to the local timezone. One of our users claims it's a bug as Postgresql gives a different result. But is it a bug? The two columns represent the same timestamp, albeit in two different time zones. When receiving a timestamp with a time zone, what is read-column-thunk for Types/TIMESTAMP_WITH_TIMEZONE supposed to return?

#2

Hi @lpoulain
What is "our Metabase driver" ? Does your driver even have full timezone handling?
And which versions are you testing against? And what is the Report Timezone?

#3

I'm talking about the Starburst Metabase driver, which does support the Types/TIMESTAMP_WITH_TIMEZONE type. But whether it returns a ZonedDateTime object or an Instant the result is the same.

So I'm wondering what is read-column-thunk supposed to return, and whether the two columns in the above query should return two different values in Metabase or the same value.

As far as the version, it's a locally built version which should be fairly recent.

#4

@lpoulain What happens if you run the exact same query directly on the database? And how about via something like DBeaver with the plain JDBC driver?
Postgres will return different results.

If you are not override the function, then it's https://github.com/metabase/metabase/blob/7b767677df2ef9ec87cc6619908d7b1b6d1dcc86/src/metabase/driver/sql_jdbc/execute.clj#L116-L126

As a partner driver, you can ask this question directly to your Metabase contact.

#5

@flamber when I run the same query in Starburst I get 2010-10-30 18:00:00 America/Los_Angeles and 2010-10-31 01:00:00 UTC.

How do I know who is my Metabase contact? The colleague who worked on the driver only had someone explaining how to submit the driver but no technical assistance.

#6

@lpoulain It's should be the same person who will facilitate technical contact.

#7

Looks like Starburst behavior makes sense and it is not a bug.

The ... AT TIMEZONE suffix is irrelevant since we will convert the timezone to the reporting timezone afterward.

My reporting timezone is UTC, and it is doing the right conversion. I'll look at other cases and verify if there are odd cases.
image