In MySQL caculating offset for a time zone
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
SELECT TIMESTAMPDIFF(HOUR, UTC_TIMESTAMP(), NOW());
If the server's timezone is PST this will return -8
.
SELECT TIMESTAMPDIFF(SECOND, NOW(), UTC_TIMESTAMP());
Add the result of the above to any unix timestamp if you want to compare it to MySQL DateTimes.
SELECT TIME_FORMAT(TIMEDIFF(NOW(), CONVERT_TZ(NOW(), 'Asia/Calcutta', 'UTC')), '%H:%i') AS offset;
Giving
+--------+
| offset |
+--------+
| 05:30 |
+--------+