1

I have an Entity CustomEntity with a data member updateDate of type OffsetDateTime.

I have defined a Repository for this Entity which has a simple method to retrieve list of records matching updateDate as

List<CustomEntity> findByUpdateDate(OffsetDateTime updateDate);

Now, when this method is called from Controller/Service bean, I can see no matching record is retrieved; however, when I execute the generated SQL in the DB, I can see matching rows available.

I can retrieve the records based on other data members of the entity; its just an issue with OffsetDateTime and LocalDateTime

I got to understand that java.time package support was not in JPA 2.1; however I am using JPA 2.3.1. Do I need to use Converters (as suggested for JPA 2.1?

Any help is much appreciable.

EDIT :- Below is the code for Entity

@Entity
@Table(name = "SAMPLE_TABLE")
public class CustomEntity {
    
    @Id
    @GeneratedValue
    private Long id;
    
    @Column
    private OffsetDateTime updateDate;
    
    //Getters & Setters
}

I am using Microsoft SQL Server and the generated SQL query (hibernate generated) looks something like below

select sample0_.id as id1_10, sample0_.updateDate as update2_10 from sample_table sample0_ where sample0_.updateDate=?

binding parameter [1] as [TIMESTAMP] - [2021-07-27T17:22:34.597Z]

Vishal
  • 674
  • 1
  • 7
  • 20
  • What database are you using and how does the generates SELECT looks like? – Simon Martinelli Jul 27 '21 at 14:28
  • @SimonMartinelli, I am using ```MS SQL Server```. Let me update the original question with the generated query. – Vishal Jul 27 '21 at 14:38
  • @SimonMartinelli, please check now – Vishal Jul 27 '21 at 14:47
  • As I can see there is no offfset in the time part. Is this correct? – Simon Martinelli Jul 27 '21 at 14:59
  • the offset has been converted to Zulu, the date has 'Z' – Vishal Jul 27 '21 at 15:44
  • Can you try to search with a date time range? I would say it's a timezone issue with JDBC – Simon Martinelli Jul 28 '21 at 05:38
  • @SimonMartinelli I can execute range search on ```LocalDateTime``` (not sure why exact match is not possible); but for ```OffsetDateTime``` even range is not working. I see its timezone issue, because when I pass the range as ```(startDate.minusDay(1), startDate.plusDay(1))```, I can see the record. When I tried setting Spring boot app time zone as same as DB timezone; still ```OffsetDateTime``` range/exact match is not possible – Vishal Jul 28 '21 at 07:20
  • What's the data type of the column in the database? – Simon Martinelli Jul 28 '21 at 07:32
  • its ```datetimeoffset(7)``` – Vishal Jul 28 '21 at 07:34
  • Can you change the column to datetime2 and try again? Becuase I think Hibernate has a problem with datetimeoffset – Simon Martinelli Jul 28 '21 at 07:58
  • Also check out https://stackoverflow.com/questions/4733844/datetimeoffset-hibernate-mapping – Simon Martinelli Jul 28 '21 at 07:59
  • @SimonMartinelli I do not have control over the database schema, hence any DML is out of the picture – Vishal Jul 28 '21 at 08:12
  • 1
    In that case you must follow: https://stackoverflow.com/questions/4733844/datetimeoffset-hibernate-mapping – Simon Martinelli Jul 28 '21 at 08:14
  • but I am not facing any error as mentioned in the problem statement of https://stackoverflow.com/questions/4733844/datetimeoffset-hibernate-mapping. Is it still relevant for my case? – Vishal Jul 28 '21 at 08:18
  • Yes becuase the handling of the datetimeoffset with hibernate seems to be not correct – Simon Martinelli Jul 28 '21 at 08:36
  • @SimonMartinelli With few changes to the https://stackoverflow.com/questions/4733844/datetimeoffset-hibernate-mapping I am able to fetch the record with exact match or range for ```OffsetDateTime```. However still unable to do exact match for ```LocalDateTime``` (Range works). I will post my code as an answer. Thank you for your kind help mate. – Vishal Jul 29 '21 at 07:37
  • @SimonMartinelli A new problem arises now with update. When I fetch a row, modify some attribute and save; it gives me an error ```org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect)``` – Vishal Jul 29 '21 at 09:01

0 Answers0