In the realm of global data management, dealing with time zones is inevitable. Salesforce, for instance, stores all DateTime fields in UTC (Coordinated Universal Time) so it is time zone independent. This is incredibly useful to ensure data that is deployed to users reflects their local timezone when events occur. When you extract data into a data warehouse like Snowflake, you typically do not convert the timezone.
When you run analysis and aggregate data by a DateTime formatted field, you could experience small discrepancies from summary data in Salesforce reports. This is due to the difference between UTC and your local time zone. CONVERT_TIMEZONE function, allows you to adjust DateTime values to the appropriate local time zone, thus ensuring consistency and accuracy in your reports and analyses.
Example:
Suppose your operations are based in California, and you need to convert a UTC CreateDate to the local time in Los Angeles. You can achieve this with the following Snowflake SQL command:
SELECt *, CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', "CreateDate") AS "PST Created Date" FROM SFDC.STAGE.LEAD TZ Create Date"This function not only caters to specific needs like the above but also supports a broad range of time zones listed in the IANA Time Zone Database, providing flexibility for global operations.
Most databases, Snowflake included, provide complete control over timezone data and how you store and query data. In our lesson, we focused solely on setting time zones while querying data. If you want to learn more about storing data in a time-zone-dependent format (https://docs.snowflake.com/en/sql-reference/data-types-datetime), Snowflake provides additional documentation. Unfortunately, this goes beyond the scope of our training.