Cockroach Issue with timestamp & JDBC

General
#1

I have a table in cockroach like below

root@:26257/vnera> show create table test_table;
+------------+--------------------------------------------------------------------------+
|   Table    |                               CreateTable                                |
+------------+--------------------------------------------------------------------------+
| test_table | CREATE TABLE test_table (                                                |
|            |                                                                          |
|            |     customerid INTEGER NOT NULL,                                         |
|            |                                                                          |
|            |     updatetimestampseconds TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT     |
|            | now(),                                                                   |
|            |                                                                          |
|            |     updatetimestampmilliseconds TIMESTAMP WITH TIME ZONE NOT NULL        |
|            | DEFAULT now(),                                                           |
|            |                                                                          |
|            |     CONSTRAINT "primary" PRIMARY KEY (customerid ASC,                    |
|            | updatetimestampseconds ASC, updatetimestampmilliseconds ASC),            |
|            |                                                                          |
|            |     FAMILY "primary" (customerid, updatetimestampseconds,                |
|            | updatetimestampmilliseconds)                                             |
|            |                                                                          |
|            | )                                                                        |
+------------+--------------------------------------------------------------------------+
(1 row)

Time: 3.592356ms

If I am running a java code like below

public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Class.forName("org.postgresql.Driver");
		Connection db = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:26257/vnera?sslmode=disable", "root", "");
		//Connection db = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/vnera?sslmode=disable", "dmanna", "");
		try {
			final int custId = new Random().nextInt();
			final String q = "insert into test_table" +
                    " (customerId, updatetimestampSeconds, updatetimestampMilliSeconds) " +
                    " values (?, ?, ?) ";
			try (final PreparedStatement stmt = db.prepareStatement(q)) {
				long ts = System.currentTimeMillis();
                stmt.setInt(1, custId);
                stmt.setLong(2, ts/1000);
                stmt.setLong(3, ts);
                stmt.executeUpdate();
            } catch(Exception e) {
            		e.printStackTrace();
            }
		}  finally {
			// Close the database connection.
			db.close();
		}
	}

Then the value that is being inserted in DB is like below

root@:26257/vnera> select * from test_table;
+------------+-------------------------------+-------------------------------+
| customerid |    updatetimestampseconds     |  updatetimestampmilliseconds  |
+------------+-------------------------------+-------------------------------+
| 1744415619 | 2018-04-20 10:50:58+00:00     | 50270-08-28 01:29:21+00:00    |
+------------+-------------------------------+-------------------------------+

Cockroach is silently converting the long to timestamp by multiplying it by 1000.

If I execute the same code with same schema in postgres in 9.6.x then postgres is throwing exception like below

org.postgresql.util.PSQLException: ERROR: column "updatetimestampseconds" is of type timestamp with time zone but expression is of type bigint
  Hint: You will need to rewrite or cast the expression.
  Position: 103
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334)
	at com.db.cockroach.App.main(App.java:71)

Let me know if this is a known issue in cockroach.

#2

Hi @tuk. Try using setTimestamp instead of setLong.

#3

Yes @dan that is what I have done. But shouldn’t the behaviour be same as postgres so that the error becomes apparent.

#4

I’m not sure it’s an “error”. We don’t aim for an identical interface to postgres and, in particular, may allow things to work that postgres doesn’t. In this case, the number of seconds since epoch is an extremely common way of representing a timestamp and it seems reasonable to me that we support it.