In MySQL caculating offset for a time zone

Question

Is there a way in mysql to calculate the offset for any timezone. For example to get the local time in the time zone 'Asia/calcutta' what i want to do is calculate the offset for this time zone and add that offset to GMT to get the local time.

Answer 1

DATEDIFF(NOW(), UTC_TIMESTAMP())

Cheers,
Eric

Answer 2

The offset will depend on the time that you're interested in - for instance, I'd currently have an offset of one hour from UTC, but during the winter my offset would be zero.

Judging by the docs page on MySQL time zone support, you want to use the convert_tz function. If you're trying to convert UTC to local time, pass in "Etc/GMT+0" as the "from" time zone, and "Asia/Calcutta" as the "to".

Answer 3

SET time_zone = '-07:00';

You can just pass in an offset

http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html