Skip to content
Advertisement

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” ?

Advertisement

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.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement