Can MySQL convert a stored UTC time to local timezone?

Can MySQL convert a stored UTC time to local timezon:ed 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).

Thanks Johan

---

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.

 Answers

Yup, the convert_tz function.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz

---

select convert_tz(now(),@@session.time_zone,'+03:00')

For get the time only use:

time(convert_tz(now(),@@session.time_zone,'+03:00'))

---

I am not sure what math can be done on a DATETIME data type, but if you are using 

PHP, I strongly recommend using the integer-based timestamps. Basically, you can

 store a 4-byte integer in the database using PHP's time() function. This makes doing 

math on it much more straightforward.