1

How do I pass an array of timestamps to the Postgres function using Java and JDBC?

Here's the signature of the function:

CREATE OR REPLACE FUNCTION getlistcount(_id integer, _subs text, _download_array timestamp without time zone[], _filter integer, _fault text) RETURNS refcursor AS...

Here is the Java code that creates the array: 

GregorianCalendar[] dataDownloads = 
        downloadTimes.toArray(new GregorianCalendar[downloadTimes.size()]);

Array sqlArray = conn.createArrayOf("timestamp", dataDownloads);
cstmt.setArray(4, sqlArray);

The downloadTimes are List passed into the Java function.

One hint: we need to concatenate the time with the date, according to the Postgres documentation.

MAbraham1
  • 1,717
  • 4
  • 28
  • 45
  • 1
    I see that you're using `TIMESTAMP WITHOUT TIME ZONE`. In almost every use case, `TIMESTAMP WITH TIME ZONE` is better; it represents an actual moment in time, while the other does not. – kgrittn May 11 '12 at 17:50
  • @KGrittn, that makes sense. We're getting the timezone from the download-time (see below), save the timezone with the timestamp. Thank you! – MAbraham1 May 11 '12 at 20:13

2 Answers2

2

I found an answer on StackOverflow that is similar, but uses the PreparedStatement rather than the JDBC notation.

//Get timezone from first entry, assuming all same timezone
if(!downloadTimes.isEmpty()) {
    cal.setTimeZone(downloadTimes.get(0).getTimeZone());
}

//Create an array of timestamps
java.sql.Timestamp [] array = new java.sql.Timestamp [downloadTimes.size()];

for(int i=0; i < array.length; i++) {
    array[i] = new java.sql.Timestamp(downloadTimes.get(i).getTimeInMillis());
} 

//pass the array to JDBC call
//conn is the connection, and cstmt is the CallableStatement
Array sqlArray = conn.createArrayOf("timestamp", array);
cstmt.setArray(4, sqlArray);
Community
  • 1
  • 1
MAbraham1
  • 1,717
  • 4
  • 28
  • 45
1

The array doesn't have to be of the old java.sql.Timestamp class, you can use java.time classes instead.

Like this 

LocalDateTime[] localTimes = new LocalDateTime[]{LocalDateTime.now()};
Array sqlArray = conn.createArrayOf("timestamp", localTimes);
cstmt.setArray(4, sqlArray);

For timestamp with zone use java.time.OffsetDateTime

David Lilljegren
  • 1,799
  • 16
  • 19
  • 1
    Thanks for updating with fresher code, @david-lilljegren. Yes, that's the best-practice for the timestamp with timezone. – MAbraham1 Dec 04 '19 at 19:11
  • Postgres JDBC driver erases the timezone information, if one passes the type `timestamp` to the `createArrayOf` method. Using the type `timestamptz` will fix this problem. – Lauri Lehmijoki Oct 10 '21 at 05:17