We are proud to share our inclusion in the 2024 Gartner Magic Quadrant for Data Integration Tools. We believe this recognition reflects the differentiated business outcomes CData delivers to our customers.
Get the Report →
Managing Snowflake TIMESTAMP discrepancies with CData Drivers
In databases, the TIMESTAMP type is a data type used to represent date and time. Accurately recording specific times or dates is generally important, but even moreso when you want to manage incremental operations based on the time and date of the data.
This article focuses on the timestamp types in Snowflake. We describe what variations of timestamp exist and how you can use the specific type from CData Drivers for Snowflake.
3 Types of Snowflake Timestamp
Snowflake has three variations of timestamp types, each with different uses and characteristics.
TIMESTAMP_LTZ (Timestamp with Local Time Zone)
Stored internally in UTC time in Snowflake. The date and time are retrieved based on the session time zone when the query is executed. It maintains consistency of data inserted from different time zones in global applications.
TIMESTAMP_NTZ (Timestamp without Time Zone)
Stored internally as wall-clock time without time zone information in Snowflake and retrieved exactly as they were initially stored. It is used when you want to handle dates and times that are not dependent on time zones.
TIMESTAMP_TZ (Timestamp with Time Zone)
Stored internally in UTC time with the associated time zone offset in Snowflake and retrieved with the unique time zone offset for each record. It is used to store and display date and time data related to specific time zones.
Challenges when Registering Date and Time
When you execute INSERT or UPDATE on Snowflake to register or update date and time data, the date and time conversion is automatically performed based on the session time zone, which can lead to unintended results. To prevent this, you can set the session time zone using the ALTER SESSION SET TIMEZONE clause or use the TIMESTAMP_NTZ column, which does not consider time zones.
The ability to configure your time zone based on your session is useful when working within the Snowflake interface. On the other hand, operations using a CData JDBC Driver for Snowflake rely on the time zone of the JVM execution environment, making the date and time conversion mechanism more complex. Therefore, problems often arise where the desired date and time cannot be registered as expected through the driver. This article introduces methods to register the desired date and time data and retrieve the same value as the registered date and time.
In the following examples, the default time zone of the JVM execution environment is "Asia/Tokyo" (UTC+9:00), and the session time zone of Snowflake is "America/Los_Angeles" (UTC-5:00).
Example TIMESTAMP Problems
Based on the above environment (JVM time zone is "Asia/Tokyo" while the session time zone is "America/Los_Angeles")let's execute the following Java code to register "2023-6-15 21:00:00" in the TIMESTAMPNTZ column COL_TIMESTAMP_LTZ:
String query = "INSERT INTO TEST(COL_TIMESTAMP_NTZ) VALUES(?)";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setTimestamp(1, Timestamp.valueOf("2023-6-15 21:00:00"));
pstmt.executeUpdate();After executing the above code, retrieving the value of COL_TIMESTAMP_NTZ in Snowflake returns "2023-06-15 07:00:00.000".
This can be inferred as the result of the following process:
- The driver converts the date and time "2023-6-15 21:00:00" in the local time zone "Asia/Tokyo" (UTC+9:00) to UTC "2023-06-15 12:00:00" and sends it to Snowflake.
- Snowflake converts the received value "2023-06-15 12:00:00" to the session time zone "America/Los_Angeles" (UTC-5:00) and registers "2023-06-15 07:00:00".
When you request the date and time of COL_TIMESTAMP_NTZ with the driver, Snowflake returns "2023-06-15 07:00:00" which is same value returned directly from the Snowflake interface. This results in a conflicting experience, where the data registered as "2023-06-15 21:00:00" through the driver is retrieved as "2023-06-15 07:00:00" through the same driver. This result is accurate based on the specifications of Snowflake and the JDBC driver, but it is inconvenient when using the driver in programs or tools. However, there are ways to avoid this inconsistency by configuring the driver as introduced below.
1. Register Date and Time as UTC
Some data integration tools treat all date and time data as UTC. When using the driver with such tools, it is convenient to register and retrieve input values as UTC. One way to register the sent UTC date and time data without converting it to the session time zone in Snowflake is to use the CLIENT_TIMESTAMP_TYPE_MAPPING session parameter.
The CLIENT_TIMESTAMP_TYPE_MAPPING session parameter converts java.sql.Timestamp data bound to a statement to the specified Snowflake TIMESTAMP type. By setting this session parameter to the type "TIMESTAMPNTZ" that does not consider time zones, you can register the sent date and time as is. To set the session parameter in the CData JDBC Driver for Snowflake, add it to the connection properties as follows:
Properties properties = new Properties();
~ Other property settings ~
properties.put("CLIENT_TIMESTAMP_TYPE_MAPPING", "TIMESTAMP_NTZ");
String connectStr = "jdbc:snowflake://test.us-east-1.snowflakecomputing.com";
Connection conn = DriverManager.getConnection(connectStr, properties);You can also achieve the same result by setting the TIMEZONE session parameter to "UTC".
2. Register Date and Time as Local Time Zone
To register the input date and time as is without converting it to UTC or the session time zone, you can use the method of binding as a string or specifying the TIMEZONE session parameter.
Binding as a String
By setting the string with the setString() method when binding to the statement as follows, you can register the date and time as is. This is the simplest method, but it cannot be applied to tools that cannot change the data type of the binding.
pstmt.setString(2, "2023-6-15 21:00:00");
Specifying the TIMEZONE Session Parameter
By specifying the local time zone in the TIMEZONE session parameter introduced in "How to Register Date and Time as UTC", you can register the date and time as the time zone. For example, if you execute in an environment where the local time zone is Asia/Tokyo, set the TIMEZONE session parameter to "Asia/Tokyo" as follows:
Properties properties = new Properties();
~ Other property settings ~
properties.put("TIMEZONE", "Asia/Tokyo");
String connectStr = "jdbc:snowflake://test.us-east-1.snowflakecomputing.com";
Connection conn = DriverManager.getConnection(connectStr, properties);With this setting, registering the date and time "2023-6-15 21:00:00" works as follows:
- The driver converts the date and time "2023-6-15 21:00:00" in the local time zone "Asia/Tokyo" (UTC+9:00) to UTC "2023-06-15 12:00:00" and sends it to Snowflake.
- Snowflake converts the received value "2023-06-15 12:00:00" to the session time zone "Asia/Tokyo" (UTC+9:00) and registers "2023-06-15 21:00:00".
The TIMEZONE session parameter is also effective for the TIMESTAMPTZ type. When you set "Asia/Tokyo" in the parameter and register the date and time in the TIMESTAMPTZ column, "2023-06-15 21:00:00 +0900" is registered as follows.
3. Retrieve the Same Value as the Registered Date and Time Data with the Driver
If you don't care about the value stored in Snowflake and just want the "same date and time value registered and retrieved by the driver," it is recommended to use the TIMESTAMP_LTZ type. This method does not use session parameters.
The TIMESTAMP_LTZ type holds date and time with a time zone. When you retrieve the value of this column with the driver, the value converted to the local time zone is returned. For example, if you register "2023-6-15 21:00:00" in the TIMESTAMP_LTZ column COL_TIMESTAMP_LTZ, it is converted to UTC (-9:00) and then to the session time zone (-5:00), and "2023-06-15 07:00:00.000 -0500" is registered.
String query = "INSERT INTO TEST(COL_TIMESTAMP_LTZ) VALUES(?)";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setTimestamp(1, Timestamp.valueOf("2023-6-15 21:00:00"));
pstmt.executeUpdate();When you retrieve the date and time of this column with the driver as follows, the reverse conversion is performed, and "2023-6-15 21:00:00" is returned.
String query = "SELECT COL_TIMESTAMP_LTZ FROM TEST";
Statement stmt = conn.createStatement();
stmt.executeQuery(query);
ResultSet rs = stmt.getResultSet();
while(rs.next()){
System.out.println(rs.getString(1));
// rs.getTimestamp(1) also returns the same value
}Free Trial & More Information
Thanks to CData connectivity solutions, working with your Snowflake timestamp data has never been easier. Try CData drivers and connectors or replicating your business data to Snowflake using CData Sync. Download a free, 30-day trial of any of our solutions (or start a free trial of CData Sync).
