Different result for date_trunc() for TABLE and VIEW with Snowflake

Hello there, I have different results depending on wether my data is in a Table or a View in Snowflake.

Our Snowflake Timezone is in UTC, and Metabase Localization is in America/Los_Angeles (running on Elastic Beanstalk in Oregon).

You can reproduce it like this:

  • Create a Table and a View with same timestamp in Snowflake
CREATE OR REPLACE TABLE TEST_TABLE (DATE) AS (SELECT to_timestamp_tz(1673630797))     
CREATE OR REPLACE VIEW TEST_VIEW (DATE) AS (SELECT to_timestamp_tz(1673630797))  
  • In Metabase the following queries give different results
  SELECT date_trunc('week', date) FROM TEST_TABLE
 -> January 8, 2023, 4:00 PM 
  SELECT date_trunc('week', date) FROM TEST_VIEW
 -> January 9, 2023, 12:00 AM

Diagnostic Info:

{
  "browser-info": {
    "language": "en-GB",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.0.0 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.17+8",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.17",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.17+8",
    "os.name": "Linux",
    "os.version": "4.14.301-224.520.amzn2.x86_64",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "googleanalytics",
      "snowflake"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "10.21"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.5.0"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2022-12-07",
      "tag": "v0.45.1",
      "branch": "release-x.45.x",
      "hash": "019d31c"
    },
    "settings": {
      "report-timezone": "America/Los_Angeles"
    }
  }
}

This cause some issues to create our dashboard as some data comes from Views and some data from Tables.
Thank you!

Hi, we have several issues with timezones in Snowflake (Issues · metabase/metabase · GitHub), can you let us know which data type is the field in the table and in the view?

thanks

Hello Luiggi, for both (Table and View) the data type of the field is TIMESTAMP_TZ(0)

One use case which is very annoying for us is:

  • I have a table with raw data, not well typed, for example timestamp as a number:
CREATE OR REPLACE TABLE TEST_TABLE (DATE) AS (SELECT 1673630797);
-> Data type: NUMBER(10,0)
  • I create a view from this table to cast data in a better format:
CREATE OR REPLACE VIEW TEST_VIEW AS SELECT DATE::TIMESTAMP_TZ as DATE FROM TEST_TABLE;
-> Data type: TIMESTAMP_TZ(0)
  • Then Analytics team uses the data from the View to create some Tables with complex SQL queries (need few minutes to create the Table) to have data available for the Dashboards:
CREATE OR REPLACE TABLE TEST_ANALYTICS_TABLE AS SELECT * FROM TEST_VIEW;
-> Data type: TIMESTAMP_TZ(0)

So from Snowflake point of view, everything seems ok. But in Metabase, View and Table does not give the same result:

SELECT date_trunc('week', date) FROM TEST_VIEW;
-> January 9, 2023, 12:00 AM
SELECT date_trunc('week', date) FROM TEST_ANALYTICS_TABLE;
-> January 8, 2023, 4:00 PM

I did not found a better solution than creating additional Views on top of the Analytics team Tables to cast again the data. And here the thing is, casting the data from the TEST_ANALYTICS_TABLE as TIMESTAMP_TZ does not give the expected result. I have to cast it as TIMESTAMP_NTZ or TIMESTAMP_LTZ.

CREATE OR REPLACE VIEW TEST_ANALYTICS_VIEW AS SELECT DATE::TIMESTAMP_LTZ as DATE FROM TEST_ANALYTICS_TABLE;

SELECT date_trunc('week', date) FROM TEST_ANALYTICS_VIEW;
-> January 9, 2023, 12:00 AM

Our goal is to be able to display all our metrics in PT Timezone.
If you have a better solution I'll be happy to hear it :slight_smile:

Thank you!

If it can help I found this:

  • Create a TABLE with date TIMESTAMP_TZ
CREATE OR REPLACE TABLE TEST_TABLE (DATE) AS (SELECT to_timestamp_tz(1673630797));
DATE format: TIMESTAMP_TZ(0)
  • Create VIEW from previous TABLE
CREATE OR REPLACE VIEW TEST_VIEW AS (SELECT DATE as DATE FROM TEST_TABLE);
DATE format: TIMESTAMP_TZ(0)

Metabase will give you unexpected result:

SELECT date_trunc('week', date) FROM TEST_TABLE;
-> January 8, 2023, 4:00 PM
SELECT date_trunc('week', date) FROM TEST_VIEW;
-> January 8, 2023, 4:00 PM

But if you update VIEW like this (convert to TIMESTAMP_TZ date casted in TIMESTAMP_LTZ):

CREATE OR REPLACE VIEW TEST_VIEW AS (SELECT to_timestamp_tz(DATE::TIMESTAMP_LTZ) as DATE FROM TEST_TABLE);
DATE format: TIMESTAMP_TZ(9)

Metabase will give you the expected result:

SELECT date_trunc('week', date) FROM TEST_VIEW;
-> January 9, 2023, 12:00 AM