12.0p: Problem with and questions for UTC Date Storage

[Blama]

Hi Isomorphic,

we are preparing for a DB change (Oracle -> PostgreSQL) and wanted to improve date storage at the same time.
Therefore we played around a bit.

In the DateFormatAndStorage docs you write:

Because storage using the JVM's local timezone just uses whatever the local timezone happens to be at the time of writing or reading, changing the operating system or JVM timezone will change the meaning of the datetimes already stored.For example, if you change your server's timezone from US Eastern to US Pacific, any datetimes you have already stored will effectively be shifted forward by three hours. This does not happen if you choose UTC as your Smart GWT storage strategy, because it is independent of server timezone

We learned this now the hard way - dates stored are CET/CEST (UTC+1 / UTC+2), as our dev systems and server run using Europe/Berlin Timezone.
Adding -Duser.timezone=UTC to the Tomcat start like here in Eclipse led to changed times stored and old stored times displayed with an offset (as expected).

Our plan is to shift all the times in the DB and run the DB (or all sessions) in UTC. Then we can of course use -Duser.timezone=UTC.
Do you have any other tips in this area? Do you suggest using columns with timezone in the DB? Is this just ignored by the framework and the DB will use the session's TZ? I did not find a datatype for this in the docs.

Besides these questions, there is also a bug IMHO and one typo (see the attached picture).
Also, running the application with sql.MYDB.useUTCData/eTimes: true in server.properties does not have the same effect as running Tomcat with JVM option -Duser.timezone=UTC - it is just ignored (using current 12.0p).
Can you double check this is working for you as expected? I tried both versions without success and also did not find a default in isomorphic_core_rpc.jar/framework.properties

Thank you & Best regards
Blama

[Isomorphic]

Our recommendation is to store in UTC.

No timezone information is transmitted from server to client, just the actual datetime value, so if you wanted to store timezone information with every datetime you would need to do that in a separate field. We wouldn't recommend doing this; even applications that have a need to render dates in multiple timezones (eg travel booking) do not typically associate timezone information with individual dates but rather with airport codes or other geographic information.

As far as useUTCDateTimes, we'll correct the doc typo, but as far as the feature working, if you turn this on *and restart your server*, what you should see is that on an "add" operation the SQL rendered is different, and at that point the feature is working. Whether this has the same overall effect as -Duser.timezone would depend on your DB and your data.

[Blama]

Hi Isomorphic,

As far as useUTCDateTimes, we'll correct the doc typo, but as far as the feature working, if you turn this on *and restart your server*, what you should see is that on an "add" operation the SQL rendered is different, and at that point the feature is working. Whether this has the same overall effect as -Duser.timezone would depend on your DB and your data.

yes, this is fine, we must have had some other problem.

Thank you & Best regards
Blama