TIMESTAMPDIFF from particular timezone

I can get the timezone offset from the database time to UTC (0) using the following:

SELECT TIMESTAMPDIFF(HOUR, UTC_TIMESTAMP(), NOW());

How would I get the number of hours offset from UTC to a particular timezone, for example, “America/New_York” ?

Answer

If you have timezone support set up in MySQL (see the manual), you can do this:

SELECT TIMESTAMPDIFF(MINUTE, 
                     UTC_TIMESTAMP(), 
                     CONVERT_TZ(UTC_TIMESTAMP(), 'UTC', 'Australia/Adelaide')
                     ) / 60

Output

10.5

Note that you should take the difference in minutes and divide by 60 as some timezones have half hour differences.