[FIXED] PostgreSQL TIMESTAMPTZ is not working with SpringBoot Java Query

Issue

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.

href="https://i.stack.imgur.com/Cbbyx.png" rel="nofollow noreferrer">

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

Solution

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 alternatively use a Z as the suffix to indicate an offset of zero:

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

---

By the way, know that the other data type, TIMESTAMP WITHOUT TIME ZONE cannot be used to record a moment, a specific point on the timeline. This type stores only a date and a time-of-day without the context of a time zone or offset-from-UTC. So, for example, given the date of last January 23rd at 12:00, we cannot know if that meant noon in Tokyo Japan, noon in Toulouse France, or noon in Toledo Ohio US — three different moments several hours apart.

If you need to track when something happened, always use TIMESTAMP WITH TIME ZONE. In the case of Postgres, any time zone or offset info supplied with an input is used to adjust to UTC (an offset of zero) and then discarded. The moment is always stored in UTC, in Postgres for this type. If you care about the original time zone, store that in a second column.

Answered By - Basil Bourque
Answer Checked By - Marilyn (JavaFixing Volunteer)