0

Until this year Turkey was using daylight saving time, staying UTC+2 on winter period, UTC+3 on summer. This year it is decided to stay only in UTC+3.

Our DateTime's are persisted on MySQL (5.26.34) db (on AWS) was using UTC timezone.

When i try to use CONVERT_TZ(DateTime, UTC, EUROPE/ISTANBUL) for recent datetime -lets say- 2016-11-21 00:00 it tries to convert according to UTC+2, whereas it suppose to be UTC+3.

When i would tried to convert 2015-11-21 00:00 -last year- it suppose to convert according to UTC+2, since Turkey was in UTC+2 at that time.

I Found an announcement on AWS forum[1], advicing to use Moscow timezone, which is not an option for us since Turkey's timezone situation is unique and our DateTime's are saved in the form of UTC0.

https://forums.aws.amazon.com/ann.jspa?annID=4115

Improve this question
2
  • I think you just need to update tzdata info. The latest releases have this change. See a similar question at ServerFault: serverfault.com/questions/804403/…
    – ypercubeᵀᴹ
    Nov 21 '16 at 12:17
  • DST sucks; I wish we could get rid of it worldwide. We use the excuse that it helps the farmers. Bull. A cow does not look at the clock to decide that is is milking time.
    – Rick James
    Nov 21 '16 at 20:14
0

DATETIME is like a picture of a clock. There is no going back to fix it. A DATETIME column reflects what the user would see on a clock at the time he/she does the INSERT.

TIMESTAMP is stored internally as UTC, but converted going in and going out. So, if the timezone is set correctly, and if the tz table is correct, TIMESTAMP always reflects a point of time in the cosmos.

So, in addition to getting the tz code 'right', and setting the timezone correctly, use the 'right' datatype.

Improve this answer
1
  • it is not like we saved our datetime wrong in db. errorneous part is timezone info of mysql, you should read the question before answering.
    – ayyayyekokojambo
    Nov 22 '16 at 6:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.