0

It's not a coincidence that this question has the same exact title as this one. Same problem, different environment.

My Angular-based application sends the Tomcat server a date taken from Angular UI Calendar Picker in ISO format and UTC time zone, so that if I choose May 1st 2016 in the CEST time zone it will be Apr 30 22:00 +2 in JSON. And Postgres will save that wrong value too! Mysql, SQL Server and Oracle are not affected by this problem (explanation below)

Here is a step-by-step explanation of the round-trip

  1. User selects 11/04/1956 in dd/MM/yyyy format
  2. Client sends "birthDate" : "1956-04-10T22:00:00.000Z"
  3. Spring MVC, using Jackson, will decode Tue Apr 10 23:00:00 CET 1956. Please note that Apr 30 should be in daylight saving already
  4. Hibernate will save that data to Postgres on a column of type java.util.Date annotated with @Temporal(DATE)
  5. Postgres will store 1956-04-10 in that column
  6. Spring requests via Ajax a JSON representation of the entity, and that contains "birthDate" : "1956-04-10" in the body

Additional considerations:

  • I have already faced and solved the problem in other DBs by setting Tomcat time zone. Not really the smartest solution ever, because imposing -Duser.timezone requires exact synchronization with client time zone, which is not possible in an international environment, which ours is not - I mean we are lucky all our customers are from the same location, no matter London Rome or Paris
  • The issue does happen only when date fields strip the time. I mean if we store a column as datetime to only keep the track of the date, it will always be presented correctly
  • I have already read this bug but it provides no helpful information
  • Worse, I need this problem solved or worked around due to time constraints. Manually editing DB entries where incorrect is the last stand

I have little experience with Postgres anyway.

Is there any option to force Postgres timezone from JDBC or Hibernate properties? (workaround)

Or what is the correct solution to this kind of issue?

halfer
  • 19,824
  • 17
  • 99
  • 186
usr-local-ΕΨΗΕΛΩΝ
  • 26,101
  • 30
  • 154
  • 305
  • What is your exact time zone? (CEST isn't a real time zone ID... it's a sort of half-time-zone-abbreviation which would apply in lots of different time zones.) – Jon Skeet May 27 '16 at 10:50
  • 1
    Why does your date picker add time information anyway? – RealSkeptic May 27 '16 at 10:58
  • CEST is CET+1. Daylight saving time in CET https://www.timeanddate.com/time/zones/cest – usr-local-ΕΨΗΕΛΩΝ May 27 '16 at 12:06
  • @RealSkeptic because of the already mentioned issue in AngularUI i guess? – usr-local-ΕΨΗΕΛΩΝ May 27 '16 at 12:10
  • @RealSkeptic I also checked Angular UI code, it massively uses Javascript's "new Date" function. See this example http://plnkr.co/edit/4uTkLY?p=preview where I omitted date formatting – usr-local-ΕΨΗΕΛΩΝ May 27 '16 at 13:55
  • 1
    I'm not an Angular expert, but I do believe you have to fix this on the Javascript side and then you won't have problems on the UI side. E.g. use a [filter](https://docs.angularjs.org/api/ng/filter/date) to get the shortened string from the date. – RealSkeptic May 27 '16 at 14:46
  • Actually filter is not the real issue, because filter works on display. We are trying to hack the directive itself to fix time to noon. I have posted on the bug too with reference to the GWT SO question. Trying to close the circle... hopefully before 11... PM... Central Europe Summer Time of course :-) – usr-local-ΕΨΗΕΛΩΝ May 27 '16 at 14:55
  • Filter doesn't have to work on display, you can filter an object and put the resulting string in the JSON instead of the full date and time string. – RealSkeptic May 27 '16 at 15:18
  • I agree with @RealSkeptic: there must be a way Angular can be set up to pass this date as a string. Have you thought about putting a bounty on this question? It is now eligible for one. – halfer May 30 '16 at 09:29
  • (If it is easier, you could always add a few hours to your JS time before sending it, so any TZ adjustments within your European zones do not make any difference. A horrible hack, but may be easier than changing types in Angular). – halfer May 30 '16 at 09:32

0 Answers0