MySQL: get local time for a specific time zone

Question

Here is a simple version of the users table:

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

I want to know what local time is for each of these users (depending on their time zones), so that I can choose users for whom it is, for example, 4 pm.

I use the LAMP stack, but I would like to do this only using MySQL (without selecting all users and not starting them in the PHP loop).

Answer 1

Use 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 

Answer 2

You can change the time zone with set time_zone :

 mysql> set time_zone='Europe/Helsinki'; mysql> select now(); 2012-09-21 16:15:06 mysql> set time_zone='Europe/Paris'; mysql> select now(); 2012-09-21 15:15:40 

Using this, you can, for example, define a function that returns the current time for a custom time zone:

 create function current_time_in_tz(tz varchar(40)) returns datetime begin set @old_tz = @@session.time_zone; set time_zone=tz; set @now = now(); set time_zone=@old _tz; return @now; end select id, current_time_in_tz(timezone) from users; 

Please note that the DATE, TIME, and DATETIME values ​​are independent of the time zone, so the values ​​of these column types are not automatically configured when prompted. TIMESTAMP values ​​are configurable:

 mysql> create temporary table tbl (dt datetime, ts timestamp); mysql> insert into tbl values (now(),now()); mysql> select * from tbl; +---------------------+---------------------+ | dt | ts | +---------------------+---------------------+ | 2012-09-21 15:21:56 | 2012-09-21 15:21:56 | +---------------------+---------------------+ mysql> set time_zone='Europe/Helsinki'; mysql> select * from tbl; +---------------------+---------------------+ | dt | ts | +---------------------+---------------------+ | 2012-09-21 15:21:56 | 2012-09-21 16:21:56 | +---------------------+---------------------+ 

If set time_zone fails to complete this error:

 ERROR 1298 (HY000): Unknown or incorrect time zone: 'Europe/Helsinki' 

you need to load timezone information in mysql with the following command:

 mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql 

See http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html for more details.

Answer 3

A more general (time zone-independent server) solution than Nin's answer:

 SELECT * FROM users WHERE hour( CONVERT_TZ(UTC_TIMESTAMP(), 'UTC', timezone) )=16 

It would be nice if MySQL had a function like NOW_TZ (time zone).