Postgres Dialect and Time Zone

Hibernate ORM
#1

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

#2

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.

1 Like
#3

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:

  1. I currently run multiple development teams around the world using shared databases. Developers tend not to run workstations in the UTC TZ, which makes for some interesting issues. This kind of development is continuing to grow.
  2. Unless you are dealing with a multi-national company or sysadmins who worked at a large company, I have found most actually setup servers with local time.
  3. I did not check all databases Hibernate works with, but the ones I quickly scanned and verified, if they really are for more than just a few users, all seemed to have timezone as an available option for timestamp; but I could be wrong.

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

#4
  1. You can still run the databases and application servers in UTC. Use the system property -Duser.timezone=UTC and the database could run in a docker container with an environment variable for configuring UTC.
  2. If you care about this, you will either enforce the use of the dialect you posted or that sys-admins properly configure the servers. A different default SQL type wonā€™t help you with the apparently lacking knowledge of your sys-admins or developers. Only training to get their awareness will help with that.
  3. You do realize that 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.

1 Like
#5

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. :slight_smile:

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

1 Like
#6

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.

1 Like