4:07 p.m.
Hi,
While reviewing the Pull Request for this issue:
https://github.com/hibernate/hibernate-orm/pull/1536
I realized that we can improve the default TimestampType as follows:
1. We could make it parameterizable so that it can also take a custom
timezone (UTC) during mapping.
2. We could also define a default timezone so that we don't rely on the JVM
one. This might be desirable when the UI requires to display the time in a
certain timezone, while we want to save all timestamps in UTC.
Let me know what you think.
Vlad
6:16 p.m.
On Wed 2016-08-31 17:07, Vlad Mihalcea wrote:
Hi,
While reviewing the Pull Request for this issue:
https://github.com/hibernate/hibernate-orm/pull/1536
I realized that we can improve the default TimestampType as follows:
1. We could make it parameterizable so that it can also take a custom
timezone (UTC) during mapping.
2. We could also define a default timezone so that we don't rely on the JVM
one. This might be desirable when the UI requires to display the time in a
certain timezone, while we want to save all timestamps in UTC.
Or a component injecting the desired timezone
8:51 p.m.
We discussed this on HipChat, but for the benefit of all on this
discussion...
Part of this (the original report) speaks to a difference in how we map
(org.hibernate.type.Type) java.time temporal versus a java.util temporal -
specifically java.util.Calendar. When we are passed a Calendar (the model
defines its attribute as Calendar) we pass along that Calendar reference to
the JDBC driver which forces the driver to use that Calendar's TZ. For
java.time temporal types, we always resolve them to UTC values mainly
because of how we get to the java.sql.Timestamp value from the java.time
value. For example, for ZonedDateTime we call:
return (X) Timestamp.from( zonedDateTime.toInstant() );
The call to #toInstant there essentially forces the value to UTC because it
converted directly to epoch-based numeric.
Thinking about this some more and "stepping back", the real problem is that
there are a few different timezones in play here that need to be balanced
and handled:
1. The database default TZ
2. The stored TZ
3. The JVM/JDBC TZ
4. The application TZ
The whole idea of "stored TZ" really depends on how the database/driver
treats TIMESTAMP and whether it supports a "TIMESTAMP_WITH_TIMEZONE" type. I
personally think it is a huge mistake to save the specific TZ differences
to the DB, so I would personally continue to not support
TIMESTAMP_WITH_TIMEZONE
types. This would mean we never have to bind the Calendar because we could
simply convert the value to to the JVM/JDBC TZ ourselves. Specifically I
would suggest that we (continue to) assume that the driver has been set up
such that the same TZ is used when
1. when binding a Timestamp (without passing a Calendar)
2. reading a Timestamp (without passing a Calendar).
Specifically this would mean setting the JVM TZ, or for JDBC drivers that
support it setting the "default TZ" on the driver. If we start storing
values in the TZ specific to the Calendar we really have no idea how to
properly read those values back because we'd lose context to that TZ. For
that reason I suggest we drop passing along the Calender and instead
perform all these conversions in memory and pass the converted Timestamp to
JDBC. When reading back, that should mean the driver would return us
Timestamps relative to that same TZ.
The last piece is "application TZ" which would be an optional setting
indicating the TZ zone-based values should be returned in.
E.g., let's say a user maps a ZonedDataTime using @Temporal(TIMESTAMP). If
following my suggestions, when saving these values we would:
1. convert the ZonedDateTime to a ZonedDateTime in the "JVM/JDBC TZ" (if
different from the incoming ZonedDateTime's TZ)
2. use that to construct a Timestamp to bind to JDBC
When reading back these values, we would:
1. get back the Timestamp from JDBC
2. assume the value is relative to the "JVM/JDBC TZ" and build the
ZonedDateTime via `ZonedDateTime.ofInstant( timestamp.toInstant(),
jvmJdbcZoneId )`
3. convert that built ZonedDateTime to the application TZ:
`builtZonedDateTime.withZoneSameInstant( applicationZoneId )` - it might
be possible to combine this step with the previous, not sure...
Some if this is type-specific. Assuming the suggestions above:
- Instant - inherently epoch-based.
- to convert this to a Timestamp for binding we would use
`Timestamp#from(Instant).
Because of the above assumptions, the driver would interpret
this relative
to JVM/JDBC TZ
- Reading back we simply use `Timestamp#toInstant()`
- LocalDateTime - is the relative idea of a date+time outside the
context of any specific TZ.
- When binding we would resolve this into the JVM/JDBC TZ (using
LocalDateTime#atZone(jvmJdbcZoneId), convert to an Instant and create a
Timestamp and bind that Timestamp to JDBC.
- When reading back we'd get the Instant from Timestamp and use that
in `LocalDateTime#ofInstant` using the application TZ
- LocalDate - relative idea of a date outside of any specific TZ. Of
course this could also be mapped to a JDBC DATE which circumvents some of
this, but mapped to a TIMESTAMP...
- When binding we'd convert to a LocalDateTime using
`LocalDate#atStartOfDay` and treat that LocalDateTime as above.
- When reading back we would create a LocalDateTime using
`LocalDateTime.ofInstant(timestamp.toInstant(),
applicationZoneId )`
- LocalTime - relative idea of a time outside any specific TZ. Could
also be mapped to a JDBC TIME...
- When binding we'd create a LocalDateTime using `LocalTime.atDate(
LocalDate.of( 1970, 1, 1 ) )`, converting that to a ZonedDateTime using
`LocalDateTime#atZone( jvmJdbcZoneId )` and finally converting that to a
Timestamp
- When reading we would call `LocalDateTime.ofInstant(
timestamp.toInstant(), applicationZoneId ).toLocalTime()`
- ZonedDateTime - like a Calendar bundles a date+time with a TZ, and so
is directly convertible to an epoch-based value (Instant, Timestamp, etc).
- When binding we would convert this to an Instant and then to a
Timestamp
- When reading we would convert the Timestamp to an Instant and then
convert the Instant to a ZonedDateTime using `ZonedDateTime.ofInstant(
timestamp.toInstant(), applicationZoneId )`
- OffsetDateTime - handled similar to ZonedDateTime.
- When binding we would convert the value to a ZonedDateTime using
`OffsetDateTime#atZoneSameInstant( jvmJdbcZoneId )` and then follows
ZonedDateTime
- When reading we would use `OffsetDateTime.ofInstant(
timestamp.toInstant(), applicationZoneId )`
- OffsetTime - atm we just drop the ZoneOffset
- for java.util.Date and java.sql.Timstamp mapping we have to decide
what those concepts model in regards to their value relative to a TZ. Are
they a LocalDateTime relative to the system TimeZone? Are they a
LocalDateTime relative to UTC? At the moment how we handled these "legacy
Java temporal types" ends up answering this question as them being "a
LocalDateTime relative to the system TimeZone".
- what about java.sql.Time? Is this a LocalTime? An OffsetTime
relative to UTC (+0)?
- and java.sql.Date?
Lot of moving parts when you actually start looking deep at "supporting
timezones".
[1]
http://stackoverflow.com/questions/18447995/postgresql-9-2-jdbc-driver-us...,
etc
On Wed, Aug 31, 2016 at 9:10 AM Vlad Mihalcea <mihalcea.vlad(a)gmail.com>
wrote:
Hi,
While reviewing the Pull Request for this issue:
https://github.com/hibernate/hibernate-orm/pull/1536
I realized that we can improve the default TimestampType as follows:
1. We could make it parameterizable so that it can also take a custom
timezone (UTC) during mapping.
2. We could also define a default timezone so that we don't rely on the JVM
one. This might be desirable when the UI requires to display the time in a
certain timezone, while we want to save all timestamps in UTC.
Let me know what you think.
Vlad
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
8:46 a.m.
it is a huge mistake to save the specific TZ differences to the DB
I once was working on a globally deployed shipping management application
where that was a requirement.
Say there is a shipment from Austin (one TZ) to London (another TZ). In
that application an operator based in Hamburg (yet another TZ) should be
able to know when the shipment was brought in in Austin (the local time in
Austin at that point) and when it was delivered in Hamburg (again the local
time there at that point).
So we stored all dates in UTC, but also the TZ (in a *different column*; I
don't quite remember whether it was just a string with the TZ (offset) or
again the entire date-time using TIMESTAMP_WITH_TZ). Storing it in UTC
allowed for comparisons whereas the other column allowed to show each date
in its local TZ. So an operator in Hamburg (at CET) could see a delivery
was brought in in Austin at 8:32 CST.
Something like that would still be my preference for handling TZ-bound
types such as ZonedDateTime: store the UTC timestamp in one column and the
TZ info (either id or offset from UTC) in another.
Relying on the JVM TZ or something like a single "application TZ" won't cut
it for cases like the one above: the operator in Hamburg wants to see dates
from Austin and London, using their respective time zones. Instead, the TZ
of the incoming ZonedDateTime would have to be persisted so it's available
when reading it back.
2016-08-31 20:51 GMT+02:00 Steve Ebersole <steve(a)hibernate.org>:
We discussed this on HipChat, but for the benefit of all on this
discussion...
Part of this (the original report) speaks to a difference in how we map
(org.hibernate.type.Type) java.time temporal versus a java.util temporal -
specifically java.util.Calendar. When we are passed a Calendar (the model
defines its attribute as Calendar) we pass along that Calendar reference to
the JDBC driver which forces the driver to use that Calendar's TZ. For
java.time temporal types, we always resolve them to UTC values mainly
because of how we get to the java.sql.Timestamp value from the java.time
value. For example, for ZonedDateTime we call:
return (X) Timestamp.from( zonedDateTime.toInstant() );
The call to #toInstant there essentially forces the value to UTC because it
converted directly to epoch-based numeric.
Thinking about this some more and "stepping back", the real problem is that
there are a few different timezones in play here that need to be balanced
and handled:
1. The database default TZ
2. The stored TZ
3. The JVM/JDBC TZ
4. The application TZ
The whole idea of "stored TZ" really depends on how the database/driver
treats TIMESTAMP and whether it supports a "TIMESTAMP_WITH_TIMEZONE"
type. I
personally think it is a huge mistake to save the specific TZ differences
to the DB, so I would personally continue to not support
TIMESTAMP_WITH_TIMEZONE
types. This would mean we never have to bind the Calendar because we could
simply convert the value to to the JVM/JDBC TZ ourselves. Specifically I
would suggest that we (continue to) assume that the driver has been set up
such that the same TZ is used when
1. when binding a Timestamp (without passing a Calendar)
2. reading a Timestamp (without passing a Calendar).
Specifically this would mean setting the JVM TZ, or for JDBC drivers that
support it setting the "default TZ" on the driver. If we start storing
values in the TZ specific to the Calendar we really have no idea how to
properly read those values back because we'd lose context to that TZ. For
that reason I suggest we drop passing along the Calender and instead
perform all these conversions in memory and pass the converted Timestamp to
JDBC. When reading back, that should mean the driver would return us
Timestamps relative to that same TZ.
The last piece is "application TZ" which would be an optional setting
indicating the TZ zone-based values should be returned in.
E.g., let's say a user maps a ZonedDataTime using @Temporal(TIMESTAMP). If
following my suggestions, when saving these values we would:
1. convert the ZonedDateTime to a ZonedDateTime in the "JVM/JDBC TZ" (if
different from the incoming ZonedDateTime's TZ)
2. use that to construct a Timestamp to bind to JDBC
When reading back these values, we would:
1. get back the Timestamp from JDBC
2. assume the value is relative to the "JVM/JDBC TZ" and build the
ZonedDateTime via `ZonedDateTime.ofInstant( timestamp.toInstant(),
jvmJdbcZoneId )`
3. convert that built ZonedDateTime to the application TZ:
`builtZonedDateTime.withZoneSameInstant( applicationZoneId )` - it
might
be possible to combine this step with the previous, not sure...
Some if this is type-specific. Assuming the suggestions above:
- Instant - inherently epoch-based.
- to convert this to a Timestamp for binding we would use
`Timestamp#from(Instant).
Because of the above assumptions, the driver would interpret
this relative
to JVM/JDBC TZ
- Reading back we simply use `Timestamp#toInstant()`
- LocalDateTime - is the relative idea of a date+time outside the
context of any specific TZ.
- When binding we would resolve this into the JVM/JDBC TZ (using
LocalDateTime#atZone(jvmJdbcZoneId), convert to an Instant and
create a
Timestamp and bind that Timestamp to JDBC.
- When reading back we'd get the Instant from Timestamp and use that
in `LocalDateTime#ofInstant` using the application TZ
- LocalDate - relative idea of a date outside of any specific TZ. Of
course this could also be mapped to a JDBC DATE which circumvents some
of
this, but mapped to a TIMESTAMP...
- When binding we'd convert to a LocalDateTime using
`LocalDate#atStartOfDay` and treat that LocalDateTime as above.
- When reading back we would create a LocalDateTime using
`LocalDateTime.ofInstant(timestamp.toInstant(),
applicationZoneId )`
- LocalTime - relative idea of a time outside any specific TZ. Could
also be mapped to a JDBC TIME...
- When binding we'd create a LocalDateTime using `LocalTime.atDate(
LocalDate.of( 1970, 1, 1 ) )`, converting that to a ZonedDateTime
using
`LocalDateTime#atZone( jvmJdbcZoneId )` and finally converting that
to a
Timestamp
- When reading we would call `LocalDateTime.ofInstant(
timestamp.toInstant(), applicationZoneId ).toLocalTime()`
- ZonedDateTime - like a Calendar bundles a date+time with a TZ, and so
is directly convertible to an epoch-based value (Instant, Timestamp,
etc).
- When binding we would convert this to an Instant and then to a
Timestamp
- When reading we would convert the Timestamp to an Instant and then
convert the Instant to a ZonedDateTime using
`ZonedDateTime.ofInstant(
timestamp.toInstant(), applicationZoneId )`
- OffsetDateTime - handled similar to ZonedDateTime.
- When binding we would convert the value to a ZonedDateTime using
`OffsetDateTime#atZoneSameInstant( jvmJdbcZoneId )` and then follows
ZonedDateTime
- When reading we would use `OffsetDateTime.ofInstant(
timestamp.toInstant(), applicationZoneId )`
- OffsetTime - atm we just drop the ZoneOffset
- for java.util.Date and java.sql.Timstamp mapping we have to decide
what those concepts model in regards to their value relative to a TZ.
Are
they a LocalDateTime relative to the system TimeZone? Are they a
LocalDateTime relative to UTC? At the moment how we handled these
"legacy
Java temporal types" ends up answering this question as them being "a
LocalDateTime relative to the system TimeZone".
- what about java.sql.Time? Is this a LocalTime? An OffsetTime
relative to UTC (+0)?
- and java.sql.Date?
Lot of moving parts when you actually start looking deep at "supporting
timezones".
[1]
http://stackoverflow.com/questions/18447995/postgresql-
9-2-jdbc-driver-uses-client-time-zone,
etc
On Wed, Aug 31, 2016 at 9:10 AM Vlad Mihalcea <mihalcea.vlad(a)gmail.com>
wrote:
> Hi,
>
> While reviewing the Pull Request for this issue:
> https://github.com/hibernate/hibernate-orm/pull/1536
>
> I realized that we can improve the default TimestampType as follows:
>
> 1. We could make it parameterizable so that it can also take a custom
> timezone (UTC) during mapping.
> 2. We could also define a default timezone so that we don't rely on the
JVM
> one. This might be desirable when the UI requires to display the time in
a
> certain timezone, while we want to save all timestamps in UTC.
>
> Let me know what you think.
>
> Vlad
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
9:13 a.m.
Hi,
For the moment, by adding a SF/Session-level configuration, we can at least
save Timestamps in UTC, which is indeed a very common scenario.
Later on, we can address the situation that you described in this email by
adding new types or introducing a new annotation to specify the secondary
field where the TZ info should be stored.
Vlad
On Thu, Sep 1, 2016 at 9:46 AM, Gunnar Morling <gunnar(a)hibernate.org> wrote:
> it is a huge mistake to save the specific TZ differences to the
DB
I once was working on a globally deployed shipping management application
where that was a requirement.
Say there is a shipment from Austin (one TZ) to London (another TZ). In
that application an operator based in Hamburg (yet another TZ) should be
able to know when the shipment was brought in in Austin (the local time in
Austin at that point) and when it was delivered in Hamburg (again the
local time there at that point).
So we stored all dates in UTC, but also the TZ (in a *different column*; I
don't quite remember whether it was just a string with the TZ (offset) or
again the entire date-time using TIMESTAMP_WITH_TZ). Storing it in UTC
allowed for comparisons whereas the other column allowed to show each date
in its local TZ. So an operator in Hamburg (at CET) could see a delivery
was brought in in Austin at 8:32 CST.
Something like that would still be my preference for handling TZ-bound
types such as ZonedDateTime: store the UTC timestamp in one column and the
TZ info (either id or offset from UTC) in another.
Relying on the JVM TZ or something like a single "application TZ" won't
cut it for cases like the one above: the operator in Hamburg wants to see
dates from Austin and London, using their respective time zones. Instead,
the TZ of the incoming ZonedDateTime would have to be persisted so it's
available when reading it back.
2016-08-31 20:51 GMT+02:00 Steve Ebersole <steve(a)hibernate.org>:
> We discussed this on HipChat, but for the benefit of all on this
> discussion...
>
> Part of this (the original report) speaks to a difference in how we map
> (org.hibernate.type.Type) java.time temporal versus a java.util temporal -
> specifically java.util.Calendar. When we are passed a Calendar (the model
> defines its attribute as Calendar) we pass along that Calendar reference
> to
> the JDBC driver which forces the driver to use that Calendar's TZ. For
> java.time temporal types, we always resolve them to UTC values mainly
> because of how we get to the java.sql.Timestamp value from the java.time
> value. For example, for ZonedDateTime we call:
>
> return (X) Timestamp.from( zonedDateTime.toInstant() );
>
> The call to #toInstant there essentially forces the value to UTC because
> it
> converted directly to epoch-based numeric.
>
> Thinking about this some more and "stepping back", the real problem is
> that
> there are a few different timezones in play here that need to be balanced
> and handled:
>
> 1. The database default TZ
> 2. The stored TZ
> 3. The JVM/JDBC TZ
> 4. The application TZ
>
> The whole idea of "stored TZ" really depends on how the database/driver
> treats TIMESTAMP and whether it supports a "TIMESTAMP_WITH_TIMEZONE"
> type. I
> personally think it is a huge mistake to save the specific TZ differences
> to the DB, so I would personally continue to not support
> TIMESTAMP_WITH_TIMEZONE
> types. This would mean we never have to bind the Calendar because we
> could
> simply convert the value to to the JVM/JDBC TZ ourselves. Specifically I
> would suggest that we (continue to) assume that the driver has been set up
> such that the same TZ is used when
>
> 1. when binding a Timestamp (without passing a Calendar)
> 2. reading a Timestamp (without passing a Calendar).
>
> Specifically this would mean setting the JVM TZ, or for JDBC drivers that
> support it setting the "default TZ" on the driver. If we start storing
> values in the TZ specific to the Calendar we really have no idea how to
> properly read those values back because we'd lose context to that TZ. For
> that reason I suggest we drop passing along the Calender and instead
> perform all these conversions in memory and pass the converted Timestamp
> to
> JDBC. When reading back, that should mean the driver would return us
> Timestamps relative to that same TZ.
>
> The last piece is "application TZ" which would be an optional setting
> indicating the TZ zone-based values should be returned in.
>
> E.g., let's say a user maps a ZonedDataTime using @Temporal(TIMESTAMP).
> If
> following my suggestions, when saving these values we would:
>
> 1. convert the ZonedDateTime to a ZonedDateTime in the "JVM/JDBC TZ"
> (if
> different from the incoming ZonedDateTime's TZ)
> 2. use that to construct a Timestamp to bind to JDBC
>
> When reading back these values, we would:
>
> 1. get back the Timestamp from JDBC
> 2. assume the value is relative to the "JVM/JDBC TZ" and build the
> ZonedDateTime via `ZonedDateTime.ofInstant( timestamp.toInstant(),
> jvmJdbcZoneId )`
> 3. convert that built ZonedDateTime to the application TZ:
> `builtZonedDateTime.withZoneSameInstant( applicationZoneId )` - it
> might
> be possible to combine this step with the previous, not sure...
>
>
> Some if this is type-specific. Assuming the suggestions above:
>
> - Instant - inherently epoch-based.
> - to convert this to a Timestamp for binding we would use
> `Timestamp#from(Instant).
> Because of the above assumptions, the driver would interpret
> this relative
> to JVM/JDBC TZ
> - Reading back we simply use `Timestamp#toInstant()`
> - LocalDateTime - is the relative idea of a date+time outside the
> context of any specific TZ.
> - When binding we would resolve this into the JVM/JDBC TZ (using
> LocalDateTime#atZone(jvmJdbcZoneId), convert to an Instant and
> create a
> Timestamp and bind that Timestamp to JDBC.
> - When reading back we'd get the Instant from Timestamp and use that
> in `LocalDateTime#ofInstant` using the application TZ
> - LocalDate - relative idea of a date outside of any specific TZ. Of
> course this could also be mapped to a JDBC DATE which circumvents some
> of
> this, but mapped to a TIMESTAMP...
> - When binding we'd convert to a LocalDateTime using
> `LocalDate#atStartOfDay` and treat that LocalDateTime as above.
> - When reading back we would create a LocalDateTime using
> `LocalDateTime.ofInstant(timestamp.toInstant(),
> applicationZoneId )`
> - LocalTime - relative idea of a time outside any specific TZ. Could
> also be mapped to a JDBC TIME...
> - When binding we'd create a LocalDateTime using `LocalTime.atDate(
> LocalDate.of( 1970, 1, 1 ) )`, converting that to a ZonedDateTime
> using
> `LocalDateTime#atZone( jvmJdbcZoneId )` and finally converting that
> to a
> Timestamp
> - When reading we would call `LocalDateTime.ofInstant(
> timestamp.toInstant(), applicationZoneId ).toLocalTime()`
> - ZonedDateTime - like a Calendar bundles a date+time with a TZ, and so
> is directly convertible to an epoch-based value (Instant, Timestamp,
> etc).
> - When binding we would convert this to an Instant and then to a
> Timestamp
> - When reading we would convert the Timestamp to an Instant and then
> convert the Instant to a ZonedDateTime using
> `ZonedDateTime.ofInstant(
> timestamp.toInstant(), applicationZoneId )`
> - OffsetDateTime - handled similar to ZonedDateTime.
> - When binding we would convert the value to a ZonedDateTime using
> `OffsetDateTime#atZoneSameInstant( jvmJdbcZoneId )` and then
> follows
> ZonedDateTime
> - When reading we would use `OffsetDateTime.ofInstant(
> timestamp.toInstant(), applicationZoneId )`
> - OffsetTime - atm we just drop the ZoneOffset
> - for java.util.Date and java.sql.Timstamp mapping we have to decide
> what those concepts model in regards to their value relative to a TZ.
> Are
> they a LocalDateTime relative to the system TimeZone? Are they a
> LocalDateTime relative to UTC? At the moment how we handled these
> "legacy
> Java temporal types" ends up answering this question as them being "a
> LocalDateTime relative to the system TimeZone".
> - what about java.sql.Time? Is this a LocalTime? An OffsetTime
> relative to UTC (+0)?
> - and java.sql.Date?
>
>
> Lot of moving parts when you actually start looking deep at "supporting
> timezones".
>
>
> [1]
> http://stackoverflow.com/questions/18447995/postgresql-9-2-
> jdbc-driver-uses-client-time-zone,
> etc
>
> On Wed, Aug 31, 2016 at 9:10 AM Vlad Mihalcea <mihalcea.vlad(a)gmail.com>
> wrote:
>
> > Hi,
> >
> > While reviewing the Pull Request for this issue:
> > https://github.com/hibernate/hibernate-orm/pull/1536
> >
> > I realized that we can improve the default TimestampType as follows:
> >
> > 1. We could make it parameterizable so that it can also take a custom
> > timezone (UTC) during mapping.
> > 2. We could also define a default timezone so that we don't rely on the
> JVM
> > one. This might be desirable when the UI requires to display the time
> in a
> > certain timezone, while we want to save all timestamps in UTC.
> >
> > Let me know what you think.
> >
> > Vlad
> > _______________________________________________
> > hibernate-dev mailing list
> > hibernate-dev(a)lists.jboss.org
> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
> >
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
11:59 a.m.
To be fair I would have mapped that differently.
Your Shipment arrives at a certain *point in time* (long, UTC based)
in a certain Port (mapped with Foreign Key).
A Port would have a reference to a local Timezone, and UI could decide
to use that (or a different one) for rendering, based on other
aspects, like the location of the user actually looking at the UI, or
even a switchable user option.
Saving in "timezoned" instants is prone to several bugs: you're then
exposed to ambiguities as time doesn't flow linearly in all zones.
Storing a timezoned instant only makes sense when you're quoting a
political/legal source; i.e. you'ave passed under OCR some legal
paperwork and you need to store the date printed on the header in a
dedicated field, in Java8 time type you'd never use an Instant for
this though, there are dedicated types which make the limitations of
this representation clear. AFAIR some kinds of comparisons are made
impossible.
I'd not spend time with improvements on Calendar: the "old Date types"
should be deprecated.
On 1 September 2016 at 07:46, Gunnar Morling <gunnar(a)hibernate.org> wrote:
> it is a huge mistake to save the specific TZ differences to the
DB
I once was working on a globally deployed shipping management application
where that was a requirement.
Say there is a shipment from Austin (one TZ) to London (another TZ). In
that application an operator based in Hamburg (yet another TZ) should be
able to know when the shipment was brought in in Austin (the local time in
Austin at that point) and when it was delivered in Hamburg (again the local
time there at that point).
So we stored all dates in UTC, but also the TZ (in a *different column*; I
don't quite remember whether it was just a string with the TZ (offset) or
again the entire date-time using TIMESTAMP_WITH_TZ). Storing it in UTC
allowed for comparisons whereas the other column allowed to show each date
in its local TZ. So an operator in Hamburg (at CET) could see a delivery
was brought in in Austin at 8:32 CST.
Something like that would still be my preference for handling TZ-bound
types such as ZonedDateTime: store the UTC timestamp in one column and the
TZ info (either id or offset from UTC) in another.
Relying on the JVM TZ or something like a single "application TZ" won't
cut
it for cases like the one above: the operator in Hamburg wants to see dates
from Austin and London, using their respective time zones. Instead, the TZ
of the incoming ZonedDateTime would have to be persisted so it's available
when reading it back.
2016-08-31 20:51 GMT+02:00 Steve Ebersole <steve(a)hibernate.org>:
> We discussed this on HipChat, but for the benefit of all on this
> discussion...
>
> Part of this (the original report) speaks to a difference in how we map
> (org.hibernate.type.Type) java.time temporal versus a java.util temporal -
> specifically java.util.Calendar. When we are passed a Calendar (the model
> defines its attribute as Calendar) we pass along that Calendar reference to
> the JDBC driver which forces the driver to use that Calendar's TZ. For
> java.time temporal types, we always resolve them to UTC values mainly
> because of how we get to the java.sql.Timestamp value from the java.time
> value. For example, for ZonedDateTime we call:
>
> return (X) Timestamp.from( zonedDateTime.toInstant() );
>
> The call to #toInstant there essentially forces the value to UTC because it
> converted directly to epoch-based numeric.
>
> Thinking about this some more and "stepping back", the real problem is
that
> there are a few different timezones in play here that need to be balanced
> and handled:
>
> 1. The database default TZ
> 2. The stored TZ
> 3. The JVM/JDBC TZ
> 4. The application TZ
>
> The whole idea of "stored TZ" really depends on how the database/driver
> treats TIMESTAMP and whether it supports a "TIMESTAMP_WITH_TIMEZONE"
> type. I
> personally think it is a huge mistake to save the specific TZ differences
> to the DB, so I would personally continue to not support
> TIMESTAMP_WITH_TIMEZONE
> types. This would mean we never have to bind the Calendar because we could
> simply convert the value to to the JVM/JDBC TZ ourselves. Specifically I
> would suggest that we (continue to) assume that the driver has been set up
> such that the same TZ is used when
>
> 1. when binding a Timestamp (without passing a Calendar)
> 2. reading a Timestamp (without passing a Calendar).
>
> Specifically this would mean setting the JVM TZ, or for JDBC drivers that
> support it setting the "default TZ" on the driver. If we start storing
> values in the TZ specific to the Calendar we really have no idea how to
> properly read those values back because we'd lose context to that TZ. For
> that reason I suggest we drop passing along the Calender and instead
> perform all these conversions in memory and pass the converted Timestamp to
> JDBC. When reading back, that should mean the driver would return us
> Timestamps relative to that same TZ.
>
> The last piece is "application TZ" which would be an optional setting
> indicating the TZ zone-based values should be returned in.
>
> E.g., let's say a user maps a ZonedDataTime using @Temporal(TIMESTAMP). If
> following my suggestions, when saving these values we would:
>
> 1. convert the ZonedDateTime to a ZonedDateTime in the "JVM/JDBC TZ"
(if
> different from the incoming ZonedDateTime's TZ)
> 2. use that to construct a Timestamp to bind to JDBC
>
> When reading back these values, we would:
>
> 1. get back the Timestamp from JDBC
> 2. assume the value is relative to the "JVM/JDBC TZ" and build the
> ZonedDateTime via `ZonedDateTime.ofInstant( timestamp.toInstant(),
> jvmJdbcZoneId )`
> 3. convert that built ZonedDateTime to the application TZ:
> `builtZonedDateTime.withZoneSameInstant( applicationZoneId )` - it
> might
> be possible to combine this step with the previous, not sure...
>
>
> Some if this is type-specific. Assuming the suggestions above:
>
> - Instant - inherently epoch-based.
> - to convert this to a Timestamp for binding we would use
> `Timestamp#from(Instant).
> Because of the above assumptions, the driver would interpret
> this relative
> to JVM/JDBC TZ
> - Reading back we simply use `Timestamp#toInstant()`
> - LocalDateTime - is the relative idea of a date+time outside the
> context of any specific TZ.
> - When binding we would resolve this into the JVM/JDBC TZ (using
> LocalDateTime#atZone(jvmJdbcZoneId), convert to an Instant and
> create a
> Timestamp and bind that Timestamp to JDBC.
> - When reading back we'd get the Instant from Timestamp and use that
> in `LocalDateTime#ofInstant` using the application TZ
> - LocalDate - relative idea of a date outside of any specific TZ. Of
> course this could also be mapped to a JDBC DATE which circumvents some
> of
> this, but mapped to a TIMESTAMP...
> - When binding we'd convert to a LocalDateTime using
> `LocalDate#atStartOfDay` and treat that LocalDateTime as above.
> - When reading back we would create a LocalDateTime using
> `LocalDateTime.ofInstant(timestamp.toInstant(),
> applicationZoneId )`
> - LocalTime - relative idea of a time outside any specific TZ. Could
> also be mapped to a JDBC TIME...
> - When binding we'd create a LocalDateTime using `LocalTime.atDate(
> LocalDate.of( 1970, 1, 1 ) )`, converting that to a ZonedDateTime
> using
> `LocalDateTime#atZone( jvmJdbcZoneId )` and finally converting that
> to a
> Timestamp
> - When reading we would call `LocalDateTime.ofInstant(
> timestamp.toInstant(), applicationZoneId ).toLocalTime()`
> - ZonedDateTime - like a Calendar bundles a date+time with a TZ, and so
> is directly convertible to an epoch-based value (Instant, Timestamp,
> etc).
> - When binding we would convert this to an Instant and then to a
> Timestamp
> - When reading we would convert the Timestamp to an Instant and then
> convert the Instant to a ZonedDateTime using
> `ZonedDateTime.ofInstant(
> timestamp.toInstant(), applicationZoneId )`
> - OffsetDateTime - handled similar to ZonedDateTime.
> - When binding we would convert the value to a ZonedDateTime using
> `OffsetDateTime#atZoneSameInstant( jvmJdbcZoneId )` and then follows
> ZonedDateTime
> - When reading we would use `OffsetDateTime.ofInstant(
> timestamp.toInstant(), applicationZoneId )`
> - OffsetTime - atm we just drop the ZoneOffset
> - for java.util.Date and java.sql.Timstamp mapping we have to decide
> what those concepts model in regards to their value relative to a TZ.
> Are
> they a LocalDateTime relative to the system TimeZone? Are they a
> LocalDateTime relative to UTC? At the moment how we handled these
> "legacy
> Java temporal types" ends up answering this question as them being "a
> LocalDateTime relative to the system TimeZone".
> - what about java.sql.Time? Is this a LocalTime? An OffsetTime
> relative to UTC (+0)?
> - and java.sql.Date?
>
> Lot of moving parts when you actually start looking deep at "supporting
> timezones".
>
>
> [1]
> http://stackoverflow.com/questions/18447995/postgresql-
> 9-2-jdbc-driver-uses-client-time-zone,
> etc
>
> On Wed, Aug 31, 2016 at 9:10 AM Vlad Mihalcea <mihalcea.vlad(a)gmail.com>
> wrote:
>
> > Hi,
> >
> > While reviewing the Pull Request for this issue:
> > https://github.com/hibernate/hibernate-orm/pull/1536
> >
> > I realized that we can improve the default TimestampType as follows:
> >
> > 1. We could make it parameterizable so that it can also take a custom
> > timezone (UTC) during mapping.
> > 2. We could also define a default timezone so that we don't rely on the
> JVM
> > one. This might be desirable when the UI requires to display the time in
> a
> > certain timezone, while we want to save all timestamps in UTC.
> >
> > Let me know what you think.
> >
> > Vlad
> > _______________________________________________
> > hibernate-dev mailing list
> > hibernate-dev(a)lists.jboss.org
> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
> >
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
12:13 p.m.
Saving in "timezoned" instants is prone to several bugs:
you're then
exposed to ambiguities as time doesn't flow linearly in all zones.
I see it the other way around: the indirection you suggest is prone to
errors.
Rules about TZ constantly change (e.g. when switching to/from DST),
locations change time zones more often than one would expect. Saving the
then valid TZ "next to" an UTC timestamp is much easier and more reliable
then trying to reconstruct that info from some historical TZ DB.
2016-09-01 11:59 GMT+02:00 Sanne Grinovero <sanne(a)hibernate.org>:
> To be fair I would have mapped that differently.
> Your Shipment arrives at a certain *point in time* (long, UTC based)
> in a certain Port (mapped with Foreign Key).
> A Port would have a reference to a local Timezone, and UI could decide
> to use that (or a different one) for rendering, based on other
> aspects, like the location of the user actually looking at the UI, or
> even a switchable user option.
>
Saving in "timezoned" instants is prone to several bugs:
you're then
exposed to ambiguities as time doesn't flow linearly in all zones.
>
> Storing a timezoned instant only makes sense when you're quoting a
> political/legal source; i.e. you'ave passed under OCR some legal
> paperwork and you need to store the date printed on the header in a
> dedicated field, in Java8 time type you'd never use an Instant for
> this though, there are dedicated types which make the limitations of
> this representation clear. AFAIR some kinds of comparisons are made
> impossible.
>
> I'd not spend time with improvements on Calendar: the "old Date types"
> should be deprecated.
>
>
> On 1 September 2016 at 07:46, Gunnar Morling <gunnar(a)hibernate.org> wrote:
> >> it is a huge mistake to save the specific TZ differences to the DB
> >
> > I once was working on a globally deployed shipping management application
> > where that was a requirement.
> >
> > Say there is a shipment from Austin (one TZ) to London (another TZ). In
> > that application an operator based in Hamburg (yet another TZ) should be
> > able to know when the shipment was brought in in Austin (the local time
> in
> > Austin at that point) and when it was delivered in Hamburg (again the
> local
> > time there at that point).
> >
> > So we stored all dates in UTC, but also the TZ (in a *different column*;
> I
> > don't quite remember whether it was just a string with the TZ (offset) or
> > again the entire date-time using TIMESTAMP_WITH_TZ). Storing it in UTC
> > allowed for comparisons whereas the other column allowed to show each
> date
> > in its local TZ. So an operator in Hamburg (at CET) could see a delivery
> > was brought in in Austin at 8:32 CST.
> >
> > Something like that would still be my preference for handling TZ-bound
> > types such as ZonedDateTime: store the UTC timestamp in one column and
> the
> > TZ info (either id or offset from UTC) in another.
> >
> > Relying on the JVM TZ or something like a single "application TZ"
won't
> cut
> > it for cases like the one above: the operator in Hamburg wants to see
> dates
> > from Austin and London, using their respective time zones. Instead, the
> TZ
> > of the incoming ZonedDateTime would have to be persisted so it's
> available
> > when reading it back.
> >
> >
> >
> >
> > 2016-08-31 20:51 GMT+02:00 Steve Ebersole <steve(a)hibernate.org>:
> >
> >> We discussed this on HipChat, but for the benefit of all on this
> >> discussion...
> >>
> >> Part of this (the original report) speaks to a difference in how we map
> >> (org.hibernate.type.Type) java.time temporal versus a java.util
> temporal -
> >> specifically java.util.Calendar. When we are passed a Calendar (the
> model
> >> defines its attribute as Calendar) we pass along that Calendar
> reference to
> >> the JDBC driver which forces the driver to use that Calendar's TZ. For
> >> java.time temporal types, we always resolve them to UTC values mainly
> >> because of how we get to the java.sql.Timestamp value from the java.time
> >> value. For example, for ZonedDateTime we call:
> >>
> >> return (X) Timestamp.from( zonedDateTime.toInstant() );
> >>
> >> The call to #toInstant there essentially forces the value to UTC
> because it
> >> converted directly to epoch-based numeric.
> >>
> >> Thinking about this some more and "stepping back", the real
problem is
> that
> >> there are a few different timezones in play here that need to be
> balanced
> >> and handled:
> >>
> >> 1. The database default TZ
> >> 2. The stored TZ
> >> 3. The JVM/JDBC TZ
> >> 4. The application TZ
> >>
> >> The whole idea of "stored TZ" really depends on how the
database/driver
> >> treats TIMESTAMP and whether it supports a
"TIMESTAMP_WITH_TIMEZONE"
> >> type. I
> >> personally think it is a huge mistake to save the specific TZ
> differences
> >> to the DB, so I would personally continue to not support
> >> TIMESTAMP_WITH_TIMEZONE
> >> types. This would mean we never have to bind the Calendar because we
> could
> >> simply convert the value to to the JVM/JDBC TZ ourselves. Specifically
> I
> >> would suggest that we (continue to) assume that the driver has been set
> up
> >> such that the same TZ is used when
> >>
> >> 1. when binding a Timestamp (without passing a Calendar)
> >> 2. reading a Timestamp (without passing a Calendar).
> >>
> >> Specifically this would mean setting the JVM TZ, or for JDBC drivers
> that
> >> support it setting the "default TZ" on the driver. If we start
storing
> >> values in the TZ specific to the Calendar we really have no idea how to
> >> properly read those values back because we'd lose context to that TZ.
> For
> >> that reason I suggest we drop passing along the Calender and instead
> >> perform all these conversions in memory and pass the converted
> Timestamp to
> >> JDBC. When reading back, that should mean the driver would return us
> >> Timestamps relative to that same TZ.
> >>
> >> The last piece is "application TZ" which would be an optional
setting
> >> indicating the TZ zone-based values should be returned in.
> >>
> >> E.g., let's say a user maps a ZonedDataTime using
> @Temporal(TIMESTAMP). If
> >> following my suggestions, when saving these values we would:
> >>
> >> 1. convert the ZonedDateTime to a ZonedDateTime in the "JVM/JDBC
TZ"
> (if
> >> different from the incoming ZonedDateTime's TZ)
> >> 2. use that to construct a Timestamp to bind to JDBC
> >>
> >> When reading back these values, we would:
> >>
> >> 1. get back the Timestamp from JDBC
> >> 2. assume the value is relative to the "JVM/JDBC TZ" and build
the
> >> ZonedDateTime via `ZonedDateTime.ofInstant( timestamp.toInstant(),
> >> jvmJdbcZoneId )`
> >> 3. convert that built ZonedDateTime to the application TZ:
> >> `builtZonedDateTime.withZoneSameInstant( applicationZoneId )` - it
> >> might
> >> be possible to combine this step with the previous, not sure...
> >>
> >>
> >> Some if this is type-specific. Assuming the suggestions above:
> >>
> >> - Instant - inherently epoch-based.
> >> - to convert this to a Timestamp for binding we would use
> >> `Timestamp#from(Instant).
> >> Because of the above assumptions, the driver would interpret
> >> this relative
> >> to JVM/JDBC TZ
> >> - Reading back we simply use `Timestamp#toInstant()`
> >> - LocalDateTime - is the relative idea of a date+time outside the
> >> context of any specific TZ.
> >> - When binding we would resolve this into the JVM/JDBC TZ (using
> >> LocalDateTime#atZone(jvmJdbcZoneId), convert to an Instant and
> >> create a
> >> Timestamp and bind that Timestamp to JDBC.
> >> - When reading back we'd get the Instant from Timestamp and use
> that
> >> in `LocalDateTime#ofInstant` using the application TZ
> >> - LocalDate - relative idea of a date outside of any specific TZ. Of
> >> course this could also be mapped to a JDBC DATE which circumvents
> some
> >> of
> >> this, but mapped to a TIMESTAMP...
> >> - When binding we'd convert to a LocalDateTime using
> >> `LocalDate#atStartOfDay` and treat that LocalDateTime as above.
> >> - When reading back we would create a LocalDateTime using
> >> `LocalDateTime.ofInstant(timestamp.toInstant(),
> >> applicationZoneId )`
> >> - LocalTime - relative idea of a time outside any specific TZ. Could
> >> also be mapped to a JDBC TIME...
> >> - When binding we'd create a LocalDateTime using
> `LocalTime.atDate(
> >> LocalDate.of( 1970, 1, 1 ) )`, converting that to a ZonedDateTime
> >> using
> >> `LocalDateTime#atZone( jvmJdbcZoneId )` and finally converting
> that
> >> to a
> >> Timestamp
> >> - When reading we would call `LocalDateTime.ofInstant(
> >> timestamp.toInstant(), applicationZoneId ).toLocalTime()`
> >> - ZonedDateTime - like a Calendar bundles a date+time with a TZ, and
> so
> >> is directly convertible to an epoch-based value (Instant, Timestamp,
> >> etc).
> >> - When binding we would convert this to an Instant and then to a
> >> Timestamp
> >> - When reading we would convert the Timestamp to an Instant and
> then
> >> convert the Instant to a ZonedDateTime using
> >> `ZonedDateTime.ofInstant(
> >> timestamp.toInstant(), applicationZoneId )`
> >> - OffsetDateTime - handled similar to ZonedDateTime.
> >> - When binding we would convert the value to a ZonedDateTime using
> >> `OffsetDateTime#atZoneSameInstant( jvmJdbcZoneId )` and then
> follows
> >> ZonedDateTime
> >> - When reading we would use `OffsetDateTime.ofInstant(
> >> timestamp.toInstant(), applicationZoneId )`
> >> - OffsetTime - atm we just drop the ZoneOffset
> >> - for java.util.Date and java.sql.Timstamp mapping we have to decide
> >> what those concepts model in regards to their value relative to a TZ.
> >> Are
> >> they a LocalDateTime relative to the system TimeZone? Are they a
> >> LocalDateTime relative to UTC? At the moment how we handled these
> >> "legacy
> >> Java temporal types" ends up answering this question as them being
"a
> >> LocalDateTime relative to the system TimeZone".
> >> - what about java.sql.Time? Is this a LocalTime? An OffsetTime
> >> relative to UTC (+0)?
> >> - and java.sql.Date?
> >>
> >> Lot of moving parts when you actually start looking deep at
"supporting
> >> timezones".
> >>
> >>
> >> [1]
> >> http://stackoverflow.com/questions/18447995/postgresql-
> >> 9-2-jdbc-driver-uses-client-time-zone,
> >> etc
> >>
> >> On Wed, Aug 31, 2016 at 9:10 AM Vlad Mihalcea
<mihalcea.vlad(a)gmail.com>
> >> wrote:
> >>
> >> > Hi,
> >> >
> >> > While reviewing the Pull Request for this issue:
> >> > https://github.com/hibernate/hibernate-orm/pull/1536
> >> >
> >> > I realized that we can improve the default TimestampType as follows:
> >> >
> >> > 1. We could make it parameterizable so that it can also take a custom
> >> > timezone (UTC) during mapping.
> >> > 2. We could also define a default timezone so that we don't rely
on
> the
> >> JVM
> >> > one. This might be desirable when the UI requires to display the time
> in
> >> a
> >> > certain timezone, while we want to save all timestamps in UTC.
> >> >
> >> > Let me know what you think.
> >> >
> >> > Vlad
> >> > _______________________________________________
> >> > hibernate-dev mailing list
> >> > hibernate-dev(a)lists.jboss.org
> >> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
> >> >
> >> _______________________________________________
> >> hibernate-dev mailing list
> >> hibernate-dev(a)lists.jboss.org
> >> https://lists.jboss.org/mailman/listinfo/hibernate-dev
> >>
> > _______________________________________________
> > hibernate-dev mailing list
> > hibernate-dev(a)lists.jboss.org
> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
12:52 p.m.
I think your example is actually a perfect example of when to not store the
TZ.
On Thu, Sep 1, 2016, 5:16 AM Gunnar Morling <gunnar(a)hibernate.org> wrote:
> Saving in "timezoned" instants is prone to several
bugs: you're then
> exposed to ambiguities as time doesn't flow linearly in all zones.
I see it the other way around: the indirection you suggest is prone to
errors.
Rules about TZ constantly change (e.g. when switching to/from DST),
locations change time zones more often than one would expect. Saving the
then valid TZ "next to" an UTC timestamp is much easier and more reliable
then trying to reconstruct that info from some historical TZ DB.
2016-09-01 11:59 GMT+02:00 Sanne Grinovero <sanne(a)hibernate.org>:
> To be fair I would have mapped that differently.
> Your Shipment arrives at a certain *point in time* (long, UTC based)
> in a certain Port (mapped with Foreign Key).
> A Port would have a reference to a local Timezone, and UI could decide
> to use that (or a different one) for rendering, based on other
> aspects, like the location of the user actually looking at the UI, or
> even a switchable user option.
>
> Saving in "timezoned" instants is prone to several bugs: you're then
> exposed to ambiguities as time doesn't flow linearly in all zones.
>
> Storing a timezoned instant only makes sense when you're quoting a
> political/legal source; i.e. you'ave passed under OCR some legal
> paperwork and you need to store the date printed on the header in a
> dedicated field, in Java8 time type you'd never use an Instant for
> this though, there are dedicated types which make the limitations of
> this representation clear. AFAIR some kinds of comparisons are made
> impossible.
>
> I'd not spend time with improvements on Calendar: the "old Date
types"
> should be deprecated.
>
>
> On 1 September 2016 at 07:46, Gunnar Morling <gunnar(a)hibernate.org>
wrote:
> >> it is a huge mistake to save the specific TZ differences to the DB
> >
> > I once was working on a globally deployed shipping management
application
> > where that was a requirement.
> >
> > Say there is a shipment from Austin (one TZ) to London (another TZ). In
> > that application an operator based in Hamburg (yet another TZ) should
be
> > able to know when the shipment was brought in in Austin (the local time
> in
> > Austin at that point) and when it was delivered in Hamburg (again the
> local
> > time there at that point).
> >
> > So we stored all dates in UTC, but also the TZ (in a *different
column*;
> I
> > don't quite remember whether it was just a string with the TZ (offset)
or
> > again the entire date-time using TIMESTAMP_WITH_TZ). Storing it in UTC
> > allowed for comparisons whereas the other column allowed to show each
> date
> > in its local TZ. So an operator in Hamburg (at CET) could see a
delivery
> > was brought in in Austin at 8:32 CST.
> >
> > Something like that would still be my preference for handling TZ-bound
> > types such as ZonedDateTime: store the UTC timestamp in one column and
> the
> > TZ info (either id or offset from UTC) in another.
> >
> > Relying on the JVM TZ or something like a single "application TZ"
won't
> cut
> > it for cases like the one above: the operator in Hamburg wants to see
> dates
> > from Austin and London, using their respective time zones. Instead, the
> TZ
> > of the incoming ZonedDateTime would have to be persisted so it's
> available
> > when reading it back.
> >
> >
> >
> >
> > 2016-08-31 20:51 GMT+02:00 Steve Ebersole <steve(a)hibernate.org>:
> >
> >> We discussed this on HipChat, but for the benefit of all on this
> >> discussion...
> >>
> >> Part of this (the original report) speaks to a difference in how we
map
> >> (org.hibernate.type.Type) java.time temporal versus a java.util
> temporal -
> >> specifically java.util.Calendar. When we are passed a Calendar (the
> model
> >> defines its attribute as Calendar) we pass along that Calendar
> reference to
> >> the JDBC driver which forces the driver to use that Calendar's TZ.
For
> >> java.time temporal types, we always resolve them to UTC values mainly
> >> because of how we get to the java.sql.Timestamp value from the
java.time
> >> value. For example, for ZonedDateTime we call:
> >>
> >> return (X) Timestamp.from( zonedDateTime.toInstant() );
> >>
> >> The call to #toInstant there essentially forces the value to UTC
> because it
> >> converted directly to epoch-based numeric.
> >>
> >> Thinking about this some more and "stepping back", the real
problem is
> that
> >> there are a few different timezones in play here that need to be
> balanced
> >> and handled:
> >>
> >> 1. The database default TZ
> >> 2. The stored TZ
> >> 3. The JVM/JDBC TZ
> >> 4. The application TZ
> >>
> >> The whole idea of "stored TZ" really depends on how the
database/driver
> >> treats TIMESTAMP and whether it supports a
"TIMESTAMP_WITH_TIMEZONE"
> >> type. I
> >> personally think it is a huge mistake to save the specific TZ
> differences
> >> to the DB, so I would personally continue to not support
> >> TIMESTAMP_WITH_TIMEZONE
> >> types. This would mean we never have to bind the Calendar because we
> could
> >> simply convert the value to to the JVM/JDBC TZ ourselves.
Specifically
> I
> >> would suggest that we (continue to) assume that the driver has been
set
> up
> >> such that the same TZ is used when
> >>
> >> 1. when binding a Timestamp (without passing a Calendar)
> >> 2. reading a Timestamp (without passing a Calendar).
> >>
> >> Specifically this would mean setting the JVM TZ, or for JDBC drivers
> that
> >> support it setting the "default TZ" on the driver. If we start
storing
> >> values in the TZ specific to the Calendar we really have no idea how
to
> >> properly read those values back because we'd lose context to that TZ.
> For
> >> that reason I suggest we drop passing along the Calender and instead
> >> perform all these conversions in memory and pass the converted
> Timestamp to
> >> JDBC. When reading back, that should mean the driver would return us
> >> Timestamps relative to that same TZ.
> >>
> >> The last piece is "application TZ" which would be an optional
setting
> >> indicating the TZ zone-based values should be returned in.
> >>
> >> E.g., let's say a user maps a ZonedDataTime using
> @Temporal(TIMESTAMP). If
> >> following my suggestions, when saving these values we would:
> >>
> >> 1. convert the ZonedDateTime to a ZonedDateTime in the "JVM/JDBC
TZ"
> (if
> >> different from the incoming ZonedDateTime's TZ)
> >> 2. use that to construct a Timestamp to bind to JDBC
> >>
> >> When reading back these values, we would:
> >>
> >> 1. get back the Timestamp from JDBC
> >> 2. assume the value is relative to the "JVM/JDBC TZ" and build
the
> >> ZonedDateTime via `ZonedDateTime.ofInstant( timestamp.toInstant(),
> >> jvmJdbcZoneId )`
> >> 3. convert that built ZonedDateTime to the application TZ:
> >> `builtZonedDateTime.withZoneSameInstant( applicationZoneId )` - it
> >> might
> >> be possible to combine this step with the previous, not sure...
> >>
> >>
> >> Some if this is type-specific. Assuming the suggestions above:
> >>
> >> - Instant - inherently epoch-based.
> >> - to convert this to a Timestamp for binding we would use
> >> `Timestamp#from(Instant).
> >> Because of the above assumptions, the driver would interpret
> >> this relative
> >> to JVM/JDBC TZ
> >> - Reading back we simply use `Timestamp#toInstant()`
> >> - LocalDateTime - is the relative idea of a date+time outside the
> >> context of any specific TZ.
> >> - When binding we would resolve this into the JVM/JDBC TZ (using
> >> LocalDateTime#atZone(jvmJdbcZoneId), convert to an Instant and
> >> create a
> >> Timestamp and bind that Timestamp to JDBC.
> >> - When reading back we'd get the Instant from Timestamp and use
> that
> >> in `LocalDateTime#ofInstant` using the application TZ
> >> - LocalDate - relative idea of a date outside of any specific TZ.
Of
> >> course this could also be mapped to a JDBC DATE which circumvents
> some
> >> of
> >> this, but mapped to a TIMESTAMP...
> >> - When binding we'd convert to a LocalDateTime using
> >> `LocalDate#atStartOfDay` and treat that LocalDateTime as above.
> >> - When reading back we would create a LocalDateTime using
> >> `LocalDateTime.ofInstant(timestamp.toInstant(),
> >> applicationZoneId )`
> >> - LocalTime - relative idea of a time outside any specific TZ.
Could
> >> also be mapped to a JDBC TIME...
> >> - When binding we'd create a LocalDateTime using
> `LocalTime.atDate(
> >> LocalDate.of( 1970, 1, 1 ) )`, converting that to a
ZonedDateTime
> >> using
> >> `LocalDateTime#atZone( jvmJdbcZoneId )` and finally converting
> that
> >> to a
> >> Timestamp
> >> - When reading we would call `LocalDateTime.ofInstant(
> >> timestamp.toInstant(), applicationZoneId ).toLocalTime()`
> >> - ZonedDateTime - like a Calendar bundles a date+time with a TZ,
and
> so
> >> is directly convertible to an epoch-based value (Instant,
Timestamp,
> >> etc).
> >> - When binding we would convert this to an Instant and then to a
> >> Timestamp
> >> - When reading we would convert the Timestamp to an Instant and
> then
> >> convert the Instant to a ZonedDateTime using
> >> `ZonedDateTime.ofInstant(
> >> timestamp.toInstant(), applicationZoneId )`
> >> - OffsetDateTime - handled similar to ZonedDateTime.
> >> - When binding we would convert the value to a ZonedDateTime
using
> >> `OffsetDateTime#atZoneSameInstant( jvmJdbcZoneId )` and then
> follows
> >> ZonedDateTime
> >> - When reading we would use `OffsetDateTime.ofInstant(
> >> timestamp.toInstant(), applicationZoneId )`
> >> - OffsetTime - atm we just drop the ZoneOffset
> >> - for java.util.Date and java.sql.Timstamp mapping we have to
decide
> >> what those concepts model in regards to their value relative to a
TZ.
> >> Are
> >> they a LocalDateTime relative to the system TimeZone? Are they a
> >> LocalDateTime relative to UTC? At the moment how we handled these
> >> "legacy
> >> Java temporal types" ends up answering this question as them being
"a
> >> LocalDateTime relative to the system TimeZone".
> >> - what about java.sql.Time? Is this a LocalTime? An OffsetTime
> >> relative to UTC (+0)?
> >> - and java.sql.Date?
> >>
> >> Lot of moving parts when you actually start looking deep at
"supporting
> >> timezones".
> >>
> >>
> >> [1]
> >> http://stackoverflow.com/questions/18447995/postgresql-
> >> 9-2-jdbc-driver-uses-client-time-zone,
> >> etc
> >>
> >> On Wed, Aug 31, 2016 at 9:10 AM Vlad Mihalcea <
mihalcea.vlad(a)gmail.com>
> >> wrote:
> >>
> >> > Hi,
> >> >
> >> > While reviewing the Pull Request for this issue:
> >> > https://github.com/hibernate/hibernate-orm/pull/1536
> >> >
> >> > I realized that we can improve the default TimestampType as follows:
> >> >
> >> > 1. We could make it parameterizable so that it can also take a
custom
> >> > timezone (UTC) during mapping.
> >> > 2. We could also define a default timezone so that we don't rely
on
> the
> >> JVM
> >> > one. This might be desirable when the UI requires to display the
time
> in
> >> a
> >> > certain timezone, while we want to save all timestamps in UTC.
> >> >
> >> > Let me know what you think.
> >> >
> >> > Vlad
> >> > _______________________________________________
> >> > hibernate-dev mailing list
> >> > hibernate-dev(a)lists.jboss.org
> >> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
> >> >
> >> _______________________________________________
> >> hibernate-dev mailing list
> >> hibernate-dev(a)lists.jboss.org
> >> https://lists.jboss.org/mailman/listinfo/hibernate-dev
> >>
> > _______________________________________________
> > hibernate-dev mailing list
> > hibernate-dev(a)lists.jboss.org
> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
1:13 p.m.
Hit send accidentally...
The "problem" is normalization. You are storing a TZ that is specific to
the place (Austin, London) a shipment event occurs on the shipment event.
So even if your app had the requirment to display the shipment event in the
local TZ that that event occurred in, data-normalization says you'd store
the TZ/offset on the place and use that to render the event.
But even then, I'd personally never do that. I think rendering the
shipping event Instant in the TZ of place it happens is confusing and not
the norm anyway. I think the norm, for apps that want to render this, is
more a TZ relative to the the user. When I go to Amazon, or FedEx, etc to
track a package I could care less about the London TZ. The whole idea of a
unified "period" would mean that I see them in my local TZ (if not UTC).
Also... this will *not* involve 2 columns. We covered why in the last
email discussion we had about this a few months ago. Now your app could do
all this... map the @Temporal and then map the TZ and apply them in
memory.
On Thu, Sep 1, 2016 at 5:52 AM Steve Ebersole <steve(a)hibernate.org> wrote:
I think your example is actually a perfect example of when to not
store
the TZ.
On Thu, Sep 1, 2016, 5:16 AM Gunnar Morling <gunnar(a)hibernate.org> wrote:
> > Saving in "timezoned" instants is prone to several bugs: you're
then
> > exposed to ambiguities as time doesn't flow linearly in all zones.
>
> I see it the other way around: the indirection you suggest is prone to
> errors.
>
> Rules about TZ constantly change (e.g. when switching to/from DST),
> locations change time zones more often than one would expect. Saving the
> then valid TZ "next to" an UTC timestamp is much easier and more reliable
> then trying to reconstruct that info from some historical TZ DB.
>
>
> 2016-09-01 11:59 GMT+02:00 Sanne Grinovero <sanne(a)hibernate.org>:
>
> > To be fair I would have mapped that differently.
> > Your Shipment arrives at a certain *point in time* (long, UTC based)
> > in a certain Port (mapped with Foreign Key).
> > A Port would have a reference to a local Timezone, and UI could decide
> > to use that (or a different one) for rendering, based on other
> > aspects, like the location of the user actually looking at the UI, or
> > even a switchable user option.
> >
> > Saving in "timezoned" instants is prone to several bugs: you're
then
> > exposed to ambiguities as time doesn't flow linearly in all zones.
> >
> > Storing a timezoned instant only makes sense when you're quoting a
> > political/legal source; i.e. you'ave passed under OCR some legal
> > paperwork and you need to store the date printed on the header in a
> > dedicated field, in Java8 time type you'd never use an Instant for
> > this though, there are dedicated types which make the limitations of
> > this representation clear. AFAIR some kinds of comparisons are made
> > impossible.
> >
> > I'd not spend time with improvements on Calendar: the "old Date
types"
> > should be deprecated.
> >
> >
> > On 1 September 2016 at 07:46, Gunnar Morling <gunnar(a)hibernate.org>
> wrote:
> > >> it is a huge mistake to save the specific TZ differences to the DB
> > >
> > > I once was working on a globally deployed shipping management
> application
> > > where that was a requirement.
> > >
> > > Say there is a shipment from Austin (one TZ) to London (another TZ).
> In
> > > that application an operator based in Hamburg (yet another TZ) should
> be
> > > able to know when the shipment was brought in in Austin (the local
> time
> > in
> > > Austin at that point) and when it was delivered in Hamburg (again the
> > local
> > > time there at that point).
> > >
> > > So we stored all dates in UTC, but also the TZ (in a *different
> column*;
> > I
> > > don't quite remember whether it was just a string with the TZ
> (offset) or
> > > again the entire date-time using TIMESTAMP_WITH_TZ). Storing it in UTC
> > > allowed for comparisons whereas the other column allowed to show each
> > date
> > > in its local TZ. So an operator in Hamburg (at CET) could see a
> delivery
> > > was brought in in Austin at 8:32 CST.
> > >
> > > Something like that would still be my preference for handling TZ-bound
> > > types such as ZonedDateTime: store the UTC timestamp in one column and
> > the
> > > TZ info (either id or offset from UTC) in another.
> > >
> > > Relying on the JVM TZ or something like a single "application
TZ"
> won't
> > cut
> > > it for cases like the one above: the operator in Hamburg wants to see
> > dates
> > > from Austin and London, using their respective time zones. Instead,
> the
> > TZ
> > > of the incoming ZonedDateTime would have to be persisted so it's
> > available
> > > when reading it back.
> > >
> > >
> > >
> > >
> > > 2016-08-31 20:51 GMT+02:00 Steve Ebersole <steve(a)hibernate.org>:
> > >
> > >> We discussed this on HipChat, but for the benefit of all on this
> > >> discussion...
> > >>
> > >> Part of this (the original report) speaks to a difference in how we
> map
> > >> (org.hibernate.type.Type) java.time temporal versus a java.util
> > temporal -
> > >> specifically java.util.Calendar. When we are passed a Calendar (the
> > model
> > >> defines its attribute as Calendar) we pass along that Calendar
> > reference to
> > >> the JDBC driver which forces the driver to use that Calendar's TZ.
> For
> > >> java.time temporal types, we always resolve them to UTC values mainly
> > >> because of how we get to the java.sql.Timestamp value from the
> java.time
> > >> value. For example, for ZonedDateTime we call:
> > >>
> > >> return (X) Timestamp.from( zonedDateTime.toInstant() );
> > >>
> > >> The call to #toInstant there essentially forces the value to UTC
> > because it
> > >> converted directly to epoch-based numeric.
> > >>
> > >> Thinking about this some more and "stepping back", the real
problem
> is
> > that
> > >> there are a few different timezones in play here that need to be
> > balanced
> > >> and handled:
> > >>
> > >> 1. The database default TZ
> > >> 2. The stored TZ
> > >> 3. The JVM/JDBC TZ
> > >> 4. The application TZ
> > >>
> > >> The whole idea of "stored TZ" really depends on how the
> database/driver
> > >> treats TIMESTAMP and whether it supports a
"TIMESTAMP_WITH_TIMEZONE"
> > >> type. I
> > >> personally think it is a huge mistake to save the specific TZ
> > differences
> > >> to the DB, so I would personally continue to not support
> > >> TIMESTAMP_WITH_TIMEZONE
> > >> types. This would mean we never have to bind the Calendar because we
> > could
> > >> simply convert the value to to the JVM/JDBC TZ ourselves.
> Specifically
> > I
> > >> would suggest that we (continue to) assume that the driver has been
> set
> > up
> > >> such that the same TZ is used when
> > >>
> > >> 1. when binding a Timestamp (without passing a Calendar)
> > >> 2. reading a Timestamp (without passing a Calendar).
> > >>
> > >> Specifically this would mean setting the JVM TZ, or for JDBC drivers
> > that
> > >> support it setting the "default TZ" on the driver. If we
start
> storing
> > >> values in the TZ specific to the Calendar we really have no idea how
> to
> > >> properly read those values back because we'd lose context to that
TZ.
> > For
> > >> that reason I suggest we drop passing along the Calender and instead
> > >> perform all these conversions in memory and pass the converted
> > Timestamp to
> > >> JDBC. When reading back, that should mean the driver would return us
> > >> Timestamps relative to that same TZ.
> > >>
> > >> The last piece is "application TZ" which would be an optional
setting
> > >> indicating the TZ zone-based values should be returned in.
> > >>
> > >> E.g., let's say a user maps a ZonedDataTime using
> > @Temporal(TIMESTAMP). If
> > >> following my suggestions, when saving these values we would:
> > >>
> > >> 1. convert the ZonedDateTime to a ZonedDateTime in the
"JVM/JDBC
> TZ"
> > (if
> > >> different from the incoming ZonedDateTime's TZ)
> > >> 2. use that to construct a Timestamp to bind to JDBC
> > >>
> > >> When reading back these values, we would:
> > >>
> > >> 1. get back the Timestamp from JDBC
> > >> 2. assume the value is relative to the "JVM/JDBC TZ" and
build the
> > >> ZonedDateTime via `ZonedDateTime.ofInstant( timestamp.toInstant(),
> > >> jvmJdbcZoneId )`
> > >> 3. convert that built ZonedDateTime to the application TZ:
> > >> `builtZonedDateTime.withZoneSameInstant( applicationZoneId )` - it
> > >> might
> > >> be possible to combine this step with the previous, not sure...
> > >>
> > >>
> > >> Some if this is type-specific. Assuming the suggestions above:
> > >>
> > >> - Instant - inherently epoch-based.
> > >> - to convert this to a Timestamp for binding we would use
> > >> `Timestamp#from(Instant).
> > >> Because of the above assumptions, the driver would interpret
> > >> this relative
> > >> to JVM/JDBC TZ
> > >> - Reading back we simply use `Timestamp#toInstant()`
> > >> - LocalDateTime - is the relative idea of a date+time outside the
> > >> context of any specific TZ.
> > >> - When binding we would resolve this into the JVM/JDBC TZ
> (using
> > >> LocalDateTime#atZone(jvmJdbcZoneId), convert to an Instant and
> > >> create a
> > >> Timestamp and bind that Timestamp to JDBC.
> > >> - When reading back we'd get the Instant from Timestamp and
use
> > that
> > >> in `LocalDateTime#ofInstant` using the application TZ
> > >> - LocalDate - relative idea of a date outside of any specific
> TZ. Of
> > >> course this could also be mapped to a JDBC DATE which circumvents
> > some
> > >> of
> > >> this, but mapped to a TIMESTAMP...
> > >> - When binding we'd convert to a LocalDateTime using
> > >> `LocalDate#atStartOfDay` and treat that LocalDateTime as above.
> > >> - When reading back we would create a LocalDateTime using
> > >> `LocalDateTime.ofInstant(timestamp.toInstant(),
> > >> applicationZoneId )`
> > >> - LocalTime - relative idea of a time outside any specific TZ.
> Could
> > >> also be mapped to a JDBC TIME...
> > >> - When binding we'd create a LocalDateTime using
> > `LocalTime.atDate(
> > >> LocalDate.of( 1970, 1, 1 ) )`, converting that to a
> ZonedDateTime
> > >> using
> > >> `LocalDateTime#atZone( jvmJdbcZoneId )` and finally converting
> > that
> > >> to a
> > >> Timestamp
> > >> - When reading we would call `LocalDateTime.ofInstant(
> > >> timestamp.toInstant(), applicationZoneId ).toLocalTime()`
> > >> - ZonedDateTime - like a Calendar bundles a date+time with a TZ,
> and
> > so
> > >> is directly convertible to an epoch-based value (Instant,
> Timestamp,
> > >> etc).
> > >> - When binding we would convert this to an Instant and then to
> a
> > >> Timestamp
> > >> - When reading we would convert the Timestamp to an Instant and
> > then
> > >> convert the Instant to a ZonedDateTime using
> > >> `ZonedDateTime.ofInstant(
> > >> timestamp.toInstant(), applicationZoneId )`
> > >> - OffsetDateTime - handled similar to ZonedDateTime.
> > >> - When binding we would convert the value to a ZonedDateTime
> using
> > >> `OffsetDateTime#atZoneSameInstant( jvmJdbcZoneId )` and then
> > follows
> > >> ZonedDateTime
> > >> - When reading we would use `OffsetDateTime.ofInstant(
> > >> timestamp.toInstant(), applicationZoneId )`
> > >> - OffsetTime - atm we just drop the ZoneOffset
> > >> - for java.util.Date and java.sql.Timstamp mapping we have to
> decide
> > >> what those concepts model in regards to their value relative to a
> TZ.
> > >> Are
> > >> they a LocalDateTime relative to the system TimeZone? Are they a
> > >> LocalDateTime relative to UTC? At the moment how we handled these
> > >> "legacy
> > >> Java temporal types" ends up answering this question as them
> being "a
> > >> LocalDateTime relative to the system TimeZone".
> > >> - what about java.sql.Time? Is this a LocalTime? An OffsetTime
> > >> relative to UTC (+0)?
> > >> - and java.sql.Date?
> > >>
> > >> Lot of moving parts when you actually start looking deep at
> "supporting
> > >> timezones".
> > >>
> > >>
> > >> [1]
> > >> http://stackoverflow.com/questions/18447995/postgresql-
> > >> 9-2-jdbc-driver-uses-client-time-zone,
> > >> etc
> > >>
> > >> On Wed, Aug 31, 2016 at 9:10 AM Vlad Mihalcea <
> mihalcea.vlad(a)gmail.com>
> > >> wrote:
> > >>
> > >> > Hi,
> > >> >
> > >> > While reviewing the Pull Request for this issue:
> > >> > https://github.com/hibernate/hibernate-orm/pull/1536
> > >> >
> > >> > I realized that we can improve the default TimestampType as
> follows:
> > >> >
> > >> > 1. We could make it parameterizable so that it can also take a
> custom
> > >> > timezone (UTC) during mapping.
> > >> > 2. We could also define a default timezone so that we don't
rely on
> > the
> > >> JVM
> > >> > one. This might be desirable when the UI requires to display the
> time
> > in
> > >> a
> > >> > certain timezone, while we want to save all timestamps in UTC.
> > >> >
> > >> > Let me know what you think.
> > >> >
> > >> > Vlad
> > >> > _______________________________________________
> > >> > hibernate-dev mailing list
> > >> > hibernate-dev(a)lists.jboss.org
> > >> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
> > >> >
> > >> _______________________________________________
> > >> hibernate-dev mailing list
> > >> hibernate-dev(a)lists.jboss.org
> > >> https://lists.jboss.org/mailman/listinfo/hibernate-dev
> > >>
> > > _______________________________________________
> > > hibernate-dev mailing list
> > > hibernate-dev(a)lists.jboss.org
> > > https://lists.jboss.org/mailman/listinfo/hibernate-dev
> >
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
2:25 p.m.
Am 01.09.2016 1:13 nachm. schrieb "Steve Ebersole" <steve(a)hibernate.org>:
Hit send accidentally...
The "problem" is normalization. You are storing a TZ that is specific to
the place (Austin, London) a shipment event occurs on the shipment event.
So even if your app had the requirment to display the shipment event in the
local TZ that that event occurred in, data-normalization says you'd store
the TZ/offset on the place and use that to render the event.
Yes, I think that is what Sanne has been proposing, too. You only need to
do that in a historized fashion as TZs may change over time. Personally, I
don't see big advantages of normalization here.
But even then, I'd personally never do that. I think rendering the
shipping
event Instant in the TZ of place it happens is confusing and not
the norm anyway. I think the norm, for apps that want to render this, is
more a TZ relative to the the user. When I go to Amazon, or FedEx, etc to
track a package I could care less about the London TZ. The whole idea of a
unified "period" would mean that I see them in my local TZ (if not UTC).
Sure, different use cases, different requirements.
Also... this will *not* involve 2 columns. We covered why in the last
email
discussion we had about this a few months ago. Now your app could do
all this... map the @Temporal and then map the TZ and apply them in
memory.
Hum, ok. I remember a discussion on querying but would have assumed that to
be addressed by having the UTC column and converting query params into UTC
as well.
My thinking is that people use date-time types with TZ info for a reason
after all, so there should be a way (an option at least) to retain this
IMHO. But yes, people could do it themselves...
On Thu, Sep 1, 2016 at 5:52 AM Steve Ebersole <steve(a)hibernate.org> wrote:
>
> I think your example is actually a perfect example of when to not store
the
TZ.
>
>
> On Thu, Sep 1, 2016, 5:16 AM Gunnar Morling <gunnar(a)hibernate.org> wrote:
>>
>> > Saving in "timezoned" instants is prone to several bugs:
you're then
>> > exposed to ambiguities as time doesn't flow linearly in all zones.
>>
>> I see it the other way around: the indirection you suggest is prone to
>> errors.
>>
>> Rules about TZ constantly change (e.g. when switching to/from DST),
>> locations change time zones more often than one would expect. Saving the
>> then valid TZ "next to" an UTC timestamp is much easier and more
reliable
>> then trying to reconstruct that info from some historical TZ
DB.
>>
>>
>> 2016-09-01 11:59 GMT+02:00 Sanne Grinovero <sanne(a)hibernate.org>:
>>
>> > To be fair I would have mapped that differently.
>> > Your Shipment arrives at a certain *point in time* (long, UTC based)
>> > in a certain Port (mapped with Foreign Key).
>> > A Port would have a reference to a local Timezone, and UI could decide
>> > to use that (or a different one) for rendering, based on other
>> > aspects, like the location of the user actually looking at the UI, or
>> > even a switchable user option.
>> >
>> > Saving in "timezoned" instants is prone to several bugs:
you're then
>> > exposed to ambiguities as time doesn't flow linearly in all zones.
>> >
>> > Storing a timezoned instant only makes sense when you're quoting a
>> > political/legal source; i.e. you'ave passed under OCR some legal
>> > paperwork and you need to store the date printed on the header in a
>> > dedicated field, in Java8 time type you'd never use an Instant for
>> > this though, there are dedicated types which make the limitations of
>> > this representation clear. AFAIR some kinds of comparisons are made
>> > impossible.
>> >
>> > I'd not spend time with improvements on Calendar: the "old Date
types"
>> > should be deprecated.
>> >
>> >
>> > On 1 September 2016 at 07:46, Gunnar Morling <gunnar(a)hibernate.org>
wrote:
>> > >> it is a huge mistake to save the specific TZ
differences to the DB
>> > >
>> > > I once was working on a globally deployed shipping management
application
>> > > where that was a requirement.
>> > >
>> > > Say there is a shipment from Austin (one TZ) to London (another
TZ). In
>> > > that application an operator based in Hamburg (yet
another TZ)
should be
>> > > able to know when the shipment was brought in in
Austin (the local
time
>> > in
>> > > Austin at that point) and when it was delivered in Hamburg (again
the
>> > local
>> > > time there at that point).
>> > >
>> > > So we stored all dates in UTC, but also the TZ (in a *different
column*;
>> > I
>> > > don't quite remember whether it was just a string with the TZ
(offset) or
>> > > again the entire date-time using
TIMESTAMP_WITH_TZ). Storing it in
UTC
>> > > allowed for comparisons whereas the other column
allowed to show
each
>> > date
>> > > in its local TZ. So an operator in Hamburg (at CET) could see a
delivery
>> > > was brought in in Austin at 8:32 CST.
>> > >
>> > > Something like that would still be my preference for handling
TZ-bound
>> > > types such as ZonedDateTime: store the UTC
timestamp in one column
and
>> > the
>> > > TZ info (either id or offset from UTC) in another.
>> > >
>> > > Relying on the JVM TZ or something like a single "application
TZ"
won't
>> > cut
>> > > it for cases like the one above: the operator in Hamburg wants to
see
>> > dates
>> > > from Austin and London, using their respective time zones. Instead,
the
>> > TZ
>> > > of the incoming ZonedDateTime would have to be persisted so it's
>> > available
>> > > when reading it back.
>> > >
>> > >
>> > >
>> > >
>> > > 2016-08-31 20:51 GMT+02:00 Steve Ebersole <steve(a)hibernate.org>:
>> > >
>> > >> We discussed this on HipChat, but for the benefit of all on this
>> > >> discussion...
>> > >>
>> > >> Part of this (the original report) speaks to a difference in how
we map
>> > >> (org.hibernate.type.Type) java.time temporal
versus a java.util
>> > temporal -
>> > >> specifically java.util.Calendar. When we are passed a Calendar
(the
>> > model
>> > >> defines its attribute as Calendar) we pass along that Calendar
>> > reference to
>> > >> the JDBC driver which forces the driver to use that Calendar's
TZ. For
>> > >> java.time temporal types, we always resolve
them to UTC values
mainly
>> > >> because of how we get to the java.sql.Timestamp
value from the
java.time
>> > >> value. For example, for ZonedDateTime we
call:
>> > >>
>> > >> return (X) Timestamp.from( zonedDateTime.toInstant() );
>> > >>
>> > >> The call to #toInstant there essentially forces the value to UTC
>> > because it
>> > >> converted directly to epoch-based numeric.
>> > >>
>> > >> Thinking about this some more and "stepping back", the
real
problem is
>> > that
>> > >> there are a few different timezones in play here that need to be
>> > balanced
>> > >> and handled:
>> > >>
>> > >> 1. The database default TZ
>> > >> 2. The stored TZ
>> > >> 3. The JVM/JDBC TZ
>> > >> 4. The application TZ
>> > >>
>> > >> The whole idea of "stored TZ" really depends on how the
database/driver
>> > >> treats TIMESTAMP and whether it supports a
"TIMESTAMP_WITH_TIMEZONE"
>> > >> type. I
>> > >> personally think it is a huge mistake to save the specific TZ
>> > differences
>> > >> to the DB, so I would personally continue to not support
>> > >> TIMESTAMP_WITH_TIMEZONE
>> > >> types. This would mean we never have to bind the Calendar because
we
>> > could
>> > >> simply convert the value to to the JVM/JDBC TZ ourselves.
Specifically
>> > I
>> > >> would suggest that we (continue to) assume that the driver has
been set
>> > up
>> > >> such that the same TZ is used when
>> > >>
>> > >> 1. when binding a Timestamp (without passing a Calendar)
>> > >> 2. reading a Timestamp (without passing a Calendar).
>> > >>
>> > >> Specifically this would mean setting the JVM TZ, or for JDBC
drivers
>> > that
>> > >> support it setting the "default TZ" on the driver. If we
start
storing
>> > >> values in the TZ specific to the Calendar we
really have no idea
how to
>> > >> properly read those values back because
we'd lose context to that
TZ.
>> > For
>> > >> that reason I suggest we drop passing along the Calender and
instead
>> > >> perform all these conversions in memory and
pass the converted
>> > Timestamp to
>> > >> JDBC. When reading back, that should mean the driver would return
us
>> > >> Timestamps relative to that same TZ.
>> > >>
>> > >> The last piece is "application TZ" which would be an
optional
setting
>> > >> indicating the TZ zone-based values should be
returned in.
>> > >>
>> > >> E.g., let's say a user maps a ZonedDataTime using
>> > @Temporal(TIMESTAMP). If
>> > >> following my suggestions, when saving these values we would:
>> > >>
>> > >> 1. convert the ZonedDateTime to a ZonedDateTime in the
"JVM/JDBC TZ"
>> > (if
>> > >> different from the incoming ZonedDateTime's TZ)
>> > >> 2. use that to construct a Timestamp to bind to JDBC
>> > >>
>> > >> When reading back these values, we would:
>> > >>
>> > >> 1. get back the Timestamp from JDBC
>> > >> 2. assume the value is relative to the "JVM/JDBC TZ"
and build
the
>> > >> ZonedDateTime via `ZonedDateTime.ofInstant(
timestamp.toInstant(),
>> > >> jvmJdbcZoneId )`
>> > >> 3. convert that built ZonedDateTime to the application TZ:
>> > >> `builtZonedDateTime.withZoneSameInstant( applicationZoneId )` -
it
>> > >> might
>> > >> be possible to combine this step with the previous, not sure...
>> > >>
>> > >>
>> > >> Some if this is type-specific. Assuming the suggestions above:
>> > >>
>> > >> - Instant - inherently epoch-based.
>> > >> - to convert this to a Timestamp for binding we would use
>> > >> `Timestamp#from(Instant).
>> > >> Because of the above assumptions, the driver would interpret
>> > >> this relative
>> > >> to JVM/JDBC TZ
>> > >> - Reading back we simply use `Timestamp#toInstant()`
>> > >> - LocalDateTime - is the relative idea of a date+time outside
the
>> > >> context of any specific TZ.
>> > >> - When binding we would resolve this into the JVM/JDBC TZ
(using
>> > >> LocalDateTime#atZone(jvmJdbcZoneId),
convert to an Instant
and
>> > >> create a
>> > >> Timestamp and bind that Timestamp to JDBC.
>> > >> - When reading back we'd get the Instant from Timestamp
and
use
>> > that
>> > >> in `LocalDateTime#ofInstant` using the application TZ
>> > >> - LocalDate - relative idea of a date outside of any specific
TZ. Of
>> > >> course this could also be mapped to a JDBC
DATE which
circumvents
>> > some
>> > >> of
>> > >> this, but mapped to a TIMESTAMP...
>> > >> - When binding we'd convert to a LocalDateTime using
>> > >> `LocalDate#atStartOfDay` and treat that LocalDateTime as
above.
>> > >> - When reading back we would create a
LocalDateTime using
>> > >> `LocalDateTime.ofInstant(timestamp.toInstant(),
>> > >> applicationZoneId )`
>> > >> - LocalTime - relative idea of a time outside any specific TZ.
Could
>> > >> also be mapped to a JDBC TIME...
>> > >> - When binding we'd create a LocalDateTime using
>> > `LocalTime.atDate(
>> > >> LocalDate.of( 1970, 1, 1 ) )`, converting that to a
ZonedDateTime
>> > >> using
>> > >> `LocalDateTime#atZone( jvmJdbcZoneId )` and finally
converting
>> > that
>> > >> to a
>> > >> Timestamp
>> > >> - When reading we would call `LocalDateTime.ofInstant(
>> > >> timestamp.toInstant(), applicationZoneId ).toLocalTime()`
>> > >> - ZonedDateTime - like a Calendar bundles a date+time with a
TZ, and
>> > so
>> > >> is directly convertible to an epoch-based value (Instant,
Timestamp,
>> > >> etc).
>> > >> - When binding we would convert this to an Instant and then
to a
>> > >> Timestamp
>> > >> - When reading we would convert the Timestamp to an Instant
and
>> > then
>> > >> convert the Instant to a ZonedDateTime using
>> > >> `ZonedDateTime.ofInstant(
>> > >> timestamp.toInstant(), applicationZoneId )`
>> > >> - OffsetDateTime - handled similar to ZonedDateTime.
>> > >> - When binding we would convert the value to a ZonedDateTime
using
>> > >> `OffsetDateTime#atZoneSameInstant(
jvmJdbcZoneId )` and then
>> > follows
>> > >> ZonedDateTime
>> > >> - When reading we would use `OffsetDateTime.ofInstant(
>> > >> timestamp.toInstant(), applicationZoneId )`
>> > >> - OffsetTime - atm we just drop the ZoneOffset
>> > >> - for java.util.Date and java.sql.Timstamp mapping we have to
decide
>> > >> what those concepts model in regards to
their value relative to
a TZ.
>> > >> Are
>> > >> they a LocalDateTime relative to the system TimeZone? Are they
a
>> > >> LocalDateTime relative to UTC? At the
moment how we handled
these
>> > >> "legacy
>> > >> Java temporal types" ends up answering this question as
them
being "a
>> > >> LocalDateTime relative to the system
TimeZone".
>> > >> - what about java.sql.Time? Is this a LocalTime? An
OffsetTime
>> > >> relative to UTC (+0)?
>> > >> - and java.sql.Date?
>> > >>
>> > >> Lot of moving parts when you actually start looking deep at
"supporting
>> > >> timezones".
>> > >>
>> > >>
>> > >> [1]
>> > >> http://stackoverflow.com/questions/18447995/postgresql-
>> > >> 9-2-jdbc-driver-uses-client-time-zone,
>> > >> etc
>> > >>
>> > >> On Wed, Aug 31, 2016 at 9:10 AM Vlad Mihalcea <
mihalcea.vlad(a)gmail.com>
>> > >> wrote:
>> > >>
>> > >> > Hi,
>> > >> >
>> > >> > While reviewing the Pull Request for this issue:
>> > >> > https://github.com/hibernate/hibernate-orm/pull/1536
>> > >> >
>> > >> > I realized that we can improve the default TimestampType as
follows:
>> > >> >
>> > >> > 1. We could make it parameterizable so that it can also take a
custom
>> > >> > timezone (UTC) during mapping.
>> > >> > 2. We could also define a default timezone so that we
don't rely
on
>> > the
>> > >> JVM
>> > >> > one. This might be desirable when the UI requires to display
the
time
>> > in
>> > >> a
>> > >> > certain timezone, while we want to save all timestamps in
UTC.
>> > >> >
>> > >> > Let me know what you think.
>> > >> >
>> > >> > Vlad
>> > >> > _______________________________________________
>> > >> > hibernate-dev mailing list
>> > >> > hibernate-dev(a)lists.jboss.org
>> > >> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
>> > >> >
>> > >> _______________________________________________
>> > >> hibernate-dev mailing list
>> > >> hibernate-dev(a)lists.jboss.org
>> > >> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>> > >>
>> > > _______________________________________________
>> > > hibernate-dev mailing list
>> > > hibernate-dev(a)lists.jboss.org
>> > > https://lists.jboss.org/mailman/listinfo/hibernate-dev
>> >
>> _______________________________________________
>> hibernate-dev mailing list
>> hibernate-dev(a)lists.jboss.org
>> https://lists.jboss.org/mailman/listinfo/hibernate-dev
9:19 a.m.
Regarding
LocalDateTime [...]
When binding we would resolve this into the JVM/JDBC TZ (using
LocalDateTime#atZone(jvmJdbcZoneId)
Couldn't you rather use UTC when handling these TZ-less types?
This will make the solution more robust in cases where you requirement "JVM
TZ when saving = JVM TZ when reading back" isn't met. As these types have
no TZ component, the JVM TZ should take no influence whatsoever.
2016-08-31 20:51 GMT+02:00 Steve Ebersole <steve(a)hibernate.org>:
We discussed this on HipChat, but for the benefit of all on this
discussion...
Part of this (the original report) speaks to a difference in how we map
(org.hibernate.type.Type) java.time temporal versus a java.util temporal -
specifically java.util.Calendar. When we are passed a Calendar (the model
defines its attribute as Calendar) we pass along that Calendar reference to
the JDBC driver which forces the driver to use that Calendar's TZ. For
java.time temporal types, we always resolve them to UTC values mainly
because of how we get to the java.sql.Timestamp value from the java.time
value. For example, for ZonedDateTime we call:
return (X) Timestamp.from( zonedDateTime.toInstant() );
The call to #toInstant there essentially forces the value to UTC because it
converted directly to epoch-based numeric.
Thinking about this some more and "stepping back", the real problem is that
there are a few different timezones in play here that need to be balanced
and handled:
1. The database default TZ
2. The stored TZ
3. The JVM/JDBC TZ
4. The application TZ
The whole idea of "stored TZ" really depends on how the database/driver
treats TIMESTAMP and whether it supports a "TIMESTAMP_WITH_TIMEZONE"
type. I
personally think it is a huge mistake to save the specific TZ differences
to the DB, so I would personally continue to not support
TIMESTAMP_WITH_TIMEZONE
types. This would mean we never have to bind the Calendar because we could
simply convert the value to to the JVM/JDBC TZ ourselves. Specifically I
would suggest that we (continue to) assume that the driver has been set up
such that the same TZ is used when
1. when binding a Timestamp (without passing a Calendar)
2. reading a Timestamp (without passing a Calendar).
Specifically this would mean setting the JVM TZ, or for JDBC drivers that
support it setting the "default TZ" on the driver. If we start storing
values in the TZ specific to the Calendar we really have no idea how to
properly read those values back because we'd lose context to that TZ. For
that reason I suggest we drop passing along the Calender and instead
perform all these conversions in memory and pass the converted Timestamp to
JDBC. When reading back, that should mean the driver would return us
Timestamps relative to that same TZ.
The last piece is "application TZ" which would be an optional setting
indicating the TZ zone-based values should be returned in.
E.g., let's say a user maps a ZonedDataTime using @Temporal(TIMESTAMP). If
following my suggestions, when saving these values we would:
1. convert the ZonedDateTime to a ZonedDateTime in the "JVM/JDBC TZ" (if
different from the incoming ZonedDateTime's TZ)
2. use that to construct a Timestamp to bind to JDBC
When reading back these values, we would:
1. get back the Timestamp from JDBC
2. assume the value is relative to the "JVM/JDBC TZ" and build the
ZonedDateTime via `ZonedDateTime.ofInstant( timestamp.toInstant(),
jvmJdbcZoneId )`
3. convert that built ZonedDateTime to the application TZ:
`builtZonedDateTime.withZoneSameInstant( applicationZoneId )` - it
might
be possible to combine this step with the previous, not sure...
Some if this is type-specific. Assuming the suggestions above:
- Instant - inherently epoch-based.
- to convert this to a Timestamp for binding we would use
`Timestamp#from(Instant).
Because of the above assumptions, the driver would interpret
this relative
to JVM/JDBC TZ
- Reading back we simply use `Timestamp#toInstant()`
- LocalDateTime - is the relative idea of a date+time outside the
context of any specific TZ.
- When binding we would resolve this into the JVM/JDBC TZ (using
LocalDateTime#atZone(jvmJdbcZoneId), convert to an Instant and
create a
Timestamp and bind that Timestamp to JDBC.
- When reading back we'd get the Instant from Timestamp and use that
in `LocalDateTime#ofInstant` using the application TZ
- LocalDate - relative idea of a date outside of any specific TZ. Of
course this could also be mapped to a JDBC DATE which circumvents some
of
this, but mapped to a TIMESTAMP...
- When binding we'd convert to a LocalDateTime using
`LocalDate#atStartOfDay` and treat that LocalDateTime as above.
- When reading back we would create a LocalDateTime using
`LocalDateTime.ofInstant(timestamp.toInstant(),
applicationZoneId )`
- LocalTime - relative idea of a time outside any specific TZ. Could
also be mapped to a JDBC TIME...
- When binding we'd create a LocalDateTime using `LocalTime.atDate(
LocalDate.of( 1970, 1, 1 ) )`, converting that to a ZonedDateTime
using
`LocalDateTime#atZone( jvmJdbcZoneId )` and finally converting that
to a
Timestamp
- When reading we would call `LocalDateTime.ofInstant(
timestamp.toInstant(), applicationZoneId ).toLocalTime()`
- ZonedDateTime - like a Calendar bundles a date+time with a TZ, and so
is directly convertible to an epoch-based value (Instant, Timestamp,
etc).
- When binding we would convert this to an Instant and then to a
Timestamp
- When reading we would convert the Timestamp to an Instant and then
convert the Instant to a ZonedDateTime using
`ZonedDateTime.ofInstant(
timestamp.toInstant(), applicationZoneId )`
- OffsetDateTime - handled similar to ZonedDateTime.
- When binding we would convert the value to a ZonedDateTime using
`OffsetDateTime#atZoneSameInstant( jvmJdbcZoneId )` and then follows
ZonedDateTime
- When reading we would use `OffsetDateTime.ofInstant(
timestamp.toInstant(), applicationZoneId )`
- OffsetTime - atm we just drop the ZoneOffset
- for java.util.Date and java.sql.Timstamp mapping we have to decide
what those concepts model in regards to their value relative to a TZ.
Are
they a LocalDateTime relative to the system TimeZone? Are they a
LocalDateTime relative to UTC? At the moment how we handled these
"legacy
Java temporal types" ends up answering this question as them being "a
LocalDateTime relative to the system TimeZone".
- what about java.sql.Time? Is this a LocalTime? An OffsetTime
relative to UTC (+0)?
- and java.sql.Date?
Lot of moving parts when you actually start looking deep at "supporting
timezones".
[1]
http://stackoverflow.com/questions/18447995/postgresql-
9-2-jdbc-driver-uses-client-time-zone,
etc
On Wed, Aug 31, 2016 at 9:10 AM Vlad Mihalcea <mihalcea.vlad(a)gmail.com>
wrote:
> Hi,
>
> While reviewing the Pull Request for this issue:
> https://github.com/hibernate/hibernate-orm/pull/1536
>
> I realized that we can improve the default TimestampType as follows:
>
> 1. We could make it parameterizable so that it can also take a custom
> timezone (UTC) during mapping.
> 2. We could also define a default timezone so that we don't rely on the
JVM
> one. This might be desirable when the UI requires to display the time in
a
> certain timezone, while we want to save all timestamps in UTC.
>
> Let me know what you think.
>
> Vlad
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
2794
days inactive
2795
days old
10 comments
5 participants
participants (5)
-
Emmanuel Bernard -
Gunnar Morling -
Sanne Grinovero -
Steve Ebersole -
Vlad Mihalcea