Snowflake temporal extract operators like hour-of-day do not respect report timezone

[camsaul]

If the report timezone is -10:00 we would expect get-hour for a timestamptz like "2011-12-31T15:30:54-10:00" to return 15, however it currently returns 1 (value for the timestamp when it is normalized to UTC)

Try enabling metabase.query-processor-test.timezones-test/general-timezone-support-test for Snowflake to see more examples of things that are broken

(deftest x-test
  (mt/test-driver :snowflake
    (mt/dataset metabase.query-processor-test.timezones-test/all-dates-leap-year
      (mt/with-temporary-setting-values [report-timezone "Pacific/Honolulu"]
        (let [query (mt/mbql-query alldates
                      {:expressions {"hour-of-day" [:temporal-extract $dt :hour-of-day]}
                       :fields      [$dt
                                     [:expression "hour-of-day"]]
                       :order-by [[:asc $id]]
                       :limit 5})]
          (mt/with-native-query-testing-context query
            (is (= [["2011-12-31T15:30:54-10:00" 15]
                    ["2012-01-01T15:30:54-10:00" 15]
                    ["2012-01-02T15:30:54-10:00" 15]
                    ["2012-01-03T15:30:54-10:00" 15]
                    ["2012-01-04T15:30:54-10:00" 15]]
                   (mt/rows (qp/process-query query))))))))))

Native Query =
SELECT
  "source"."dt" AS "dt",
  "source"."hour-of-day" AS "hour-of-day"
FROM
  (
    SELECT
      "PUBLIC"."alldates"."id" AS "id",
      "PUBLIC"."alldates"."dt" AS "dt",
      DATE_PART("hour", "PUBLIC"."alldates"."dt") AS "hour-of-day"
    FROM
      "2024_04_30_e15f5997_60ab_4c7d_8fde_164a001da04c_all-dates-leap-year"."PUBLIC"."alldates"
  ) AS "source"
ORDER BY
  "source"."id" ASC
LIMIT
  5

{:params nil}


expected: [["2011-12-31T15:30:54-10:00" 15]
           ["2012-01-01T15:30:54-10:00" 15]
           ["2012-01-02T15:30:54-10:00" 15]
           ["2012-01-03T15:30:54-10:00" 15]
           ["2012-01-04T15:30:54-10:00" 15]]
  actual: [["2011-12-31T15:30:54-10:00" 1]
           ["2012-01-01T15:30:54-10:00" 1]
           ["2012-01-02T15:30:54-10:00" 1]
           ["2012-01-03T15:30:54-10:00" 1]
           ["2012-01-04T15:30:54-10:00" 1]]