I try to add support for time zones to the web application. User's time zone will be used to display time and for calculations.

I checked a lot of blogs and posts on StackOverflow about Hibernate, Spring Boot related to time zones but I didn't found solution. Some posts describes using time zone only for displaying date/time to user but it's not enough. I need date time loaded from database with user time zone to make calculations on server.

I made some experiments with Hibernate Interceptors (Hibernate user guide - Events). Hibernate gives ability to implement interceptor with onLoad method which allow to modify entity after load. In this case interceptor should modify date/time fields based on user time zone get from some global service. It looks like something I need but I don't have any experience with that on production and didn't know is it good way to solve the problem.

Requirements

Each user should be able to set their own time zone.

The time zone is used not only for time display, but also for calculations. The application includes a booking module where the exact time is important. It cannot be that we allow someone to book an appointment, for example, at 10:00, because it is just 8:30 on the server, because it may already be 1:00 p.m., i.e. 3 hours after the appointment time in user's time zone.

Problem

I would like to change time zone used by Hibernate for every HTTP request based on current user settings. How to set time zone that Hibernate uses during a given request?

Tech stack

In the application I use Hibernate and the time is always saved in the database in UTC. On the application side, the time is converted to the time zone set in config (same time zone for every user).

I use Quarkus in the application, but the solution for Spring Boot is also satisfactory for me. Rewrite solution for Spring Boot or other Java EE framework to Quarkus will not be a problem for me.

UPDATE 1

More context

Application is for photographers which can share booking page with their clients (and potential clients). I need to calculate available hours to reserve an appointment. Photographer can set up working hours like 8:00 - 16:00 in his/her time zone (e.g. Europe/Warsaw). In those hours clients can reserve appointments. Problem with using UTC on backend and using time zone only for display date/time starts when photographer use time zone with DST (Daylight Saving Time). In UTC 7:00 can be 8:00 in local time zone but it will be 9:00 during DST.

The same problem is when you try to send notifications like reminders about appointment. I need to know photographer time zone on server because I need to how much time remaining until appointment. For example in 20:00 UTC in time zone UTC 12 mean 8:00 next day. If I send reminder at 20:00 UTC with message like "You have appointment next day" it will wrong, because that client has appointment today.

CodePudding user response：

Appointments are not moments

Appontments should not be kept as moments. An appointment booked in the future, when driven by a time-of-day, cannot be pinned down to a particular point on the timeline.

The reason is that political time is controlled by politicians. And those politicians can change the rules of the time zones under their jurisdiction on a whim at any moment. So if you try to nail down the moment of an appointment next January 23rd at 3 PM, you’ll be an early or late when the politicians decide to stay on Daylight Saving Time year-round. Or you’ll be a half-hour off when the politicians decide to change their clock to match that of a neighbor as a diplomatic gesture.

So you should keep appointments as a date with a time-of-day but keep the time zone separately. So two separate values.

• In Java that means a LocalDateTime and a ZoneId.
• In SQL that means one column of a type akin to the SQL standard type TIMESTAMP WITHOUT TIME ZONE, and another column of a textual type for the name of the time zone.

When you need to build a schedule, you can determine a moment dynamically by combining the LocalDateTime with a ZoneId te produce a ZonedDateTime. Do not store this result — it will not be valid when the politicians change the time zone rules.

Notice that the above is for appointments such as dentists and hair stylists and photographers, for appointments aimed at a particular time of the day. In contrast are plans tied to natural time such as a rocket launch scheduled according to expected weather conditions. Such plans are not altered by politicians changing the rules of a time zone. Such plans are recorded using a column of a type akin to SQL standard type TIMESTAMP WITH TIME ZONE, and Java type Instant (though exchanged with database via OffsetDateTime class).

I’ve posted on this before on Stack Overflow, as have others. Search to learn more.

CodePudding user response：

I think this is simple and for some reason you have some over complicated situation.

If this was a spring-mvc application I would understood the need for UI-backend communication with some type of timezone. But you probably have a rest backend application since you are mentioning quarkus / spring-boot. Rest application is normally stateless.

I think that this article from the very known @vlad-mihalcea also verifies what needs to be followed as best practice. It concludes with what I also agree 100%.

When dealing with time zones, it’s best to use UTC as much as possible and only convert the timestamp to the current user time zone when rendering the UI.

So I think your problem would be much simplified if you used UTC for database, UTC for backend server and you just used timezone in the UI just for the user to read it, by just converting from UTC to user's timezone. If you have the normal stateless rest application then the backend is completely agnostic for user timezone. The user lives in the browser window and so only the frontend knows about user timezone.

Even if we suppose that you need to remember the timezone of the user, then you can store the specific timezone for the user in database (user A has timezone GMT 2), and then the UI can poll it once and then convert all data it receives from backend to that timezone for that specific user.

So if you make calculations in the backend related with user timezone and you also persist timezones in Database, I would look at this as a probable architecture deficiency which will might overcomplicate the situation in the future even if you manage to solve your issue for the moment.