Reporting & GMT & MySql

[mikesolomon]

I started using ireports & was initially confused by the fact that all times are held in GMT

I came up with a solution for MySql and thought I'd share it

The trick is to use the MySql CONVERT_TZ function

First try SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');

If that returns null your timezone tables are not setup - for details on how to set them up see

http://dev.mysql.com/doc/refman/5.0/...e-support.html

Once that is done you can then convert dates in your reports to whatever timezone you like

for example

SELECT
name, TIME(CONVERT_TZ(date_start,'GMT','GB'))
FROM
tasks

That converts GMT to BST

To see the available timezone names look in the database mysql - table : time_zone_name

I hope you find this useful

[jsagar]

HI mikesolomon

thank you very much. this is really helpfull for me. great job