CODEWITHSUNDEEP
mysqltimezone
aaaaaa
aaaaaa

Reputation: 2618

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

Upvotes: 11

Views: 19090

Answers (3)

Mark1
Mark1

Reputation: 166

A more generic (not depending on the timezone of the server) solution than Nin's answer would be:

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

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

Upvotes: 5

Nin
Nin

Reputation: 3020

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

Upvotes: 10

Joni
Joni

Reputation: 111309

You can change the timezone 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 the user's timezone:

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;

Note that DATE, TIME and DATETIME values don't depend on the time zone, so values from columns of these types are not automatically adjusted when querying. TIMESTAMP values are adjusted:

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 with this error:

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

you need to load the time zone info into mysql with a command like this:

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

For more info see http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

Upvotes: 9

Related Questions