Sunday, February 11, 2007

MySQL spanning different timezones

So what, if you are planning a centralized MySQL server and many clients all around the world?
First of all, check the manual
Then install timezone description tables from here
Then run a small example to see how the server and client timezones can interact.
Here is the standard server setup

mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | SYSTEM |
+---------------+--------+
1 row in set (0.00 sec)

Now add a table for our experiments

mysql> create table time_table (datetime_col datetime);
Query OK, 0 rows affected (0.11 sec)

Let's add some data in that table.
Note that I'm not setting any option for the client time_zone

mysql> insert into time_table (datetime_col) values (now());
Query OK, 1 row affected (0.05 sec)

mysql> select * from time_table;
+---------------------+
| datetime_col |
+---------------------+
| 2007-02-11 13:57:00 |
+---------------------+
1 row in set (0.00 sec)

Ok, that's the current time (I'm connecting from the command line to a server running on the same PC), now I'll set the client timezone, on the same connection!

mysql> set time_zone = 'Europe/London';
Query OK, 0 rows affected (0.01 sec)

mysql> insert into time_table (datetime_col) values (now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from time_table;
+---------------------+
| datetime_col |
+---------------------+
| 2007-02-11 13:57:00 |
| 2007-02-11 12:57:28 |
+---------------------+
2 rows in set (0.00 sec)

Hey I just set a timezone and voilĂ , time travelling ;)
Now I'm connecting from another client, no special timezone setting

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.33 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> insert into time_table (datetime_col) values (now());
Query OK, 1 row affected (0.02 sec)

mysql>

Back to the first connection:

mysql> select * from time_table;
+---------------------+
| datetime_col |
+---------------------+
| 2007-02-11 13:57:00 |
| 2007-02-11 12:57:28 |
| 2007-02-11 13:58:32 |
+---------------------+
3 rows in set (0.00 sec)

So I really set the timezone for a specific connection!!
This is handy, but could bite, see

mysql> select * from time_table order by datetime_col desc;
+---------------------+
| datetime_col |
+---------------------+
| 2007-02-11 13:58:32 |
| 2007-02-11 13:57:00 |
| 2007-02-11 12:57:28 |
+---------------------+
3 rows in set (0.02 sec)

Rows were NOT inserted in this order!
But when the remote client loads that row will shurely want to see date and time in his own terms.

I know of the CONVERT_TZ() function, but I don't see it useful in this case if a "datetime with timezone" or "timestamp with timezone" data type is not supported (PostgreSQL does support it), I know I could store in another column the timezone but it's not a really practical solution.
Can anyone shed any light on this?

4 comments:

Anonymous said...

Maybe you want to use a TIMESTAMP datatype instead of DATETIME.

Felix said...

We run out server in GMT so all our dates are always stored in gmt. For items that need to handle stuff in a different timezone we store their timezone in a field. When displaying the data we convert from GMT either with CONVERT_TZ or get it with UNIX_TIMESTAMP( ) and format the dates with php's date time/date functions.

pabloj said...

Hi Felix,
that's the work around I thought of, but I hoped it could be avoided with proper datetime with timezone.

pabloj said...

@anonimous:
That won't work as there is no "timestamp with time zone" support