SNOW-928973: Date field is returning one day less when getting through getString method

[mmuruganandam]

I totally get that for getting a date we have to use getDate on the resultset. But in our legacy code, we have had getString when using other database and now converted to snowflake.

The get string of a date field is returning one day less while changing the method to getDate is returning the right date. Either the getString should throw an error if it can't return the same value as the getDate. It is NOT a timestamp to think that there would have been TZ conversion. It is a pure date field and should return same values or at least throw an error when using the wrong method to get the data.

Please answer these questions before submitting your issue.
In order to accurately debug the issue this information is required. Thanks!

1. What version of JDBC driver are you using?
   3.13.33

2. What operating system and processor architecture are you using?
   RedHat - 64bit

3. What version of Java are you using?
   8

4. What did you do?
   resultset.getString("date")

returns 02/01/2022. but expected to see 02/02/2022

If possible, provide a recipe for reproducing the error.
A complete runnable program is good.

5. What did you expect to see?
   resultset.getString("date") should give similar result as resultset.getDate("date")

02/02/2022

What should have happened and what happened instead?
should have resturned same as date. instead it is returning one day less.

6. Can you set logging to DEBUG and collect the logs?

   https://community.snowflake.com/s/article/How-to-generate-log-file-on-Snowflake-connectors

7. What is your Snowflake account identifier, if any? (Optional)

[sfc-gh-spanaite]

@mmuruganandam What is the data type of field date on Snowflake side?

[mmuruganandam]

It is a date in DB

[sfc-gh-spanaite]

@mmuruganandam When I run this snippet of code:

Statement statement = con.createStatement();
    statement.execute("create or replace table testtable (d date);");
    statement.execute("insert into testtable (d) values ('02/02/2022');");
    ResultSet res = statement.executeQuery("select * from testtable");
    while (res.next()) {
	    System.out.println(res.getString("D"));
    }

I get:

2022-02-02

Can you post the DDL of your table along with 1-row sample of data?

[mmuruganandam]

Sorry, it is a VIEW and the column is PRVDR_BIRTH_DT DATE

See the image below for the data mismatch.

[sfc-gh-spanaite]

This doesn't help me much @mmuruganandam
Please post the DDL of the view and the DDL of the table/s behind the view.

You can use get_ddl function to get the DDL output.

[sfc-gh-spanaite]

Do you still have any questions @mmuruganandam otherwise I'll go ahead and close this issue.

[krocodl]

I have the similar issue: after ALTER SESSION SET JDBC_QUERY_RESULT_FORMAT=JSON

rs.getObject(1) => 2020-12-01
rs.getDate(1) => 2020-12-02 - right value

For default ARROW format both ways return the same right value

Driver net.snowflake:snowflake-jdbc:3.14.2
DDL: create or replace TABLE TEST_TABLE (A_DATE DATE);
Filling: insert into TEST_TABLE (A_DATE) values ('2020-12-02')

[sfc-gh-spanaite]

@krocodl I cannot reproduce when using JSON resultset format.
I tried this:

Statement statement = con.createStatement();
    statement.execute("alter session set JDBC_QUERY_RESULT_FORMAT=JSON");
    statement.execute("create or replace table testtable (d date);");
    statement.execute("insert into testtable (d) values ('2020-12-02');");
    ResultSet res = statement.executeQuery("select * from testtable");
    while (res.next()) {
	    System.out.println("getObject: " + res.getObject(1));
	    System.out.println("getDate: " + res.getDate(1));
	    System.out.println("getString: " + res.getString(1));
    }

and I get back:

getObject: 2020-12-02
getDate: 2020-12-02
getString: 2020-12-02

Are you able to provide a JDBC log with tracing=all so I can see what is happening?

[krocodl]

@sfc-gh-spanaite

`
public void jdbcDateIssueTest() throws Exception{

    try (
            Connection conn = dataSource.getConnection()
    ) {
        Statement statement = conn.createStatement();
        statement.execute("alter session set JDBC_QUERY_RESULT_FORMAT=JSON");
        statement.execute("create or replace table testtable (d date);");
        statement.execute("ALTER SESSION SET TIMEZONE = '" + ZoneId.of("Asia/Kolkata") + "'");
        statement.execute("insert into testtable (d) values ('2020-12-02');");
        ResultSet res = statement.executeQuery("select * from testtable");

        while (res.next()) {
            System.out.println(System.getProperty("java.io.tmpdir"));
            System.out.println("getObject: " + res.getObject(1));
            System.out.println("getDate: " + res.getDate(1));
            System.out.println("getString: " + res.getString(1));
        }
    }
}`

Output:

getObject: 2020-12-01
getDate: 2020-12-02
getString: 2020-12-01

JDBC log is attached
snowflake_jdbc0.log

Without JDBC_QUERY_RESULT_FORMAT all is ok. Setting connection property JDBC_QUERY_RESULT_FORMAT results in the same effect - the difference between getDate and getString

This problem is indirectly related to different time zones.
It would be understandable if, due to the time difference, getDate would give a more accurate result in both cases.
But the difference between the two low-level protocols for receiving data from servers still seems to me to be an error.

[sfc-gh-spanaite]

@krocodl I see now what you mean. I am able to reproduce as well, and I can see it's related to the implementation of getString and getObject methods when using JSON for result-set. I'll discuss it back internally for a fix.

[krocodl]

@krocodl I see now what you mean. I am able to reproduce as well, and I can see it's related to the implementation of getString and getObject methods when using JSON for result-set. I'll discuss it back internally for a fix.

Thanks!

[sfc-gh-wfateem]

I was looking into this as well, if you really need to use JSON as the resultset then a workaround is to set the parameter JDBC_FORMAT_DATE_WITH_TIMEZONE=FALSE in the JDBC driver.

[sfc-gh-wfateem]

This is currently being addressed in PR #1715

[ianils]

Looks like we are awaiting a code review of the fix ?

[sfc-gh-dszmolka]

change seems to have merged in the meantime and released with Snowflake JDBC driver version v3.18.0