[HIVE-24891] Tablestamp field returns a value different from what was inserted using PreparedStaement.setLong

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Component/s: JDBC
• Assignee: Unassigned
• Reporter: Anurag Shekhar
• Created: 17/Mar/21 07:11
• Updated: 18/Mar/21 05:05

Description

Setup

Hive Cluster Timezone - UTC

JDBC Client Timezone - IST

Create timestamp "ts = Timestamp.valueOf("2021-03-16 00:00:00.000");"

Insert using PreparedStatement (call setLong (index, ts.getTime())

Query Same field.

Return Timestamp differs from the one inserted.

Reproduction Code 

private static String getFormattedTimestamp(Timestamp ts) {
    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS z");
    return format.format(ts);
}

public static void main (String [] arg) throws SQLException {
    TimeZone.setDefault(TimeZone.getTimeZone("IST"));
    Connection conn = DriverManager.getConnection ("jdbc:hive2://anurag-hwc-2.anurag-hwc.root.hwx.site:10000", "hive", "hive");
    Statement stmt = conn.createStatement();
    stmt.execute("drop table if exists ts_table");
    stmt.execute("create table ts_table (ts timestamp) stored as orc");
    PreparedStatement pStmt = conn.prepareStatement("insert into ts_table (ts) values (?)");
    long timeStamp = System.currentTimeMillis();
    Timestamp ts = Timestamp.valueOf("2021-03-16 00:00:00.000");
    pStmt.setLong (1, ts.getTime());
    pStmt.execute();
    pStmt.close();

    System.out.println ("Inserted " + getFormattedTimestamp(ts) + " In millis " + ts.getTime());

    ResultSet rs = stmt.executeQuery("Select * from ts_table");
    rs.next();
    Timestamp resultTs = rs.getTimestamp(1);
    System.out.println("Retrieved " + getFormattedTimestamp(resultTs) + " In millis " + resultTs.getTime());
    rs.close();
} 

 

Output of above code
Inserted 2021-03-16T00:00:00.000 IST In millis 1615833000000
Retrieved 2021-03-15T18:30:00.000 IST In millis 1615813200000

Issue Links

• causes: HIVE-24888 JDBC Query with where clause containing timestamp field in long doesn't return any result.