2

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.

enter image description here

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

Improve this question
4
  • You should use instances of ZonedDateTime as the parameter value, not java.util.Date (or java.sql.Date) Commented Jul 30, 2022 at 19:09
  • @a_horse_with_no_name Did you mean to say OffsetDateTime? Commented Jul 30, 2022 at 19:13
  • 2
    In any case I strongly recommend you don’t use SimpleDateFormat and Date. Those classes are poorly designed and long outdated, the former in particular notoriously troublesome. Do use OffsetDateTime and DateTimeFormatter, both from java.time, the modern Java date and time API, as Basil Bourque says in his answer. Commented Jul 30, 2022 at 19:20
  • You are not parsing the offset, +00, from your strings. SimpleDateFormat is probably assuming some other time zone, giving you results that are hours off, potentially up to 14 hours. Commented Jul 30, 2022 at 19:23

1 Answer 1

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Hmm i have also tried the OffsetDateTime, but it didnt worked for me. Can you add some more code details in your answer. Thanks in advance
@Ansif I do not use Spring, so that’s as far as I can help you. I’ll bet you can find code examples on existing Answers to other Questions.
Ok i did a partial fix to the above problem. I have changed the Datatype from TIMESTAMPTZ to TIMESTAMP in my Database and the query is working now. Anyway the real is fix is based on your solution. I will mark this as the correct answer. Thanks Basil Bourque
It certainly is. A timestamp without time zone cannot define a point in time so despite the name is unsuited for a timestamp.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.