** UPDATE **

After some tests I think i have to change the 'now' property in initial_schema.xml to store a UTC timestamp. The 'now' property is using to set "created_date" in the user table.
I changed it for all dbms except for h2 because i can't find a way to do this.

Here we are: the function UTC_TIMESTAMP() for both mysql and mariadb can't be used as default colum value. I suggest to keep 'now()' and adding serverTimezone=UTC in the JDBC url configuration property. But I'm not really sur it's the best way todo this.

This default column value is really a tricky thing and used only if we want to use dbms function to retrieve a timestamp. By the default now() functions (or equivalent) retrieve a timsestamp on the server timezone. So values setted by dmbs functions will be considered by hibernate as UTC timestamp even if it's not really the case.

To facilitate this every dbms are not providing an UTC timestamp function... 😢

For MySQL/MariaDB using the serverTimezone=UTC might work, I've also seen useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC but I haven't tried.

If you can do quick test with those it would be awesome, then we rely on each database's specificities and do our best. Also relying on the default is usually a bad idea. So for me that would be "good enough", at least for a first release.

According to MySql connector/J documentation the property "useJDBCCompliantTimezoneShift" having effect only if "useLegacyDatetimeCode" is set true.

thus the property has an effect only when "useLegacyDatetimeCode=true."

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html#connector-j-reference-set-config

And in the connector/j 8.0 this both properties have been removed.
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-properties-changed.html

If we don't want to make all this changes and avoid using default on column definitions we could remove it from liquibase changelog and add an arbitrary value for injected users.
Because, yes i did not mention it before, the 'now' property in changelog is only used for the 'created_date' column and only for users added by liquibase data injection.
Users created by the application use the jpa repository and so the hibernate configuration.

For me, we have 2 choices:

• keeping the default column value for created date only used by injected users AND so setting all dbms 'now' value to be UTC compliant (if we can).
• removing totally the 'now' property and setting an abritrary created_date value for injected users.

In both case our 'UTC documentation' should warn users about generating timestamp value with DBMS function.

Just a little update for mysql... As expected this does NOT work 😢
And the reason is clear. We expect mysql to generate and store a timestamp in UTC with his internal functions by adding some properties in jdbc connection...

So it confirm my last thought (see the post above) if we want to use a default column value in UTC with a dbms that do not provide a function to do this we have to tune THE dbms config.
Not sure it's up to JHipster to do this (by the way we can't).

Do we have to let the "now" property down ? Or assume this behaviour with Mysql and MariaDB ?

OK, indeed the "now()" function is painful as this depends on the database and its configuration.

Also, one could argue it's not such a good practice to rely on the database to fill out "null" fields, especially if nobody can be sure of the date that would be inserted.

So my commendation would be:

• remove "now()" in all database default fields
• allow "nullable" date fields (not sure why I didn't allow this from the start, I hope it's not a limitation with Liquibase - have a look at ./mvnw liquibase:diff to see if that produces an issue on an app with "nullable" date fields)
• correctly insert today's date from the Java server when needed. I'm also hoping this doesn't have bad consequences with the "audit" system in JHipster (which I would like to remove in the next major release, but that's another topic)

I've just remove the now property and also allow nullable for 'created date' on AbstractAuditingEntity classe. I think this is the only impacted property.

I haven't see a limitation with liquibase for nullable date field. The changelog produce does not contain the nullable constraint, that's it.

Since we set a default value for created date in the audit entity bean there is no reason that think it have a bad consequence. This field can be null if the user explicitly set it as null (why would do something like that ?).
private Instant createdDate = Instant.now();

If this seems working I wonder if t's a good choice to allow nullable created date. There is no logical in having a null created date. Of course this could be happen only with data adding ouside hibernate, so with a liquibase changelog or directly with sql data injection. We assume people who do this already know that and can handle it properly. But is it not strange to JHipster to adding user with a null created date ?

Yes it's a bit strange but it's not supposed to happen, and also I would like to remove the audit in the future, so no big deal for me.

it's not supposed to happen

It will happen at least for JHipster users in the jhi_user table (added by users.csv) but if it's ok so let's do it.

I need to fix travis tests (hope my last commit will do it), squash commits and I'll remove the WIP tag.

Thanks for support, tips and recommendation 👍

Tests are green
commits are squashed
UTC is in
Title is washed

"WIP" were not the ending quest
Let's do this Pull Request

👍

Awesome, congrats!!! I'll try to review it tomorrow

Sorry I did a very bad and totally wrong review (which I cancelled!). This looks really neat, give me some time to do more tests, and I'll merge this.

No problem. By the way I wonder if I have to open another issue (or PR) to add some documentation about this new behaviour.

Adding a new web page in jhipster.tech seems to me the proper way. What does the community use to do in this case ?

Yes it should be documented, as it's a cool feature and as it might surprise some people. At least could you add a note in the fields types at https://www.jhipster.tech/creating-an-entity/ ?

Merging this as it looks all OK, thanks a lot @avdev4j and don't forget to claim the bug bounty!

Of course this might break existing applications, so I'll explain this in the next release notes, but this isn't a very big or very breaking change.

Bounty claimed https://opencollective.com/generator-jhipster/expenses/5367

hey @avdev4j, @jdubois where can I find a summary and the reasons for the change?
I'm trying to update my jhipster and came across your change, and I'm trying to understand what happened.

@rabiori : you can look the initial ticket #8284 and long discussion

I tried looking there, It's not very understandable...
I was hoping for something along the lines of:

"Of course this might break existing applications, so I'll explain this in the next release notes"

that @jdubois said.

Hi @rabiori ,
as you can see we don't have write a "guide" or a note yet about this feature.

Quickly :
When we are dealing with dates in applications, we always want to care about timezones. Because we can't always store the time zone information (some dmbs can) we want to control timestamps storage zone.

So a good practice is to set every timestamps to be stored in UTC. So we can display them in every zone we want to users.

If you have an existing app it could be break it because you will have timestamps in both zones : utc and the legacy jvm one.

If you want to keep the feature be sure to migrate all your timestamps to UTC (I'm aware that it will not be so easy as expected) or if you don't you can remove this :

        properties:
            hibernate.jdbc.time_zone: UTC

in spring.jpa properties.

I suggest this article from the great @vladmihalcea http://in.relation.to/2016/09/12/jdbc-time-zone-configuration-property/.

Of course I have to write a more complete document about this change, sorry.

Hi @avdev4j !
First of all, thank you for your attention.
Regarding migrating existing projects to this change - after playing around with this in the last 2 days there is one key point that should be emphasized (you already mentioned it):
All current timestamps in the db should be UTC, or migrated to it!
If you don't do this, your server will still see these dates as UTC, and you will see a shift in your times.

Fortunately for us our production data is UTC, but it made for some dizzying scenarios in the dev env.

Thanks for the change, it's a good one (although hard on existing projects - I'd take the time to write a guide for migration).

Thanks @rabiori
I'll try to add a guide for those changements as we said above. Maybe we'll could add your migration guide when it will be ready.