In MySQL caculating offset for a time zone

mysql timezone offset
Varun picture Varun · Apr 30, 2009 · Viewed 31.7k times · Source

Is there a way in MySQL to calculate the offset for any timezone? For example, to get the local time in the timezone Asia/calcutta. What I want to do is calculate the offset for this timezone and add that offset to GMT to get the local time.

Answer

Kenneth Spencer picture Kenneth Spencer · Mar 14, 2012

If you want to calculate the offset of a time zone such as America/Vancouver from UTC you can do it as follows: 

SELECT (unix_timestamp() -  unix_timestamp(convert_tz(now(), 'Etc/UTC', 'America/Vancouver'))) / 3600   as offset;

For this to work you will first need to load the time zone information into mysql as outlined here: http://dev.mysql.com/doc/refman/5.0/en/mysql-tzinfo-to-sql.html

Related questions

How do I set the time zone of MySQL?

On one server, when I run: mysql> select now(); +---------------------+ | now() | +---------------------+ | 2009-05-30 16:54:29 | +---------------------+ 1 row in set (0.00 sec) On another server: mysql> select now(); +---------------------+ | now() | +---------------------+ | 2009-05-30 20:01:43 | +---------------------+ 1 row in set (0.00 sec)

Read More
MySQL JDBC Driver 5.1.33 - Time Zone Issue

Some background: I have a Java 1.6 webapp running on Tomcat 7. The database is MySQL 5.5. Previously, I was using Mysql JDBC driver 5.1.23 to connect to the DB. Everything worked. I recently upgraded to Mysql JDBC driver 5.1.33. After the upgrade, Tomcat would …

Read More
How do I get the current time zone of MySQL?

Anyone knows if there is such a function in MySQL? UPDATE This doesn't output any valid info: mysql> SELECT @@global.time_zone, @@session.time_zone; +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | SYSTEM | SYSTEM | +--------------------+---------------------+ …

Read More