在Snowflake JOIN条件下强制UTC时间

Question

我有一个设置，我们有

1. 具有TIMESTAMP_LTZ(consumption_date(类型列的登录表。包括+02:00的时区
2. 从平台表读取的视图(landing_view(
3. 从具有TIMESTAMP_NTZ(SOURCE_TIMESTAMP(类型字段的表中读取的视图(raw_data(，但值本身以UTC时间为单位

我必须使用consumption_date和SOURCE_TIMESTAMP将landing_view中的数据连接到raw_data中的数据。

SELECT l.ID, l.consumption_date, l.RUN_TIME, r.DISPLAY_NAME, r.source_timestamp, r.value_as_double
FROM "raw_data" r
JOIN "landing_view" l
ON r.SOURCE_TIMESTAMP >= DATEADD(second,120, convert_timezone('UTC',l.consumption_date))
and r.SOURCE_TIMESTAMP < DATEADD(second,1000, convert_timezone('UTC',l.consumption_date))

我的问题是convert_timezone命令似乎根本不影响联接子句，而是使用LTZ类型中包含的本地时间(+02:00(进行联接。

如果我使用convert_timezone是一个选择，如果工作得很好，但对于JOIN来说就不行了。

有没有办法告诉雪花在加入中使用UTC？

Answer 1

这将取决于您的TIMEZONE设置。请参阅下面的示例。

如果时区为UTC:

alter session set TIMEZONE = 'UTC';
select 
-- 2AM UTC
'2021-01-02 02:00:00'::timestamp_ntz as SOURCE_TIMESTAMP,
-- 1AM UTC / 12PM Australia/Melbourne time / 1 hour before SOURCE_TIMESTAMP
'2021-01-02 12:00:00 +1100'::timestamp_ltz as CONSUMPTION_DATE,
-- so add one hour to CONSUMPTION_DATE should equal to SOURCE_TIMESTAMP
SOURCE_TIMESTAMP = DATEADD(hour, 1, convert_timezone('UTC', CONSUMPTION_DATE)) as is_equal
;
+-------------------------------+-------------------------------+----------+
| SOURCE_TIMESTAMP              | CONSUMPTION_DATE              | IS_EQUAL |
|-------------------------------+-------------------------------+----------|
| 2021-01-02 02:00:00.000000000 | 2021-01-02 01:00:00.000 +0000 | True     |
+-------------------------------+-------------------------------+----------+

然而，如果您将TIMEZONE设置更改为另一个时区，结果将有所不同：

alter session set TIMEZONE = 'Australia/Melbourne';
select 
-- 2AM UTC
'2021-01-02 02:00:00'::timestamp_ntz as SOURCE_TIMESTAMP,
-- 1AM UTC / 12PM Australia/Melbourne time / 1 hour before SOURCE_TIMESTAMP
'2021-01-02 12:00:00 +1100'::timestamp_ltz as CONSUMPTION_DATE,
-- so add one hour to CONSUMPTION_DATE should equal to SOURCE_TIMESTAMP
SOURCE_TIMESTAMP = DATEADD(hour, 1, convert_timezone('UTC', CONSUMPTION_DATE)) as is_equal
;
+-------------------------------+-------------------------------+----------+
| SOURCE_TIMESTAMP              | CONSUMPTION_DATE              | IS_EQUAL |
|-------------------------------+-------------------------------+----------|
| 2021-01-02 02:00:00.000000000 | 2021-01-02 12:00:00.000 +1100 | False    |
+-------------------------------+-------------------------------+----------+

由于SOURCE_TIMESTAMP存储UTC值，因此应更改TIMEZONE设置以匹配它。

顺便说一句，在DATEADD中具有CONVERT_TIMEZONE是多余的，因为它只添加了额外的操作，但没有任何效果。参见以下示例：

select 
-- 1AM UTC / 9AM Australia/Perth time / 1 hour before SOURCE_TIMESTAMP
'2021-01-02 09:00:00 +0800'::timestamp_ltz as CONSUMPTION_DATE,
DATEADD(hour, 1, CONSUMPTION_DATE) as no_convert_tz,
DATEADD(hour, 1, convert_timezone('UTC', CONSUMPTION_DATE)) as convert_tz,
no_convert_tz = convert_tz
;
+-------------------------------+-------------------------------+-------------------------------+----------------------------+
| CONSUMPTION_DATE              | NO_CONVERT_TZ                 | CONVERT_TZ                    | NO_CONVERT_TZ = CONVERT_TZ |
|-------------------------------+-------------------------------+-------------------------------+----------------------------|
| 2021-01-02 12:00:00.000 +1100 | 2021-01-02 13:00:00.000 +1100 | 2021-01-02 02:00:00.000 +0000 | True                       |
+-------------------------------+-------------------------------+-------------------------------+----------------------------+

您可以看到最后一列返回True。