Hello,
I am curious why the DDL does not create a timestamp with time zone?
If backward compatibility, I can understand having a legacy dialect that specifies it that way. But still seems odd in this day and age that we do not encourage more people to think about the timezone when they build an app.
Thanks,
Tim
You already figured out the most important aspect, backwards compatibility. Apart from that, not all databases support timestamp with time zone, so I guess another aspect is that we would like to use a default that works on every database.
In the end, people should IMO put the database server and application server into the UTC time zone anyway, in which case it doesn’t matter which type is used.
From a theory perspective, I agree that putting servers in UTC makes sense, and is my default when I set them up.
However the following issues I have seen multiple times, and they seem to be rather common:
Next, if you use the reason that not all databases support TZ, then you are developing against the lowest level of common features, in which case there are many features in Hibernate we need to remove. Starting with transactions. (yes, this is a reduction to the absurd argument, but I do it to make a point).
Lastly, because I could. I created the following class:
public class PostgressDialectWithTZ extends PostgreSQL10Dialect {
public PostgressDialectWithTZ() {
super();
registerColumnType( Types.TIMESTAMP, "timestamp with time zone" );
}
}
combined with the following Hibernate property:
properties.put(AvailableSettings.JDBC_TIME_ZONE, TimeZone.getTimeZone( "UTC" ));
Ensures the database DDL is TZ aware, and also the JDBC driver is TZ aware.
Tim
-Duser.timezone=UTC and the database could run in a docker container with an environment variable for configuring UTC.timestamp with time zone on PostgreSQL does not store the time zone but it does on other databases? So it’s not that simple.Next, if you use the reason that not all databases support TZ, then you are developing against the lowest level of common features, in which case there are many features in Hibernate we need to remove. Starting with transactions. (yes, this is a reduction to the absurd argument, but I do it to make a point).
This has nothing to do with the lowest common denominator but that we don’t want to use defaults that don’t work on some databases. I actually don’t know what the JDBC spec has to say about time zones, but since JDBC 4.2 (Java 8) introduced the Type TIMESTAMP_WITH_TIMEZONE I would assume that the standard TIMESTAMP type should not map to the SQL type you would like to have.
I often work in environments where it is lights management, as in I have not access to production servers. (I cannot critique this much as I also develop software that allows companies to do this). Therefore, I have extremely limited capacity to try and set server time or timezone, let alone influence the system admins. 
The JDBC spec for 4.2; like Java 8 was finally cleaning up dates and times which have been an issue since Java 1.
From a practical perspective, what we care about is consistent representation of data when the data leaves Hibernate and maybe at the JDBC level. With PostgreSQL the value is always stored as UTC. The driver is responsible for translating UTC to local time. If you use Timestamp datatype in MySQL, the behavior is effectively the same as PostgreSQL with the difference that the database server handles the timezone translation based on the timezone established at connection. Oracle, I forget where the timezone is translated, but the result is handled in both cases where the timezone is included in the data type.
I do use my simple class above, but in reality it is not the correct solution, the correct solution requires more intensive changes in the dialect than what I did above. It solves my needs since I do not ever envision having a Timestamp without a timezone.
I believe the correct solution is to more correctly model the java data types, when the java data type includes a timezone implicitly (Instant) or explicitly (OffsetDateTime); Hibernate should respect it and map to TIMESTAMP_WITH_TIMEZONE if this is supported by the database. If the Java data type does not include timezone information (LocalDateTime) then mapping to TIMESTAMP without timezone would be correct. In PostgreSQL “timestamp with time zone” and “timestamp”. For MYSQL it would be “timestamp” and “datetime”. For Oracle, you can pick either ‘timestamp with time zone’ or ‘timezone with local time zone’ to handle the timezone and the normal ‘timestamp’ for the absence of timezone case.
And yes, this could be a breaking change. However, it is the correct solution, otherwise, you will get lots of band-aids such as mine above applied to Hibernate.
Tim
I understand it might not be easy to get this going, but IMO it is really important to spread the knowledge in your company about this so that this gets a wide acceptance as well. The problem doesn’t stop at the chosen SQL type. You have to use Instant or ZonedDateTime everywhere and translate to the user time zone at the representation layer.
FYI, in Hibernate 6 we started to support the new JDBC types for OffsetDateTime and ZonedDateTime. If you want something to change, you can come to our chat and start a discussion with the other developers. Maybe in Hibernate 6 we could change the default, but I personally doubt that for backwards compatibility reasons.