fix: wrong timezone processing in snowpipe

dbt-labs / dbt-external-tables

dbt macros to stage external sources

https://hub.getdbt.com/dbt-labs/dbt_external_tables/latest/

Apache License 2.0

294 stars 119 forks source link

fix: wrong timezone processing in snowpipe #186

Closed taskooh closed 6 months ago

taskooh commented 1 year ago

Description & motivation

Now it is using current_timestamp::timestamp function to get current timestamp, but this is wrong because type of current_timestamp is TIMESTAMP_LTZ(local time zone) and when converted to timestamp_ntz(no time zone) by ::timestamp timezone is forcibly removed and showing wrong timestamp. We should use sysdate() instead. https://docs.snowflake.com/en/sql-reference/functions/sysdate.html

How i checked:

Look at dbt log and generated raw sql. Run that sql with this update manually on snowflake, and check that timezone issue is fixed.

Checklist

[x] I have verified that these changes work locally
[x] I have updated the README.md (if applicable)
[x] I have added an integration test for my fix/feature (if applicable)

github-actions[bot] commented 1 year ago

This PR has been marked as Stale because it has been open with no activity as of late. If you would like the PR to remain open, please comment on the PR or else it will be closed in 7 days.

yingyingqiqi commented 1 year ago

The snowflake documentation does not recommend using SYSDATE/CURRENT_TIMESTAMP in Snowpipe. i am using METADATA$START_SCAN_TIME

https://docs.snowflake.com/en/user-guide/data-load-snowpipe-ts#load-times-inserted-using-current-timestamp-earlier-than-load-time-values-in-copy-history-view

github-actions[bot] commented 6 months ago

This PR has been marked as Stale because it has been open with no activity as of late. If you would like the PR to remain open, please comment on the PR or else it will be closed in 7 days.

github-actions[bot] commented 6 months ago

Although we are closing this PR as stale, it can still be reopened to continue development. Just add a comment to notify the maintainers.

AndyHuang1991 commented 5 months ago

We're also encounter this issue, we then modify system timezone to 'UTC' to address... Agree on @yingyingqiqi 's reply https://github.com/dbt-labs/dbt-external-tables/pull/186#issuecomment-1680289740 to get more accurate timestamp, but as per snowflake document METADATA$START_SCAN_TIME data type is also TIMESTAMP_LTZ. convert to TIMESTAMP (TIMESTAMP_NTZ) would still cause problem like origin post.