Snowflake JDBC driver incorrectly converts JDBC dates with a timezone

[senior]

When dealing with the older (pre JDK 8) dates, the dates are always converted to the JVM timezone. The can present a problem when using Metabase's report-timezone feature, which will set the database sessions timezone to the user configured value. When filtering based on a date and the report-timezone is specified, that date needs to also be in the report timezone. Since the JVM always converts the date to the JVM's timezone (and there is no tracking of an "original" timezone) the JDBC library allows the timezone to be included as a separate parameter.

This doesn't appear to work correctly with Snowflake as when that parameter is included, it looks to be double converting the values. Creating this to track the issue and for linking to the tests with the incorrect values so it can be fixed after Snowflake support is merged.

⬇️ Please click the 👍 reaction instead of leaving a +1 or update? comment

[adrianisk]

Hey @senior, would this bug also affect timestamps with timezones being displayed incorrectly in results? The below query displays correctly when running against Redshift, but not for Snowflake (expect the LA timestamp to match the to_char version):

[viblo]

Not sure if this is exactly the same problem, but I also have issues with snowflake and time zones. First posted here: https://discourse.metabase.com/t/local-timezone-when-data-is-in-utc-implicitly-using-snowflake/9961
(If this is a separate issue I can move it to new issue).

So, in snowflake I have this table, where all the dates are in UTC:

CREATE OR REPLACE TABLE ts_test(ts TIMESTAMP_NTZ);
INSERT INTO ts_test values('2017-04-30 20:00:00');
INSERT INTO ts_test values('2017-04-30 22:00:00');
INSERT INTO ts_test values('2017-04-30 23:00:00');
INSERT INTO ts_test values('2017-05-01 00:00:00');
INSERT INTO ts_test values('2017-05-01 02:00:00');
INSERT INTO ts_test values('2017-05-01 03:00:00');

If I put reporting time zone to UTC everything work as expected. But I want to use Europe/Berlin as my timezone.

Some examples with metabase report time zone set to Europe/Berlin:

Visualizing the values in the table:

Queries to Snowflake:

ALTER SESSION SET TIMEZONE = 'Europe/Berlin';

SELECT "TECH_DB"."PUBLIC"."TS_TEST"."TS" AS "TS"
FROM "TECH_DB"."PUBLIC"."TS_TEST"
LIMIT 1048576

Result in Metabase:

May 1, 2017, 3:00
May 1, 2017, 2:00
May 1, 2017, 24:00
April 30, 2017, 23:00
April 30, 2017, 22:00
April 30, 2017, 20:00

Summarize row count by day

Snowflake queries:

ALTER SESSION SET TIMEZONE = 'Europe/Berlin';

SELECT date_trunc("day", CAST("TECH_DB"."PUBLIC"."TS_TEST"."TS" AS timestamp)) AS "TS", count(*) AS "count"
FROM "TECH_DB"."PUBLIC"."TS_TEST"
GROUP BY date_trunc("day", CAST("TECH_DB"."PUBLIC"."TS_TEST"."TS" AS timestamp))
ORDER BY date_trunc("day", CAST("TECH_DB"."PUBLIC"."TS_TEST"."TS" AS timestamp)) ASC

Result in metabase:

April 30, 2017 3
May 1, 2017 3

Summarize row count by day, filter on date (Ts is before May 1, 2017)

Snowflake queries:

ALTER SESSION SET TIMEZONE = 'Europe/Berlin';

SELECT date_trunc("day", CAST("TECH_DB"."PUBLIC"."TS_TEST"."TS" AS timestamp)) AS "TS", count(*) AS "count"
FROM "TECH_DB"."PUBLIC"."TS_TEST"
WHERE "TECH_DB"."PUBLIC"."TS_TEST"."TS" < timestamp '2017-05-01 00:00 +02:00'
GROUP BY date_trunc("day", CAST("TECH_DB"."PUBLIC"."TS_TEST"."TS" AS timestamp))
ORDER BY date_trunc("day", CAST("TECH_DB"."PUBLIC"."TS_TEST"."TS" AS timestamp)) ASC

Result in metabase:

April 30, 2017 3

[HugoManoMano]

Hey friends,
I'm writing a comment because of the lack of news on this issue. We just added a snowflake connection to Metabase (which we upgraded to the latest current release), and we're facing the exact same issue.
@viblo @senior are you still having that on your side ?

[viblo]

@HugoManoMano No changes since I wrote the comment. For now Ive put reporting time zone to UTC, but it would be great to make it work properly with all time zones.

[spencerschack]

We had a similar issue and fixed it by ensuring that our JAVA_TIMEZONE environment variable was the same as our reporting timezone setting. This is mentioned in the docs about timezones. Not sure if this helps, but thought I would pass it on.

[HugoManoMano]

Thanks for your answers ! Setting the JAVA_TIMEZONE as the same as our reporting timezone seems to have solved the problem when set to UTC (we had Europe/Paris before on both side but this didn't work). I guess we'll run it that way for the moment ; thanks @spencerschack

[eozturkTF]

Hi everyone
I'm encoutering the same issue but can't fix it.

Can you please confirm that the right syntax in the DB connection page is this : user.timezone=UTC ?
And then i also set the same timezone in the Settings > Localization page, but this only solve spartially.

I used to have 24:00:00 displayed as time part for DATE columns.
Now they do display as 00:00:00 but the day ui still the previous one :(

[flamber]

@eozturkTF You need to start Metabase with the Java parameter ...-Duser.timezone=UTC...
On Docker, that would be ...-e JAVA_OPTS="-Duser.timezone=UTC"...

---

To help figuring out solutions, please provide "Diagnostic Info" from Admin > Troubleshooting, and run the following query and provide a screenshot of the output:

select
current_timestamp(), current_timestamp()::text,
convert_timezone('UTC', current_timestamp()) utc, convert_timezone('UTC', current_timestamp())::text utctext,
convert_timezone('America/New_York', current_timestamp()) ny, convert_timezone('America/New_York', current_timestamp())::text nytext,
convert_timezone('Asia/Bangkok', current_timestamp()) bk, convert_timezone('Asia/Bangkok', current_timestamp())::text bktext