search

marschall / threeten-jpa

JPA attribute converters for JSR-310 dates and times
53 stars 6 forks source link

Support for Oracle TIMESTAMP WITH LOCAL TIME ZONE #49

Open LumnitzF opened 6 years ago

LumnitzF commented 6 years ago

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

marschall commented 6 years ago

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?

LumnitzF commented 6 years ago

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

marschall commented 6 years ago

Can you elaborate why you would prefer TIMESTAMPLZ over TIMESTAMPTZ?

LumnitzF commented 6 years ago

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

marschall commented 6 years ago

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.

LumnitzF commented 6 years ago

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