Snowflake JDBC TIME datatype value converted to UTC

Question

We are using JDBC driver to connect to Snowflake and perform inserts. While working with TIME datatype, we provide time value as 10:10:10 with setTime in insert and when retrieved with getTime, we get 02:10:10.

The documentation says - TIME internally stores “wallclock” time, and all operations on TIME values are performed without taking any time zone into consideration.

Why this conversion? How to make sure we get back what we inserted?

Sample program -

try (Connection con = getConnection()) {
    try (PreparedStatement ps = con.prepareStatement("insert into Test_Time values (?)"))  {
        ps.setTime('10:10:10');
        ps.executeUpdate();
    }

    try (Statement statement = con.createStatement()) {
        try (ResultSet resultSet = statement.executeQuery("select * from Test_Time ")) {
            while (resultSet.next()) {
                System.out.println(resultSet.getTime(1));
            }
        }
    }
}

We tried both -

ps.setTime(Time.valueOf('10:10:10'))
ps.setTime('10:10:10')

When similar is tried from WEB portal worksheet, we see the same inserted value 10:10:10. Web portal able to show the right desired result so wondering Is there anything basic missing in my case?

Thank you for help in advance.

Update - Done more testing around this - found there is no difference between "setTime(int idx, Time t)" and "setTime(int idx, Time t, Calendar c)". If you already know the timezone of your data then there is no way to supply custom timezone.

score 2 · Answer 1 · answered May 13 '20 at 09:38

The conversion is done by Java. If you use bind a string variable instead of Time variable, you will see that it works as expected:

CREATE TABLE test_time ( t TIME );

When we run the below Java block, it will insert "10:10:10" to the table:

try (Connection con = getConnection()) {
    try (PreparedStatement ps = con.prepareStatement("insert into Test_Time values (?)"))  {
        ps.setString(1, "10:10:10" );
        ps.executeUpdate();
    }

    try (Statement statement = con.createStatement()) {
        try (ResultSet resultSet = statement.executeQuery("select * from Test_Time ")) {
            while (resultSet.next()) {
                System.out.println(resultSet.getString(1));
                System.out.println(resultSet.getTime(1));
            }
        }
    }
}

You will see the following output:

10:10:10
02:10:10

The first one is reading the time as string, and the second was using Time object so it's converted to your timezone. If you query the table on web UI, you will see that it will return the expected value:

SELECT * FROM test_time;

+----------+
|    T     |
+----------+
| 10:10:10 |
+----------+

At least, it worked for me :)

score 1 · Accepted Answer · answered May 13 '20 at 09:47

From what I've seen searching around (eg. here) it seems like it comes down to the driver that you're using and not the database. I'm seeing different results to you too - in my script I'm inserting 10:10:10 and getting back 10:10:10 but when I look at the Snowflake table it says 09:10:10. This is because I live in London which is currently BST (GMT+1) so my driver is doing the conversion from my local timezone to GMT before inserting into Snowflake (when ps.setTime is run) and also when retrieving from the database (when results.getTime is run).

Example here in scala but basically the same code:

val ps: PreparedStatement = connection.get.prepareStatement("insert overwrite into test_db.public.test_table values (?)")
val insertedTime: Time = Time.valueOf("10:10:10")

println(s"Inserting ${insertedTime} into table")
ps.setTime(1, insertedTime)
ps.executeUpdate()

val results: ResultSet = connection.get.createStatement().executeQuery("select time_col from test_db.public.test_table")
while(results.next) {
  val returnedTime = results.getTime(1)
  println(s"Time returned: ${returnedTime}")
}

The above script prints out:

Inserting 10:10:10 into table
Time returned: 10:10:10

In Snowflake the time is:

+----------+
| time_col |
+----------+
| 09:10:10 |
+----------+

To fix this you can set the global timezone to GMT and run the same script. Here is the same example above with the TimeZone changed to GMT:

TimeZone.setDefault(TimeZone.getTimeZone("GMT+00:00")) // <----- ** New bit **
val ps: PreparedStatement = connection.get.prepareStatement("insert overwrite into test_db.public.test_table values (?)")
val insertedTime: Time = Time.valueOf("10:10:10")

println(s"Inserting ${insertedTime} into table")
ps.setTime(1, insertedTime)
ps.executeUpdate()

val results: ResultSet = connection.get.createStatement().executeQuery("select time_col from test_db.public.test_table")
while(results.next) {
  val returnedTime = results.getTime(1)
  println(s"Time returned: ${returnedTime}")
}

Now the script prints (the same):

Inserting 10:10:10 into table
Time returned: 10:10:10

And in Snowflake the time now shows what you would expect:

+----------+
| time_col |
+----------+
| 10:10:10 |
+----------+

Thank you SimonD. Setting default timezone to GMT helped to get everything in sync. — Sam, May 14 '20 at 20:52

Snowflake JDBC TIME datatype value converted to UTC

2 Answers2