Daily Archives: 28 June 2012

Adding timezone information in MySQL

Posted on by
Reply

Problem
You would like to convert times in MySQL with the CONVERT_TZ function and to be able to use timezone names (ie CE).

Problem

  1. Find out if your MySQL includes timezone information as it is not included by default. Log in and run : select count(*) from mysql.time_zone_name;
  2. If the result is 0 then the timezone information is not included
  3. Log out from your MySQL server on the command prompt and upgrade your server with: mysql_upgrade -p
  4. On the command prompt again load up the table with the data (linux server): mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql -u root -p mysql/li>
  5. Login to your MySQL server again and run the first query: select count(*) from mysql.time_zone_name;