Pioul Pioul - 6 months ago 27
MySQL Question

MySQL: Get local time for a specific time zone

Here's a simple version of the table

users
:

+--------------+-------------------+
| id | timezone |
+--------------+-------------------+
| 1 | 'Europe/Helsinki' |
| 2 | 'Europe/Paris' |
+--------------+-------------------+


I want to know what's the local time for each one of these users (depending on their time zones), so that I can select users for who it's 4pm for example.

I'm using the LAMP stack, but I'd like to do that using MySQL only (not selecting all users and running them in a PHP loop).

Nin Nin
Answer Source

Use the CONVERT_TZ for this: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz

SELECT * FROM users WHERE hour(CONVERT_TZ(now(), server_tz, `timezone`))=16
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download