Question :
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.
Answer :
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.