threeten-jpa icon indicating copy to clipboard operation
threeten-jpa copied to clipboard

Published 20 hours ago • verified publisher icon marschall

Support for Oracle TIMESTAMP WITH LOCAL TIME ZONE

Open LumnitzF opened this issue 6 years ago • 6 comments

Oracle also supports the TIMESTAMP WITH LOCAL TIME ZONE, where the database stores the time normalized to the database timezone. Upon querying, the time is converted into the session timezone of the query. --> The Java Attribute could either be of LocalDateTime (this can be read directly from the oracle.sql.TIMESTAMPLTZ object) or of Offset-/ZonedDateTime (this would require to read the timezone information out of the session)

Please note, that EclipseLink also uses a TIMESTAMPLTZWrapperwhich leads to a similiar issue as #23

LumnitzF avatar Mar 23 '18 09:03 LumnitzF

Do you use this in your application or is this simply for completeness? If you use it in your application would you mind sharing your use case?

Which JPA provider are you using?

marschall avatar Mar 23 '18 12:03 marschall

In our application, we are currently storing times only as local times. In the future we want to migrate to times with time-zone information. So it is possible that we will change to the TIMESTAMPLTZ and then the backend has to support it... So it's part for completness and part for real use case.

I am using EclipseLink 2.6.4

LumnitzF avatar Mar 23 '18 13:03 LumnitzF

Can you elaborate why you would prefer TIMESTAMPLZ over TIMESTAMPTZ?

marschall avatar Mar 23 '18 15:03 marschall

The TIMESTAMPLTZ supports an automated conversion into the time zone of the session. When an application has many users from all over the world, and you don't want to do the conversion in the back- or frontend, you can set the correct timezone in the session, and let the database do the conversion for you.

So in my opinion, I would prefare TIMESTAMPLTZ only, when I always want the time to be in the timezone of the session, without having to check or convert it in my application

LumnitzF avatar Mar 28 '18 08:03 LumnitzF

When an application has many users from all over the world, and you don't want to do the conversion in the back- or frontend

Can you expand on why you don't want to have this happen in the back- or frontend?

supports an automated conversion into the time zone of the session

Of the database session. (Assuming you have a server application) When a request comes in you have to change the time zone of the database session to the time zone of the user before making and database calls. Also you have to be careful that you don't store or cache objects across different sessions, (eg. JPA second-level cache).

I'm not saying you're wrong. I'm just curious about your use case.

marschall avatar Mar 28 '18 14:03 marschall

As I already said, at the moment I don't really have a use case. However I may have to support this type in the future, so it may be good to support it for completeness

LumnitzF avatar Mar 29 '18 08:03 LumnitzF