MySQL – Turkey/Istanbul Daylight Saving Time Change

amazon-rdsawsMySQLmysql-5.6timezone

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

Best 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.

Related Solutions

Mysql – How to compare (in MySQL) a DATETIME value to a TIMESTAMP value

There is a manual page on Time and Date functions.

The best way to compare date and time and get minutes back is probably to use one of the following:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)

Note that while TIMEDIFF works with times, TIMESTAMPDIFF requires a date component - and the values are switched around for TIMESTAMPDIFF.

You can return hours with either of these:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)

Note that when using DATEDIFF, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).

To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:

SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)

This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.

Mysql – How to ensure that date-range queries involving multiple time zones are sargable

Based on the query you provided, I would say:

For date_add field, I would definitely recommend that you separate the date part and time part, as this will allow you to group by a field instead of a function.
Assuming that you will always be passing a id_website, I would recommend you create a composite index covering, and in this order: id_website, date_add_date, date_add_time.
After that, do not perform a DATE_FORMAT on GROUP BY but simply pass date_add_date

Also, might be worth considering partitioning your main tables such as visit, either by date_add_date or by id_website, depending on your need. Might be worth checking out pitfall of table partitioning as well:

http://www.mysqlperformanceblog.com/2010/12/11/mysql-partitioning-can-save-you-or-kill-you/

Best Answer

Related Solutions

Mysql – How to compare (in MySQL) a DATETIME value to a TIMESTAMP value

Mysql – How to ensure that date-range queries involving multiple time zones are sargable

Related Question