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

Consubstantial

It seems like something is removing the leading 0's. What it rs.next doing, how are you getting this data from the table? it is possible that the method may be the cause of your problems. Edit: Your code looks correct, I think the issue is caused in how you are retrieving the data from the table. – Dairy

One other thought that may not help at all but it is worth a shot: Instead of using (VerticaDayTimeInterval)(rs.getObject(1)); have you tried rs.getObject(1, VerticaDayTimeInterval)? – Dairy

@sorifiend, unfortunately that cannot be done. – Consubstantial

why are you using octal numbers as minute parameters? integers with leading zeros are treated as octal numbers. Perhaps the java class is confused at this point. – Redstart

@JoshuaK, removing the leading 0 did not change the output, so octal conversion is not an issue. – Consubstantial

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).

Alterable

If you have access to vertica setup, could you confirm if this issue is seen or not seen for values 1-6 , which includes value of 3 – Consubstantial

Unfortunatelly not, I don't have access to vertica setup. All I wanted to say is that you don't need the fractions precision to be stored. And it's not stored. – Alterable