4

I have one master database (MySQL) hosted on Ubuntu Server with the machine timezone set to America/New York (Default). Below command is showing that MySQL is using system timezone.

mysql> @@global.time_zone, @@session.time_zone

I have 3 Servers hosted on different regions. Basically regions near to user base.

Server 1 : Hosted on Europe Region.
Server 2 : Hosted on North America Region.
Server 3 : Hosted on South America Region.

These servers are managed through load-balancers and using Round-robin method. All these servers are storing data on same MySQL server.

All these servers have their native timezones and all are different timezones.

I want to scale master database to take on the extra load and also want to add some new servers at asia.

Does using different timezones for multiple servers cause problems in near future ? Or should I use same time zone for all servers (America/New York) and database server too ? But I want to use mysqli date functions when selecting data and also want to show the data into user native timezone.

Currently I am converting all dates to America/New York timezone and then storing in MySQL database, but it may take performance hit. And displaying to users by converting them by their country.

What is the best approach to do this?

Btw I'm using Apache,Mysql and PHP.

Thanks in advance.

Mickaël Leger
  • 3,426
  • 2
  • 17
  • 36
Kim
  • 275
  • 1
  • 10
  • *"What is the best approach to do this?"* What are you really asking here? if you can run into invalid converted times, performance issues or both? – Raymond Nijland Jun 24 '19 at 14:56
  • @RaymondNijland both, All servers have different timezones. Now suppose a user access server 1 with europe timezone and do some action then a record will be inserted to database into datetime or timestamp column such as id = 1, datetime = 2019-06-06 10:10:20 but if another user from server 3 done an action then the time inserted will be according to Newyork timezone. Thus causing time problem with database. – Kim Jun 24 '19 at 15:06
  • *"Thus causing time problem with database"* i don't see a problem with that user case that is how it should work.. the problem comes i suspect when you need to have the data from all servers togther or query a America server from a EU connection/zonetime and see the correct times? – Raymond Nijland Jun 24 '19 at 15:13
  • @RaymondNijland, Yea similar to this, I guess UTC is good to handle global visitors, but how to set it, Utc has time limitations and thus can't be used to store dates. Another example would be a real time chat app, where multiple users connected to different servers and chatting with each other. Now in chat user 1 shows last message as 2019-24-06, while other user shows 2019-23-06 due to various timezones. Also it will be mess to sort and order these messages. – Kim Jun 24 '19 at 15:20
  • 1
    i think you should read [Should MySQL have its timezone set to UTC?](https://stackoverflow.com/questions/19023978/should-mysql-have-its-timezone-set-to-utc) – Raymond Nijland Jun 24 '19 at 15:23
  • also because `CONVERT_TZ()` can be used to convert from UTC to all timezones on the fly.. – Raymond Nijland Jun 24 '19 at 15:27
  • Does it have any performance issue, when selecting many rows like SELECT *. – Kim Jun 24 '19 at 15:33
  • *"Does it have any performance issue, when selecting many rows like SELECT *."* Yes it can.. – Raymond Nijland Jun 24 '19 at 15:34
  • @RaymondNijland, Readed your provided link. So is it okay if i set all servers timezone to 'America/New York' and also php timezone like ini_set("date.timezone", "America/New York"); date_default_timezone_set('America/New York'); and then mysql database standard to UTC and then just insert it normally from all servers to database and convert it user's timezone when retrieving from database. As Mysql stores datatime as UTC internally. – Kim Jun 24 '19 at 15:43
  • *"It seems that it does not matter what timezone is on the server as long as you have the time set right for the current timezone, know the timezone of the datetime columns that you store, and are aware of the issues with daylight savings time. On the other hand if you have control of the timezones of the servers you work with then you can have everything set to UTC internally and never worry about timezones and DST. "* (which was copy/paste from the accepted answer) That basically covers your question. – Raymond Nijland Jun 24 '19 at 15:44
  • 1
    @RaymondNijland, Thanks for help, I will make these steps and let you know how it goes. – Kim Jun 24 '19 at 15:45

1 Answers1

2

WHAT IS DST?

DST refers to Daylight saving time or Summer time. When DST starts in the spring, our clocks are set forward by a certain amount of time, usually by 1 hour. This means that 1 hour is skipped, and on the clock, the day of the DST transition has only 23 hours.

A QUICK EXAMPLE OF DST :

This example will show you effects of DST. I am using Windows 10 as my operating system.

Follow below steps to see what DST means in practical.

STEP 1 : Right Click on Date and Time(avaliable at Right Bottom corner in windows taskbar).

STEP 2 : Click Adjust date/time option.

STEP 3 : Turn ON - "Adjust for daylight savings automatically" Option.

STEP 4 : Set "Time zone" to (UTC-6:00 Central Time (US & Canada)).

STEP 5 : Turn OFF - "Set time automatically" Option.

STEP 6 : Under Change date and time label, Click Change.

STEP 7 : Set Date to March/10/2019 and time to 1:59 AM and click on Change.

- Now look at your PC time after 1 Minute and instead of 2:00 AM it clocks to 3:00 AM. That's because in summer(when DST time starts) your clock is set to 1 hour forward the actual time, and goes back 1 hour when the DST time ends.

- Daylight saving time 2019 in United States began at 2:00 am on Sunday, 10 March and ends at 2:00 am on Sunday, 3 November

*Don't worry about the extra 1 hour added to your PC time, It will be subtracted by 3 November 2019 at 2:00 am.

ISSUES WITH DAYLIGHT SAVING TIME/SUMMER TIME :

Since the time does not follow a primary time standard, it may cause some issues for server applications. Below are some most affected area by DST enabled timezone.

  • Realtime Chat Applications.
  • Scaling Servers across multiple regions.
  • Logging Data.
  • Transactional Data.
  • Database concurrency.
  • NoSQL Database scaling.
  • Performance Hits.
  • Handling Global Visitors.
  • and Other uses.

Solutions for DST :

- Change MySQL Timezone to UTC(+0:00), Just Change my.conf file(also known as My.INI, my.CNF) add below code:

[mysqld]
default-time-zone=+00:00

- PHP timezone to UTC, to do just use below code :

ini_set("date.timezone", "UTC");

- Change Server/System timezone to UTC.

SOME KEYPOINTS ABOUT DST :

  • DST Does not apply to Universal Time Coordinated(UTC). Since Its based on coordinated time scale.

  • Some Countries have not implemented DST and instead using their own timezone based on UTC differences.For Example - India uses IST(Indian Standard Time) set to Asia/Kolkata or UTC+5:30.Thus you don't have to take care about changing clock for these countries.

Kim
  • 275
  • 1
  • 10