Convert timestamp between timezones : CONVERT_TZ() – MySql
Any application targeted for people in different countries or even in different cities requires to handle timezones for the users for application. One main requirement is to show data against user’s timezone e.g. creation date & time of any message to the user.
Many applications solve this by asking users their timezone at the time of registration and display part is more or less solved in this way. Most of the time these applications ask about your country and figure out by themselves about the timezone but there are some countries which are having multiple timezones and in that case time zone offset is used in GMT+- hh:mm format.
Problem arises when you also need to support the day light saving time. And it becomes more important when you need to do more than just displaying few values to the user like contacting certain people of certain timezone in certain time range.
There are ways to do support all different timezones with day light saving time and actually supported in many languages/platforms like Java and .Net. This is implemented using Zone info Database or Olsen Database
Mysql also provides this functionality but not in very standard way and probably that is the reason for non-popularity of this function. Another reason is that it requires some extra arrangements to use this function i.e. populating some of the timezone tables in mysql schema.
Here is the usage of this function:
1. SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
2. SELECT CONVERT_TZ(UTC_TIMESTAMP(),'GMT','MST');
Before using this function, just make sure that you have populated some of the timezone tables in mysql schema.
a. time_zone
b. time_zone_leap_second
c. time_zone_name
d. time_zone_transition
e. time_zone_transition_type
You can get these tables at http://dev.mysql.com/downloads/timezones.html and see more information about MySql timezone support at http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html
Here is an example of this function with the use case, suppose you want to send an e-mail to the user of your application on his birthday and users belong to multiple timezones having day light saving time. If you have following information , you can actually greet your user for his birthday as soon as date changes to his B’Day.
You need to run a thread every 5 minutes to check if any of your users have birthday.
a. user’s timezone information
b. user’s birth date.
SELECT DATE(CONVERT_TZ(UTC_TIMESTAMP(),'GMT','users-time-zone'));
if result is equal to the user’s birth date, you can send him B’Day wishes.
Most Commented Posts
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
[...] neioo. Do you have any idea what would be done in MySQL though? A fast Google search took me to: http://www.techiegyan.com/2009/11/05…vert_tz-mysql/ But since Oracle bought Sun, I don't use MySQL any [...]