Can MySQL convert a stored UTC time to local timezone?

Question

Can MySQL convert a stored UTC time to local time-zoned time directly in a normal select statement?

Let's say you have some data with a timestamp (UTC).

CREATE TABLE `SomeDateTable` (   `id`    int(11) NOT NULL auto_increment,   `value` float NOT NULL default '0',   `date`  datetime NOT NULL default '0000-00-00 00:00:00',   PRIMARY KEY  (`id`) ) 

Then when I

"select value, date from SomeDateTable"; 

I of course get all the dates as in their stored UTC form.

But let's say that I would like to have them in another timezone (with DST), can I then add some magic to the select query so that I get all the dates back in the selected timezone?

"select value, TIMEZONE(date, "Europe/Berlin") from SomeDateTable"; 

Or must I do this in some other layer on top, like in some php code? (it seems to be how most people have solved this problem).

---

If your MySQL installation allows you to use CONVERT_TZ it is a very clean solution, this example shows how to use it.

SELECT CONVERT_TZ( '2010-01-01 12:00', 'UTC', 'Europe/Stockholm' ) 

However I don't know if this is a good way since some MySQL installation is missing this function, use with care.

Answer 1

Yup, there's the convert_tz function.