Generator-jhipster: JHipster should configure hibernate to use the UTC convention with timestamps

[avdev4j]

Overview of the feature request

Hi All,
I have just dealing with some problems with Instant fields in entities and values stored in my database.
After some research i found that, in JHipster, we do not specified a time zone in hibernate configuration.
I think, to avoid some problems and have independant time zone application, we should use UTC convention to store timestamps.

Motivation for or Use Case

The reason why i wrote this request is that in my application i have to inject some datas with UTC date time fields and filter them by date.
Datas are load in Instant object (java.time) and store in timestamp column in a postgres DB.

After some research i feel it will be a better practice to store all timestamps on the UTC Time zone (well UTC is not really a time zone as GMT is ).

Since UTC is the common time standard across the world i'm sur we should adopt this convention in JHipster. By default hibernate use the JVM time zone.

I made a sample project, get it here (JHipster 5.3.1):
https://github.com/avdev4j/utcHipster
the .yo-rc.json
the application.yml

Solution :
As you can see in my application.yml we just have to add this in JPA part :

    properties:
      hibernate:
        jdbc:
             time_zone: UTC

Then all timestamps will be store in the UTC standard.

Sample taken from this app : (from h2 console)

Sources:
Vlad Mihalcea - Hibernate Developer Advocate
Baeldung
moelholm.com

How to test my sample application

Get it from the git repo, start it and with a tool like postman do:

Add a data ("dateTime" field is set automatically by Instant.now())

post /api/myData
{ "name": "test_with_utc"}

Get all datas

get /api/myData

You can turn off the UTC configuration to see that hibernate will use the JVM time Zone and change the value stored in the 'date_time' column.

Related issues or PR

• [X] Checking this box is mandatory (this is just to show you read everything)

[PierreBesson]

I agree with you that it's a good practice, but what if users specifically configure their JVM timezone to non UTC and then notice that it's not taken into account in their database. Are you sure everyone will want this ?

[jdubois]

I have some mixed feelings here...

Indeed, as @PierreBesson points out, that might surprise some users - that's why we have Policy 1.

Then, we try to push "best practices" (and that's indeed one), and that wouldn't be the first time we tune Hibernate (and Spring Boot) default configuration, following the advice of our great friend @vladmihalcea - so I'm keen on doing this change.

If anybody has any comments here, don't hesitate to add them, this could be an important decision impacting many people, so let's not rush too quickly.

[cbornet]

I would also argue that the true best practice is having your OS/JVM and DB all configured to UTC timezone...

[avdev4j]

@PierreBesson
This is an important question and of course can be a problem because if you are not aware of this"tuning" you will be surprised and confusing about this behaviour.

Now let me ask you the opposite question : why people would not want this ? (timestamp in UTC). Since we can't know the time zone used to store the data in the db it can be a real huge problem. But if you are sure that all timestamps are stored with a "generic" time zone you can translate it into the good one.
In the other way you are dependant on JVM time zone that can be changed. Of course i think JVM time zone can always be changed (for reason that i don't know) and values be stored in UTC.

To simplify: It's always simplier to convert an UTC timestamp into a time zone wanted.

@jdubois
Dealing with date and time zones is, in my opinion, very complicated. "On wich time zone am I ? Why the hour value has changed ? why the day has changed ?" etc...
I will be very happy if some JHipster's user can give us the way they use to do and decide or not to set UTC as default storage time zone.

@cbornet
Only for you : utceverywhere.com 🤣

EDIT: A solution can be to use "timestampz" data type column instead of "timestamp" but not sure it's supported by other db than postgresql and it belong to the project to decide to use this, not really JHipster (except for timestamp column already use in jhipster like "creation date" for a user).

[Blackdread]

I put everything in UTC, database, time in DTO, etc
It is just normal behavior, DTOs have the information of timezone when changed to JSON which is Z -> UTC, front-end (Angular) can be configured to show datetime as is or automatically change to current timezone of user browser (default behavior)

[DanielFran]

+1 for UTC by default and documenting telling to remove in case wanna use JVM timezone

[pascalgrimaud]

@cbornet is our expert for this subject ! So same as @DanielFran : +1 for UTC and documentation for people who want to change

[PierreBesson]

Also +1 for me.

[jdubois]

OK so let's do this!
@avdev4j as you already have the solution, would you like to do the PR?

[avdev4j]

@jdubois consider it’s done ;)
But before doing the PR i need to test more and all cases. I will update my github project. Stay tuned ;)

[DanielFran]

@avdev4j Do not forget to test requests using Filters.

[avdev4j]

@DanielFran You mean web services requests ?

currently i have some trouble with localTime and OffSetTime stored in time type column. The value stored is neither an utc one neither a local one....

[DanielFran]

@avdev4j I mean using DTO and filter options: https://www.jhipster.tech/entities-filtering/

[avdev4j]

Hi guys,
Just a little update from my work.
I had to open an issue on hibernate bug tracker because I think there is somathing wrong when dealing with UTC dateTime on a time zone on Daylight time savng period.
https://hibernate.atlassian.net/browse/HHH-12988

Maybe we can wait for a hibernate community response before using definitively the "hibernate.jdbc.time_zone" option.

However only LocalDateTime and OffsetTime are involved.

As @DanielFran advised, I tested filtering. I create a new entity with an instant field and send some requests (equals, lessThan, greaterThan...).
I think this modification will not cause any problems beacause it's only the stored value that will be impacted.
Since we already send dates on ISO 8601 format with moment JS there is not impact.

Here is a cycle life explanation before and after :

Before
Prompt a date value on local time zone -> send it to server with utc time zone (see toJSON() - momentJs) -> the value is stored by hibernate according to the JVM time zone
23/09/2018 23h37 -> 2018-09-23T21:37:00Z -> 2018-09-23 23:37:00

After
Prompt a date value on local time zone -> send it to server with utc time zone (see toJSON() - momentJs) -> the value is stored by hibernate according to UTC time zone
23/09/2018 23h37 -> 2018-09-23T21:37:00Z -> 2018-09-23 21:37:00

In both case hibernate change the date value on insert or select requests according to the time zone setted.
Of course system should always manage date with UTC time zone and local format should only use to UI.

_Note_ :
In case of MYSQL database we have to add a specific configuration : "useLegacyDatetimeCode=false" as
jdbc:mysql://localhost:3306/<%= baseName %>?useUnicode=true&characterEncoding=utf8&useSSL=false&useLegacyDatetimeCode=false

Remains to be done:
Test the generator with MYSQL db and his specific configuration.
Should I add some unitTests, like I did with hibernate templates (see my repo https://github.com/avdev4j/hibernate_utc_localTime) ?

I wonder if I forgot something, if you think it's the case please tell me.

Thanks and have a good night ;)

Anthony Viard

[jdubois]

Thanks a lot @avdev4j that's really awesome work!

• For the Hibernate issue that's quite unfortunate, so yes let's wait until they give some answer
• yes some tests are always good, especially on this part as it's quite tricky to check

[Blackdread]

In addition of "useLegacyDatetimeCode=false" I also used "serverTimezone=UTC&timezone=UTC", I do not remember why I put it and if it was necessary (I have mysql db)
See: Connector 5.1 doc or more recent one 8.0

[avdev4j]

@jdubois
In my opinion it's a good choice to wait for a response from Hibernate but it's not a problem for keeping working on this feature:

@Blackdread
Thanks for tips. But I think it will not be necessary in the futur :

serverTimezone
Override detection / mapping of time zone. Used when time zone is not in Java time zone

It's mean that this behaviour will override the hibernate one. If we definitively use the hibernate tuning you will not need to do this anymore ;).

[avdev4j]

Hi all,
I'm coming with some good news. First we have response from @vladmihalcea here and there is no bug, the serialization (even on DST) work as intended.

So I keep working on the issue by forking the generator and adding unit tests.

https://github.com/avdev4j/generator-jhipster/tree/feature/8284

I already squash my commits, update from the last version (5.4.1) and I wonder if I should open a pull request now.
Because it's my first contribution I wonder if some of you could validate my work before open the PR or not. In every case feedbacks are really appreciate to improve my work and myself.

Hope it will be usefull for the community.

thanks,

Anthony

[jdubois]

Thanks a lot for all this hard work @avdev4j ! I'm adding a $100 bug bounty to this ticket because that's totally worth it!

Don't worry about the code review: if you are not confident about your pull request you can mark [WIP] in the title, or add some comment about it. And as it will pass all our CI chain, it's good to make the pull request at anytime, so you know if you messed up something important. Anyway, we'll review it before merging, that's what we always do.

[avdev4j]

Thanks a lot @jdubois !!
I've just add the PR with mark as [WIP] as you advise me about.
👍

[hdurix]

I think this issue can be closed (https://github.com/jhipster/generator-jhipster/pull/8501 has been merged).

[mraible]

I'm experiencing something that might be related to this issue in 21-Points Health.

The value stored in the database is 2018-11-13 13:06:00. However, the same value returned from the API on 21-points.com is "timestamp":"2018-11-13T13:06:00Z". When I run things locally, the value is "timestamp":"2018-11-13T13:06:00-07:00". It seems I might need to change something on the server so it doesn't add the timezone.

The response from the API seems to have timezone information in it, and I'd prefer it didn't. I tried making the change below, but it doesn't seem to help. Any ideas?

diff --git a/src/main/resources/config/application.yml b/src/main/resources/config/application.yml
index de676f3..4870ac4 100644
--- a/src/main/resources/config/application.yml
+++ b/src/main/resources/config/application.yml
@@ -44,6 +44,8 @@ spring:
         active: #spring.profiles.active#
     jpa:
         open-in-view: false
+        properties:
+            hibernate.jdbc.time_zone: UTC
         hibernate:
             ddl-auto: none
             naming:

[avdev4j]

Hi Mat,
This option will tell to hibernate to store the timestamp value in UTC. Does the "2018-11-13 13:06:00" value is correct (I mean has the correct value as UTC timestamp) ?

If this is the expected value in database so I think we have to look after server config or jackson properties.

Does your server is set in UTC ? Your local I suppose not. Let's do a try by setting your computer in UTC.
I wonder if jackson would have a specific behaviour with zonedDateTime by adding the server timezone on deserialization.

[mraible]

I think I figured out the problem. In JHipster 4.x, I was displaying the dates with the following HTML in my Angular component:

{{weight.timestamp | date:'medium': 'UTC'}}

This worked, and I remember I had to add UTC to get dates to display correctly. I kept this change when migrating to JHipster 5. However, it doesn't work now (and it seems strange that it ever did).

I changed my local timezone to UTC on my laptop, changed the expression to the following, and ran my app in prod mode again:

{{weight.timestamp | date:'medium'}}

Now it displays in my local time (I tested with my phone since using my laptop renders it in the local UTC timezone).

It doesn't seem to make a difference if I use hibernate.jdbc.time_zone: UTC or not. I'm guessing that's because it only affects writes, not reads. Since I already seem to be storing timestamps in UTC (on Heroku), it doesn't seem like I need it.

[vladmihalcea]

@mraible When we added the hibernate.jdbc.time_zone configuration property in Hibernate, we wanted to address the use case when the DB server is configured to use a given time zone (e.g. UTC), but the web/application server is using a different time zone (e.g. CET).

The time zone given by the hibernate.jdbc.time_zone the property will be used like this:

• st.setTimestamp( index, timestamp, Calendar.getInstance( options.getJdbcTimeZone() ) );
• rs.getTimestamp( name, Calendar.getInstance( options.getJdbcTimeZone() ) ), options )

So, it applies both for reads and writes. Now, the JDBC Driver simply appends the provided time zone offset when binding/fetching a given Timestamp. Otherwise, if you don't specify the hibernate.jdbc.time_zone property, the web/application server default time zone will be used to build the time zone offset.

Therefore, it's a matter of how you want Timestamp to be persisted/fetched. If you already convert them to UTC in the application, it makes sense to set the hibernate.jdbc.time_zone to UTC, to avoid the JDBC Driver to consider that the Timestamp is in the local time zone.

Otherwise, if you want to use the local web/application server time zone while the DB runs in UTC time zone, then you don't need to set the hibernate.jdbc.time_zone property since the JDBC Driver will append the time zone offset for you.

For some examples, check out this article I wrote some time ago.

[DanielFran]

@vladmihalcea If we implement a multitenancy Database (https://vladmihalcea.com/hibernate-database-catalog-multitenancy/), can we define different timezone in the application, 1 per tenant Database?

[vladmihalcea]

@DanielFran For the moment, this is not supported since the timezone setting applies to the SessionFactory, not to the current tenant which is bound to the Session. To support this, we need a new Jira issue.

[tibistibi]

is there any effect on existing data?

[tibistibi]

i see there is a new table should i move my current data to this table?
or is this only for testing?

[DanielFran]

@vladmihalcea Thanks for your feedback. Should I open this issue or you will take care of it?

[vladmihalcea]

Please create a Jira issue. Thanks.

[DanielFran]

@vladmihalcea I created https://hibernate.atlassian.net/browse/HHH-13115

[avdev4j]

i see there is a new table should i move my current data to this table?
or is this only for testing?

@tibistibi
is the new table named jhi_date_time_wrapper ? If yes it's only used by unit tests (cf "context="test"" in the initial changelog).

On existing data it could have a huge effect if you don't store your timestamp in UTC yet. With this tuning all timestamps stored will be considered as UTC one.

If you still want to use your JVM timeZone (or specific config) you should comment or remove the hibernate config in application.yml.

BUT if you want to use this improvement be sur to convert all timestamps in UTC before (if it matter for you otherwise nevermind consider all timestamps as UTC one when you migrate to 5.6).

Hope it's clear enough for you otherwise feel free to ask more informations ;).

[tibistibi]

i @avdev4j thanks for the information. i think its not a big change for me because i have no data which is dependant on hours. but i think these kind of change should be communicated more clear because it could mess up live data. which i think is rather critical.

[tibistibi]

@avdev4j it has more effect than expected :(

i have LocalTime field and now it does not work any more, i expected LocalTime to be time zone independent but i guess they are not. it would be great if you can shed some light on the situation.

so i have an angular form and add a time like '9:00' this is stored in mysql as 9:00* but when i retrieve it back into my form i get '10:00'

what i do not understand is why it gets 10:00 or why it is not stored as 8:00.

i want to have the times being stored and retrieved as is i'm not interested in timezone. any help?

*) when i do a select in the database console i see 09:00:00

[avdev4j]

@tibistibi
I think you should read this : DST is ignored when saving a LocalTime with hibernate.jdbc.time_zone=UTC and the response from @vladmihalcea to understand how localtime are managed by JDBC. (I don't think it's related to DST but LocalTime management could be a clue).

I wonder what the value is when it's retrieve by hibernate. Maybe it's only an Angular issue ?

[Blackdread]

I think it is angular issue, what type do you use in angular?
Moment will display in local time of user

[tibistibi]

thanks for the pointers i will read it. i hoped it was angular but i added logging and see the time added is defferent than retrieved:

save declaration days: [DeclarationDayDTO{id=1460, day='2018-11-16', amount=25.0, hourFreelancer=null, hourClient=null, allowance=null, startFreelancer='12:00', endFreelancer='19:30', breakFreelancer='00:00', startClient='null', endClient='null', breakClient='null', declaration=null}, DeclarationDayDTO{id=1461, day='2018-11-17', amount=25.0, hourFreelancer=null, hourClient=null, allowance=null, startFreelancer='11:00', endFreelancer='20:00', breakFreelancer='01:45', startClient='null', endClient='null', breakClient='null', declaration=null}, DeclarationDayDTO{id=1462, day='2018-11-18', amount=25.0, hourFreelancer=null, hourClient=null, allowance=null, startFreelancer='10:00', endFreelancer='19:30', breakFreelancer='02:30', startClient='null', endClient='null', breakClient='null', declaration=null}, DeclarationDayDTO{id=1463, day='2018-11-19', amount=25.0, hourFreelancer=null, hourClient=null, allowance=null, startFreelancer='null', endFreelancer='null', breakFreelancer='null', startClient='null', endClient='null', breakClient='null', declaration=null}, DeclarationDayDTO{id=1464, day='2018-11-20', amount=25.0, hourFreelancer=null, hourClient=null, allowance=null, startFreelancer='null', endFreelancer='null', breakFreelancer='null', startClient='null', endClient='null', breakClient='null', declaration=null}]

and when found:

2018-11-30 14:57:16.237 TRACE 1796 --- [  XNIO-5 task-6] nl.tibi.sbys.service.DeclarationService  : found day: DeclarationDay{id=1460, day='2018-11-16', amount=25.0, hourFreelancer=null, hourClient=null, allowance=null, startFreelancer='13:00', endFreelancer='20:30', breakFreelancer='01:00', startClient='null', endClient='null', breakClient='null'}
2018-11-30 14:57:16.237 TRACE 1796 --- [  XNIO-5 task-6] nl.tibi.sbys.service.DeclarationService  : found day: DeclarationDay{id=1461, day='2018-11-17', amount=25.0, hourFreelancer=null, hourClient=null, allowance=null, startFreelancer='12:00', endFreelancer='21:00', breakFreelancer='02:45', startClient='null', endClient='null', breakClient='null'}
2018-11-30 14:57:16.238 TRACE 1796 --- [  XNIO-5 task-6] nl.tibi.sbys.service.DeclarationService  : found day: DeclarationDay{id=1462, day='2018-11-18', amount=25.0, hourFreelancer=null, hourClient=null, allowance=null, startFreelancer='10:00', endFreelancer='19:30', breakFreelancer='02:30', startClient='null', endClient='null', breakClient='null'}

and in db:

| id   | day        | amount | hour_freelancer | hour_client | allowance | declaration_id | start_freelancer | end_freelancer | break_freelancer | start_client | end_client | break_client |
+------+------------+--------+-----------------+-------------+-----------+----------------+------------------+----------------+------------------+--------------+------------+--------------+
| 1460 | 2018-11-16 |     25 |            NULL |        NULL |      NULL |            139 | 12:00:00         | 19:30:00       | 00:00:00         | NULL         | NULL       | NULL         |
| 1461 | 2018-11-17 |     25 |            NULL |        NULL |      NULL |            139 | 11:00:00         | 20:00:00       | 01:45:00         | NULL         | NULL       | NULL         |
| 1462 | 2018-11-18 |     25 |            NULL |        NULL |      NULL |            139 | 09:00:00         | 18:30:00       | 01:30:00         | NULL         | NULL       | NULL         |
| 1463 | 2018-11-19 |     25 |            NULL |        NULL |      NULL |            139 | NULL             | NULL           | NULL             | NULL         | NULL       | NULL         |
| 1464 | 2018-11-20 |     25 |            NULL |        NULL |      NULL |            139 | NULL             | NULL           | NULL             | NULL         | NULL       | NULL         |
+------+------------+--------+-----------------+-------------+-----------+----------------+------------------+----------------+------------------+--------------+------------+--------------+
5 rows in set (0.00 sec)

[avdev4j]

Well I have wrote some tests for all time Object -> HibernateTimeZoneTest
And for localTime the value set is 14:30
The value stored 13:30
The value retrieved by hibernate: 14:30

Is there an issue with Mysql and LocalTime ?

[tibistibi]

i had my tests setup to use h2 and they worked. i'm working on changing it to mysql to check it there.

with h2 i have a problem though that when i add start 9:00 end 19:00 and break 1:00 and i do a calculation in the database query to get worked hours i should get 9 (as a decimal)
but with h2 i get 11. probably because all are translated to UTC with 2:00 so the calculation goes wrong.
on mysql i do not have this problem.

[tibistibi]

ok switched to mysql and now have these errors:

[ERROR] Failures: 
[ERROR]   HibernateTimeZoneTest.storeInstantWithUtcConfigShouldBeStoredOnGMTTimeZone:71->assertThatDateStoredValueIsEqualToInsertDateValueOnGMTTimeZone:173 expected:<"2014-11-12 0[5]:50:00.0"> but was:<"2014-11-12 0[6]:50:00.0">
[ERROR]   HibernateTimeZoneTest.storeLocalDateTimeWithUtcConfigShouldBeStoredOnGMTTimeZone:86->assertThatDateStoredValueIsEqualToInsertDateValueOnGMTTimeZone:173 expected:<"2014-11-12 0[6]:50:00.0"> but was:<"2014-11-12 0[7]:50:00.0">
[ERROR]   HibernateTimeZoneTest.storeLocalTimeWithUtcConfigShouldBeStoredOnGMTTimeZoneAccordingToHis1stJan1970Value:130->assertThatDateStoredValueIsEqualToInsertDateValueOnGMTTimeZone:173 expected:<"1[3]:30:00"> but was:<"1[4]:30:00">
[ERROR]   HibernateTimeZoneTest.storeOffsetDateTimeWithUtcConfigShouldBeStoredOnGMTTimeZone:100->assertThatDateStoredValueIsEqualToInsertDateValueOnGMTTimeZone:173 expected:<"2011-12-14 0[8]:30:00.0"> but was:<"2011-12-14 0[9]:30:00.0">
[ERROR]   HibernateTimeZoneTest.storeOffsetTimeWithUtcConfigShouldBeStoredOnGMTTimeZoneAccordingToHis1stJan1970Value:147->assertThatDateStoredValueIsEqualToInsertDateValueOnGMTTimeZone:173 expected:<"1[3]:30:00"> but was:<"1[4]:30:00">
[ERROR]   HibernateTimeZoneTest.storeZoneDateTimeWithUtcConfigShouldBeStoredOnGMTTimeZone:114->assertThatDateStoredValueIsEqualToInsertDateValueOnGMTTimeZone:173 expected:<"2011-12-14 0[8]:30:00.0"> but was:<"2011-12-14 0[9]:30:00.0">

so it seems h2 is handling times differently than mysql.

[avdev4j]

Well I assume it’s Mysql that handle times differently than others (joke ;)).

By the way I tested with a local mysql instance during developpement of this feature and I’ve never notice this...

I will test again with mysql.
I’m sorry you meet all those problems but for sure it will be usefull for the entire community.

[tibistibi]

could it be different way of installing the db? i have both my mysql and mariadb install via default way of apt-get on linux (mysql on ubuntu and mariadb on debian)

[tibistibi]

with mysql i only could get your tests working (and mine) without the UTC and setting the formatter in the test to CET which i'm on.

[gmarziou]

@avdev4j jhi_date_time_wrapper table and related entity/repository are not used in code. Should they be removed or is there some manuel step that should be documented?

[avdev4j]

jhi_date_time_wrapper is used for testing only in the HibernateTimeZoneIT.java file.

Related repository and entity is declared in the test package too.

The table is only created with the "test" liquibase context.

        <changeSet author="jhipster" id="00000000000002" context="test">
        <createTable tableName="jhi_date_time_wrapper">

[gmarziou]

Sorry, I did not notice the Liquibase context.
Thanks