Monday, December 24, 2012

Mysql Setting Indian TimeZone (Asia/Calcutta)

Problem : 
The following query : 
select count(1) cnt,date_format(from_unixtime(absoluteTime),'%D-%M-%Y') as date1 from a,b where = b.fid and b.col2 = 'xyz' group by date1 order by absoluteTime asc

Gave different results on my local machine as compared to the server hosted on EC2.

When I did : 
mysql> show variables like '%time_zone%

I got : 

| Variable_name    | Value         |
| system_time_zone | EST           |
| time_zone        | SYSTEM |

Which means that mysql is picking the system time zone which is EST(some US time zone).

Solution : 
1. I checked time_zone* tables in mysql db - they were empty. So I did 2.
2. mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql (Source)
3. I edited /etc/my.cnf file and put this line there : default_time_zone=Asia/Calcutta   

now service mysqld restart

No comments:

Blog Archive