bug #8343
closedpotential timezone problem in application stack
Added by Andreas Kohlbecker almost 5 years ago. Updated almost 2 years ago.
100%
Description
Ist denn genau definiert, um welche Zeitzone es sich handelt? Ich frage auch weil auf https://www.phycobank.org/cdm_dataportal/registration/http%253A%252F%252Fphycobank.org%252F100160 z.B.
2018-06-11 22:00:00 als DateTime genannt ist. Das sieht nach einer Verschiebung durch die Zeitzone aus. Vermutlich ist eher 2018-06-12 gemeint.
Aber auch sonst wäre natürlich interessant, ob Europe/Berlin oder UTC dargestellt wird, wenn man die Uhrzeit schon mit ausgibt.
2018-06-11 22:00:00
Na die Uhrzeit ist die typische Uhrzeit die du bekommst, wenn du Daten mit Zeitzone CEST eingibst und diese dann auf UTC (also 2 h rückwärts gerechnet werden). Das habe ich schon oft gesehen. Wäre schon sehr großer Zufall, wenn jemand die Daten wirklich auf die Sekunde um 22:00:00 eingegeben hätte. Gemeint ist aber vermutlich 2018-06-12 ohne Uhrzeit und das finde ich nicht so schön, dass das Datum sich da verschiebt.
Related issues
Updated by Andreas Kohlbecker almost 5 years ago
- Status changed from New to Rejected
- Target version deleted (
Unassigned CDM tickets)
I don't think that we are having a timezone shift problem since the date is stored in the db as 2018-06-11 22:00:00. This is at least what mysql is sending to the client.
Maybe this is an issue which comes from the time when the timezone data was not correctly set in for the mysql servers see #8118 and MySqlFaq#The-server-time-zone-value-is-unrecognized
Updated by Andreas Müller almost 5 years ago
- Status changed from Rejected to Feedback
- Target version set to Unassigned CDM tickets
I don't understand why this is rejected. The problem still exists as you can see in the above link. It might not be a dataportal issue so we may change the category. But by closing the ticket we will forget that either the MySQL server is not correctly configured or the data is incorrect.
Updated by Andreas Müller almost 5 years ago
- Related to task #8118: Update MySQL drivers added
Updated by Andreas Müller almost 5 years ago
Andreas Kohlbecker wrote:
Maybe this is an issue which comes from the time when the timezone data was not correctly set in for the mysql servers see #8118 and MySqlFaq#The-server-time-zone-value-is-unrecognized
Can you please provide a link to the above FAQ?
Updated by Andreas Kohlbecker almost 5 years ago
Andreas Müller wrote:
Andreas Kohlbecker wrote:
Maybe this is an issue which comes from the time when the timezone data was not correctly set in for the mysql servers see #8118 and MySqlFaq#The-server-time-zone-value-is-unrecognized
Can you please provide a link to the above FAQ?
here it is: MySqlFaq
Updated by Andreas Kohlbecker almost 5 years ago
- Subject changed from potential timezone problem in format_datetime() to potential timezone problem in application stack
- Category changed from cdm-dataportal to cdmlib
- Assignee deleted (
Andreas Kohlbecker)
Andreas Müller wrote:
I don't understand why this is rejected. The problem still exists as you can see in the above link. It might not be a dataportal issue so we may change the category. But by closing the ticket we will forget the either the MySQL server is not correctly configured or the data is incorrect.
"MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME." (see https://dev.mysql.com/doc/refman/5.6/en/datetime.html). So it seems as if the timezone was set to UTC+2 (=CEST) when 2018-06-12 was stored in the db, so it was filed as 2018-06-11 22:00:00.
If the whole application stack from client to server is respecting and handling the zimezone correctly the datetime should come out as 2018-06-12. This in deed needs more reseach. I am changing the subject to better reflect the general importance of this issue.
Updated by Andreas Müller over 2 years ago
- Assignee set to Andreas Kohlbecker
- Target version changed from Unassigned CDM tickets to Release 5.45
Is this solved in the meanwhile? https://www.phycobank.org/cdm_dataportal/registration-search/filter?identifier=100160 now shows the correct date (though for datetime with time 00:00:00 we should probably better remove the time during formatting)
Updated by Andreas Kohlbecker over 2 years ago
- Assignee changed from Andreas Kohlbecker to Andreas Müller
Andreas Müller wrote in #note-7:
Is this solved in the meanwhile? https://www.phycobank.org/cdm_dataportal/registration-search/filter?identifier=100160 now shows the correct date (though for datetime with time 00:00:00 we should probably better remove the time during formatting)
I am a bit confused by your last comment. In comment 6 you explained that this issue is of general importance for the whole application stack. An topic which "in deed needs more reseach". I dont unterstand how we can interpolate from one single place in the UI with correctly shown dates, to the rest of the stack.
Updated by Andreas Müller over 2 years ago
Andreas Kohlbecker wrote in #note-8:
Andreas Müller wrote in #note-7:
Is this solved in the meanwhile? https://www.phycobank.org/cdm_dataportal/registration-search/filter?identifier=100160 now shows the correct date (though for datetime with time 00:00:00 we should probably better remove the time during formatting)
I am a bit confused by your last comment. In comment 6 you explained that this issue is of general importance for the whole application stack. An topic which "in deed needs more reseach". I dont unterstand how we can interpolate from one single place in the UI with correctly shown dates, to the rest of the stack.
Comment 6 was yours not mine. However, I also do not understand why I said "{link} now shows the correct date". At least now it shows 2018-06-11 22:00:00 (again?) which seems not correct (but I didn't check the stored data).
Updated by Andreas Müller about 2 years ago
- Assignee changed from Andreas Müller to Andreas Kohlbecker
Updated by Andreas Kohlbecker about 2 years ago
- Assignee changed from Andreas Kohlbecker to Andreas Müller
Updated by Katja Luther almost 2 years ago
Andreas Müller wrote in #note-9:
Andreas Kohlbecker wrote in #note-8:
Andreas Müller wrote in #note-7:
Is this solved in the meanwhile? https://www.phycobank.org/cdm_dataportal/registration-search/filter?identifier=100160 now shows the correct date (though for datetime with time 00:00:00 we should probably better remove the time during formatting)
I am a bit confused by your last comment. In comment 6 you explained that this issue is of general importance for the whole application stack. An topic which "in deed needs more reseach". I dont unterstand how we can interpolate from one single place in the UI with correctly shown dates, to the rest of the stack.
Comment 6 was yours not mine. However, I also do not understand why I said "{link} now shows the correct date". At least now it shows 2018-06-11 22:00:00 (again?) which seems not correct (but I didn't check the stored data).
Actually in the database there are dates like "1998-10-19 22:00:00" as well as "1998-10-19 23:00:00", both are showing as 1998-10-20 00:00:00 in Berlin in the portal output.
The timezone of the server is
SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| Europe/Berlin | Europe/Berlin |
+--------------------+---------------------+
Updated by Katja Luther almost 2 years ago
Locally I added the timezone to the out put and for http://phycobank.org/100158 the timezone was CEST and for http://phycobank.org/100145 the timezone was CET. Actually there should be no timezone information in DATETIME??
For the test data the timezone was CET.
Updated by Andreas Müller almost 2 years ago
- Related to bug #10079: Fix timezone problems after upgrading jadira.usertype, hibernate and/or mysql drivers added
Updated by Andreas Müller almost 2 years ago
Katja Luther wrote in #note-13:
... Actually there should be no timezone information in DATETIME??
Are we talking about MySQL DATETIME? This is somehow true, but togehter with the global timezone of the DB Server there should be a timezone and also joda DateTime always includes a timezone information.
For the test data the timezone was CET.
CET or CEST depends on the date being in winter or summer not on the server.
Updated by Andreas Müller almost 2 years ago
- Status changed from Feedback to In Progress
- Priority changed from New to Highest
- Target version changed from Release 5.45 to Release 5.32
- % Done changed from 0 to 30
Updated by Andreas Müller almost 2 years ago
- Status changed from In Progress to Closed
- % Done changed from 30 to 100
I think this ticket can be closed. The timezone problem is now solved with correct availability of timezone information in webservices after upgrading to jadira-usertype 7.0.
Addtional formatting requirements for phycobank are handled in #10079#note-8