When we are working with big distributed database system, it is very important to take care about the default database time zone.
When we are selecting data from different countries, we should always work with UTC time zone because it is a World’s time standard and stand for Universal Time Coordinated.
In one of the our MySQL Database Server, we have stored all Date_Time related information based on local time zone.
But now, the problem is some of the users are manipulating same server from the different country.
This is creating a big problem for us because we cannot identify accurate Date_Time values.
The solution of this problem is to convert local time zone values to UTC time zone values.
SELECT local time:
SELECT NOW() AS Local_DateTime;
SELECT UTC time:
SELECT UTC_TIMESTAMP() AS UTC_DateTime;
Convert local time to UTC time:
SELECT CONVERT_TZ(NOW(), @@session.time_zone, '+00:00') AS UTC_DateTime;
My suggestion is to store all Date_Time information with UTC time zone only, whenever we are working with large and distributed database system.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.