Convert Time Zone

[DGFCPEuro]

Hello, I am working with time stamps that are UTC, but not defined as such. Sigma seems to be assuming that they are local time stamps, so I am unable to use Converttimezone. Is there a way to run a 3-argument function instead of a 2-argument function so that I can define the column as UTC and then convert it to my desired time zones?

[Jack_M]

Hi Dan, welcome to Sigma community!

From this documentation from snowflake; Convert Time Zone

We can try this…

SELECT CURRENT_TIMESTAMP() AS now_in_la,
CONVERT_TIMEZONE (‘America/Los_Angeles’, ‘America/New_York’, CURRENT_TIMESTAMP()) AS now_in_nyc,
CONVERT_TIMEZONE (‘America/Los_Angeles’, ‘Europe/Paris’, CURRENT_TIMESTAMP()) AS now_in_paris,
CONVERT_TIMEZONE (‘America/Los_Angeles’, ‘Asia/Tokyo’, CURRENT_TIMESTAMP()) AS now_in_tokyo

And the output…

Let us know if this is what you’re expecting Dan.

[DGFCPEuro]

Hello, thanks for the reply! This works well for me if I query using a custom SQL, but I would like to accomplish this by converting an existing column within the live data feed I am working on.

[Jack_M]

Hello Dan,

We do have a ConvertTimezone() function;

ConvertTimezone

Does this work for your use case?

[DGFCPEuro]

This function is what I want to use, but it does not work. It makes an assumption that the time is already in my local time zone due to admin settings, and when I use it to convert to America/New York it makes no change.

The data is UTC, but not defined as such and this feature is assuming that it is America/New York

[nathan]

Hi Dan,

Sigma engineering recommends doing the timestamp conversion in the warehouse. There is a currently unscheduled feature request to extend Sigma’s ConvertTimezone() to support a three argument version, which includes the source timezone. If you would like your organization to be attached to that request, please open a ticket via https://help.sigmacomputing.com/ (I can’t look up your email here in Sigma Community).

One potential workaround is to use the existing CurrentTimezone() function to modulate custom time conversion logic, but I’ll concede that handling things like DST would make this complicated to get exactly right.

Hope this helps,
Nathan

[DGFCPEuro]

Thank you, Nathan, you and Jack have provided perfectly usable solutions for now! I will have our team put in a ticket for the future feature release.