Project

General

Profile

bug #8343

potential timezone problem in application stack

Added by Andreas Kohlbecker over 1 year ago. Updated about 1 year ago.

Status:
Feedback
Priority:
New
Assignee:
-
Category:
cdmlib
Target version:
Start date:
06/27/2019
Due date:
% Done:

0%

Severity:
normal
Found in Version:
Tags:

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

Related to Edit - task #8118: Update MySQL drivers Feedback 02/21/2019

History

#1 Updated by Andreas Kohlbecker over 1 year 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

#2 Updated by Andreas Müller about 1 year 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 the either the MySQL server is not correctly configured or the data is incorrect.

#3 Updated by Andreas Müller about 1 year ago

  • Related to task #8118: Update MySQL drivers added

#4 Updated by Andreas Müller about 1 year 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?

#5 Updated by Andreas Kohlbecker about 1 year 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

#6 Updated by Andreas Kohlbecker about 1 year 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.

Also available in: Atom PDF

Add picture from clipboard (Maximum size: 40 MB)