The SpringBoot Query returns null while using TIMESTAMPTZ as the Datatype, but the Query works for other Datatypes like TIMESTAMP etc. My Date formats are like, "2022-07-24 10:11:29.452 00".

The DB screenshot is added below.

Also the date type is defined as follows

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "datem")
private Date datem;

The API calls the below code

Date start = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").parse("2022-07-24 10:11:29.452 00");
Date end = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").parse("2022-07-24 10:11:49.452 00");

List<MqttMessageParsed> sensor_data = messageParsedRepository.findByCreatedAtBetween(start, end);

The Query function is as follows

@Query("SELECT t FROM MqttMessageParsed t WHERE t.datem BETWEEN :startDate AND :endDate") List<MqttMessageParsed> findByCreatedAtBetween(@Param("startDate")Date start, @Param("endDate")Date end);

The API shoud return the data between the above start and end dates, but it is returning null now. Am i missing something?

Thanks

CodePudding user response：

Avoid legacy classes

You are using terrible date-time classes that were years ago supplanted by the modern java.time classes. Avoid Date, SimpleDateFormat, and Timestamp.

java.time

For a column of a type akin to the SQL standard type TIMESTAMP WITH TIME ZONE, use the class OffsetDateTime in JDBC 4.2 and later.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

Writing:

myPreparedStatement.setObject( … , odt);

Hibernate was years ago updated to support java.time. Ditto for Jakarta Persistence, formerly Java Persistence API (JPA).

ISO 8601

I suggest you educate the publisher of your inputs about the value of strict compliance with the ISO 8601 standard for date-time formats.

Replace SPACE in the middle with a T.
Use full offset with both hours and minutes, separated by a COLON character.

So this:

"2022-07-24 10:11:29.452 00"

… should be:

"2022-07-24T10:11:29.452 00:00"

… or this:

"2022-07-24T10:11:29.452Z"

If you cannot effect that change, then define a custom formatting pattern to parse that non-standard format. Use DateTimeFormatter class, as has been covered many times already on Stack Overflow.