Vertica VerticaDayTimeInterval precision is stored wrongly

Question

EDIT: Please point out if others are not seeing this issue, and I am doing something wrong.

I am trying to add rows to a table containing single VerticaDayTimeInterval column through the VerticaDayTimeInterval constructor. The precision and fractional values are being wrongly printed and on retrieving precision for all 6 irrespective of what I gave earlier.

dayInt = new VerticaDayTimeInterval(10, 10, 01, 8, 2, 1, false) ; ((VerticaPreparedStatement) pstmt).setObject(1, dayInt) ; pstmt.addBatch() ; System.out.println(dayInt.toString()); dayInt = new VerticaDayTimeInterval(10, 10, 02, 7, 3, 2, false) ; ((VerticaPreparedStatement) pstmt).setObject(1, dayInt) ; pstmt.addBatch() ; System.out.println(dayInt.toString()); dayInt = new VerticaDayTimeInterval(10, 10, 03, 6, 43, 3, false) ; ((VerticaPreparedStatement) pstmt).setObject(1, dayInt) ; pstmt.addBatch() ; System.out.println(dayInt.toString()); 

table output

DayInt        ------------------- 10 10:03:49.000211 11 07:00:00.0002 9 09:09:05.000005 (3 rows) 

retriving and printing rows with resultset.

for (int x=1 ; rs.next() ; ++x) {     VerticaDayTimeInterval dti = (VerticaDayTimeInterval)(rs.getObject(1));     System.out.println("vertica object tostring "+dti.toString()+" frac "+dti.getFraction()+" precision "+dti.getPrecision()); } 

Output

vertica object tostring 10 10:03:49.211000 frac 211000 precision 6 vertica object tostring 11 07:00:00.200000 frac 200000 precision 6 vertica object tostring 9 09:09:05.500000 frac 500000 precision 6 

My code is similar to https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/ConnectingToHPVertica/ClientJDBC/UsingIntervalsWithJDBC.htm

Answer 1

Precision is always a guideline for representation of data. Data are always stored with a specific precision, which is the maximum precision supported by the driver (and the engine of course). The precision that was used by the user to insert data is not stored in the database, as it is only useful when representing data.

Classic JDBC drivers support up to nanoseconds (precision of 9 digits) for java.sql.Timestamp.

Vertica JDBC driver supports up to microseconds (precision of 6 digits) for VerticaDateTimeInteval.

I don't know what is exactly your intent when you want to specify a precision of 1, 2 or 3 in your sample code, but, IMHO when you create a new VerticaDateTimeInterval object and you want to define a number of second fractions, you have 2 main options (that make sense):

• precision of 3, and the given fractions number would be in milliseconds
• precision of 6, and the given fractions number would be in microseconds.

With a precision of 1, the number of fractions is in tenths of second. With a precision of 2, is in hundredths of second. With a precision of 4, is in 1/10000s of second. With a precision of 5, is in 1/100000s of second.

Accordingly, when the database returns your query result, then it should inform you that the integer content of the fractions field of the returned object has a precision of 6 (meaning this is a number of microseconds).