Timestamp Columns and Timezones
Anchor |
---|
| SnowflakeTimestamps |
---|
| SnowflakeTimestamps |
---|
|
Snowflake supports saving timestamp columns with different variations as described here.
For example, if you are using the TIMESTAMP_NTZ variation, records saved into a timestamp column will be saved with the timestamp value as is and when queried with no timezone offset possible. Thus when records are queried, Snowflake will always return the exact same timestamp value regardless of the timezone you specify in your session using the TIMEZONE parameter.
That is, if you save a value as 2021-01-01 00:14:30 into a column defined as TIMESTAMP_NTZ(9), Snowflake will always return the value as 2021-01-01 00:14:30 regardless of the timezone your current session is in.
To mimic how data is saved in ServiceNow, by default the meshlet uses the TIMEZONE session parameter in its JDBC connection string to connect when connecting to Snowflake, specifying that timestamp values the meshlet saves will be in UTC (i.e. TIMEZONE=UTC). In addition to using this parameter, glide_date_time fields from ServiceNow (which is the default field type for datetimes datetime fields in ServiceNow) are mapped to the TIMESTAMP_LTZ(9) column column type in the meshlet's databaseConfig.json configuration file.
This combination ensures that ServiceNow timestamp fields the meshlet pushes into Snowflake are saved in UTC time and then allows for querying of records in Snowflake to be returned in local timezone based on the TIMEZONE parameter a user sets in their session.
That is, if you save a value as 2021-01-01 00:14:30 and specified that we were saving it in UTC time (TIMEZONE=UTC) into a column defined as TIMESTAMP_LTZ(9), Snowflake will return the value as 2021-01-01 00:06:30 -0800 if your current session is in the America/Los_Angeles (Pacific) timezone.
Expand |
---|
title | Change meshlet to not save in UTC |
---|
|
If you prefer the meshlet to not save records in UTC time, set the following configuration in the application-dev.yml meshlet configuration file: NOTE: This is an advanced configuration and it is recommended you review how Snowflake handles timestamp variations before making these changes.
Code Block |
---|
perspectium:
snowflake:
useUTCTimeZone: false |
By default or if the configuration is not specified, useUTCTimeZone is set to true so records are saved in UTC time. |
By When setting this to false, the meshlet will save in the session timezone as defined in the session/account for the Snowflake account credentials entered in application-dev.yml for connecting to Snowflake. After setting this configuration to false, you can then use the connectionUrl configuration in application-dev.yml to use a different timezone if desired i.e. Code Block |
---|
perspectium:
snowflake:
connectionUrl: jdbc:snowflake://<account_identifier>.snowflakecomputing.com/?TIMEZONE=America/Los_Angeles |
In this case, records will be saved into the database with the America/Los_Angeles (Pacific) timezone.
|
In addition, you can modify the glide_date_time configuration in the databaseConfig.json file if you want timestamp fields to be saved in a different TIMESTAMP variation and precision.
NOTE: This is an advanced configuration and it is recommended you review how Snowflake handles timestamp variations before making these changes.
For example, if you wanted to change all ServiceNow datetime fields to be saved without timezone considerations with a precision of 0 (only seconds), you can update the glide_date_time configuration in databaseConfig.json as follows:
Code Block |
---|
"types":{
"glide_date_time":"TIMESTAMP_NTZ(0)",
} |
↑ Go to top of page