I need to pass Timestamp value as parameter in URL to retrieve some data. Format of Timestamp in DB2 is: 2022-05-25-11:10:44.662000, but in java.sql.Timestamp is: 2022-05-25 11:10:44.0 and when I pass it, it returns empty list.
My ActionController class method:
@GetMapping(value = "/fetchData/{id}/{timestamp}")
def fetchData(@PathVariable int id, @PathVariable Timestamp timestamp) {
def serviceResult = actionService.fetchData(id, timestamp);
return parseServiceResult(serviceResult)
}
My ActionService class method:
ServiceResult fetchData(int id, Timestamp timestamp) {
ServiceResult ret = new ServiceResult()
List<DataDTO> retSet = new ArrayList<DataDTO>()
List<String> errorMessage = new ArrayList<String>()
try {
retSet = actionEntity.fetchData(id, timestamp)
} catch (CollectionsException e) {
errorMessage << "ActionService.fetchData error"
log.debug("ActionService.fetchData error" + e.getMessage())
}
ret = new ServiceResult(success: errorMessage.isEmpty(), result: retSet, errorMessageTextList: errorMessage)
ret
}
My ActionEntity class method:
ArrayList<DataDTO> fetchPoulsFromStorno(int id, Timestamp timestamp) throws CollectionsException {
def sql = """SELECT * FROM NSNP.TABLE1 PT INNER JOIN NSNP.TABLE2 ST
ON (ST.COLUMN1 = PT.COLUMN1 AND
ST.COLUMN2 = PT.COLUMN2 AND
ST.COLUMN3 = PT.COLUMN3 AND
ST.COLUMN4 = PT.COLUMN4 AND
ST.COLUMN5 = PT.COLUMN5 AND
ST.COLUMN6 = PT.COLUMN6 AND
ST.COLUMN7 = PT.COLUMN7 AND
ST.COLUMN8 = PT.COLUMN8 )
WHERE ST.ID = $id AND ST.TIMESTAMP= $timestamp"""
ArrayList<DataDTO> dataList = new ArrayList<DataDTO>()
try {
if (this.sql != null) {
this.sql.eachRow(sql) {
resultSet ->
System.out.println(resultSet)
DataDTO dataDTO = new DataDTO()
dataDTO .setProperty1(resultSet.COLUMN1)
dataDTO .setPropety2(resultSet.COLUMN2)
dataDTO .setProperty3(resultSet.COLUMN3)
dataList.add(dataDTO)
}
}
} catch (SQLException se) {
log.info "ActionEntity.fetchData error $se.message executed sql: $sql"
} finally {
if (this.sql != null) {
this.sql.close()
}
}
dataList
}
Can't provide you exact and real data, but I'm sure you'll understand code and what I'm trying to do.
You are using the wrong types.
TIMESTAMP WITHOUT TIME ZONE, use java.time.LocalDateTime.TIMESTAMP WITH TIME ZONE, use java.time.OffsetDateTime.Never use the flawed legacy class java.sql.Timestamp.
👉 Your hour difference is likely resulting from that class adjusting for time zone, which is problematic because the TIMESTAMP column (TIMESTAMP WITHOUT TIME ZONE column) in DB2 has no time zone or offset.
Format of Timestamp in DB2 is: 2022-05-25-11:10:44.662000
No, a TIMESTAMP type in DB2 does not have a “format”. Text has a format. But that type TIMESTAMP does not contain text. That type uses its own internally-defined data for tracking a date-time value.
The matching type in Java for a DB2 column of type TIMESTAMP WITHOUT TIME ZONE is java.time.LocalDateTime.
but in java.sql.Timestamp
Never use the Timestamp class. That class is part of the legacy date-time classes that were tragically flawed in their design. They were years ago supplanted by the modern java.time classes defined in JSR 310. The Timestamp class was specifically replaced by java.time.Instant, though for interacting with a database you would use OffsetDateTime class.
JDBC 4.2 and later requires all JDBC drivers to support an appropriate subset of the java.time classes.
Another issue is that TIMESTAMP in DB2 (short for TIMESTAMP WITHOUT TIME ZONE) purposely lacks the context of an offset-from-UTC or time zone. Without an offset or zone, that type cannot be used to represent a moment, a specific point on the timeline.
In contrast, the java.sql.Timestamp class does represent a moment, a specific point on the timeline.
So TIMESTAMP in DB2 does not match up to java.sql.Timestamp.
👉 Instead, for exchanging values with a TIMESTAMP column in DB2, use the java.time.LocalDateTime class. Like the DB2 type, that class purposely lacks the context of an offset or zone.
LocalDateTime ldt = LocalDateTime.parse( "2022-05-25T11:10:44.662000" ) ;
See this code run live at Ideone.com.
ldt.toString() is 2022-05-25T11:10:44.662
Writing.
myPreparedStatement.setObject( … , ldt ) ;
Retrieval.
LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;
The TIMESTAMP type in DB2 resolves to the very FINE resolution of picoseconds. To quote the manual:
The number of digits in the fractional seconds portion is specified using an attribute in the range 0 - 12 with a default of 6
A nanosecond, a billionth of a second, is a fractional second of nine digits. Twelve digits represent a picosecond, a trillionth of a second.
The java.time classes have a resolution of nanoseconds.