22

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.

kio21
  • 639
  • 1
  • 6
  • 23
Varun
  • 4,054
  • 6
  • 31
  • 54

8 Answers8

19

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

drewish
  • 9,042
  • 9
  • 38
  • 51
Kenneth Spencer
  • 1,392
  • 12
  • 15
17

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.

ColinM
  • 13,367
  • 3
  • 42
  • 49
13
SELECT TIME_FORMAT(TIMEDIFF(NOW(), CONVERT_TZ(NOW(), 'Asia/Calcutta', 'UTC')), '%H:%i') AS offset;

Giving

+--------+
| offset |
+--------+
|  05:30 |
+--------+
Shibu
  • 141
  • 1
  • 6
  • Usually it is a good idea to expound on the code in your answer. Tell about how it works or what exactly you are doing. This helps newer developers understand exactly what is going on. – Caleb Kleveter Mar 09 '17 at 15:33
5

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".

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
1

I mixed up @ColinM and @Kenneth Spencer's answers because I wanted the offset of an arbitrary timezone in hours:

TIMESTAMPDIFF(HOUR, UTC_TIMESTAMP(), CONVERT_TZ(UTC_TIMESTAMP(), "UTC", "America/Denver"))

-

SELECT ROUND(TIMESTAMPDIFF(MINUTE, UTC_TIMESTAMP(), CONVERT_TZ(UTC_TIMESTAMP(), "Etc/UTC", "Asia/Kolkata"))/60,1)

As Kenneth pointed out you'll need to load the timezone info: http://dev.mysql.com/doc/refman/5.0/en/mysql-tzinfo-to-sql.html

But then you can do fun things like find the offset for multiple timezones at once:

SELECT Name,
TIMESTAMPDIFF(HOUR, UTC_TIMESTAMP(), CONVERT_TZ(UTC_TIMESTAMP(), "Etc/UTC", Name)) as Offset
FROM mysql.time_zone_name 
WHERE (Name IN ('America/Vancouver', 'Etc/UTC', 'America/Denver'))

Giving:

+-------------------+--------+
| Name              | Offset |
+-------------------+--------+
| America/Denver    |     -6 |
| America/Vancouver |     -7 |
| Etc/UTC           |      0 |
+-------------------+--------+
Timo Huovinen
  • 53,325
  • 33
  • 152
  • 143
drewish
  • 9,042
  • 9
  • 38
  • 51
0

I wonder if @ColinM's answer above is safe. Does it read the clock once or twice? Is there any possibility that UTC_TIMESTAMP() and NOW() could be one second apart? I don't know but this would avoid that.

SET @now = now();
SET @utc = CONVERT_TZ(@now, 'SYSTEM', '+00:00');
SELECT TIMESTAMPDIFF(MINUTE, @utc, @now);
tetranz
  • 1,932
  • 3
  • 24
  • 32
-1
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

Charles Himmer
  • 7
  • 1
  • Never use offset to set a timezone! Always use name, like "Europe/Prague". Otherwise you will be very surprised when DST occurs. – Josef Kufner Jan 07 '14 at 22:38
-1
DATEDIFF(NOW(), UTC_TIMESTAMP())
Eric
  • 92,005
  • 12
  • 114
  • 115