[hibernate-dev] Date/Time Support and timezones

[Steve Ebersole]

The real difficulty with (3) is querying.  How do you restrict a query on a
(TIMESTAMP,TZ) tuple for example when the individual values could be in
multiple timezones?  Andrea and I were just discussing the same in regards
to Java 9(?) Money/Currency support.

Personally I think this idea of trying to store and handle dates in
different timezones is silly, and more importantly that it is asking for
trouble.  IMO I think all dates and times should be treat as UTC, stored
and handled as such and only ever converted to a specific TZ when rendering
(UI, etc).

The other difficulty is how to know that the db type is "WITH TIMEZONE" or
not.  Because that effects how we need to pass things and retrieve them to
be consistent.  If you always handle temporals in UTC in the app and tell
the JDBC driver to use UTC for read/write (or set default JVM TZ to UTC)
then things JustWork.  I will say that JSR 310 is much better in terms of
being able to normalize temporals.  So maybe we should leverage that.


On Thu, Mar 26, 2015 at 10:44 AM, Sanne Grinovero <sanne at hibernate.org>
wrote:

> Forcing 3 is correct but rarely desirable, I'm afraid the user should
> choose.
>
> But most people will not like the additional
> space/performance/complexity hit, and will use a single timezone.
>
> If you think we should force people into a correct choice, you'd
> require 3# unless they are willing to set a global timezone
> configuration property, or maybe even a per-property timezone on the
> mapping, in which case you'd go for 2(4) but having them choose the
> poison.
>
> On 24 March 2015 at 12:30, Gunnar Morling <gunnar at hibernate.org> wrote:
> > 3) seems most desirable to me, too.
> >
> > What type would your "TZ" column have? Just a numeric offset? Or a
> VARCHAR,
> > also capable of storing textual TZ representations such as "+01:00
> > Europe/Paris"? I think the latter would be needed to make it fully
> > reflexive for ZonedDateTime.
> >
> > Another variant of 3) could be to leverage DB-specific types such as
> > TIMESTAMP WITH TIME ZONE on Oracle. I could imagine that'd be what users
> of
> > databases supporting such a type might want.
> >
> > --Gunnar
> >
> >
> > 2015-03-24 3:00 GMT+01:00 Steve Ebersole <steve at hibernate.org>:
> >
> >> Not in the same Type.  But of course "Type swapping" is so easy
> nowadays...
> >>
> >> On Mon, Mar 23, 2015 at 4:01 PM, Hardy Ferentschik <hardy at hibernate.org
> >
> >> wrote:
> >>
> >> > > A few options:
> >> > >
> >> > > 1) Forego OffsetDateTime, OffsetTime and ZonedDateTime support and
> just
> >> > > stick with LocalDateTime, LocalDate and LocalTime.
> >> > > 2) Use the timezone/offset to pass along to the driver (for proper
> >> > > conversion); when reading back we'd have to read back based on the
> >> > default
> >> > > timezone.  This is essentially the old strategy used in CalendarType
> >> > which
> >> > > I never really liked because its not reflexive.
> >> > > 3) Break them into a tuple of the store each piece.  E.g., for
> >> > > OffsetDateTime the Tuple is a LocalDateTime (the Timestamp) and a TZ
> >> > > offset.  So we'd store each individually in the database and be
> able to
> >> > > rebuild them in a fully reflexive manner.
> >> > > 4) Handle them using UTC or GMT at the JDBC level.  This is
> essentially
> >> > the
> >> > > same as (2)
> >> >
> >> > Personally, I think I'd prefer #3. I am not sure whether all users
> would
> >> > be happy
> >> > with two columns for a OffsetDateTime. Could we support multiple
> options,
> >> > for example
> >> > #3 and #4 and make it configurable?
> >> >
> >> > --Hardy
> >> >
> >> _______________________________________________
> >> hibernate-dev mailing list
> >> hibernate-dev at lists.jboss.org
> >> https://lists.jboss.org/mailman/listinfo/hibernate-dev
> >>
> > _______________________________________________
> > hibernate-dev mailing list
> > hibernate-dev at lists.jboss.org
> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>