YES Another TIME_ZONE question

whatsthis

Hi why can't I set the GLOBAL TIME_ZONE ?

I have a connections script which is called when I do a mysql query. In this script after setting the Host, User, Password I've added this

$timezone='Europe/London';

$link = mysql_connect($hostname, $username, $password);
mysql_query("SET GLOBAL time_zone = $timezone");

Then on the web page after doing a mysql query I have

here in the uk the date is ". date('l, F jS, Y,')." and the time is ".date('g:i a');

But whatever I set $timezone to it never changes from London +2 hours. even though my host is in the states.

Before someone sends it, here is the web link.

http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

Regards

bogu

Do u have a dedicated server?

If you have the SUPER privilege, you can set the global value at runtime with this statement:

SET GLOBAL time_zone = timezone;

whatsthis

No I doubt that I have a dedicated server. I'm trying freehostia.com at the moment

So as it is not possible to change the global time_zone do I need to go back to changing it every time I display it on my site with '+ 6 HOUR' ?

Can I change the timestamp setting in mysql so every time a new record is made the timestamp is in uk time zone ?

regards

bogu

U should check php function gmdate.

MarkR

You should set the default timezone in PHP using date_default_timezone_set. This will affect the behaviour of getdate() and its friends.

You should probably store all database date/times (retrospective ones, at least) in GMT. To get this in mysql, use the mysql utc_timestamp() function. To get it in php, use gmdate() with some suitable format for the database.

Then when getting dates out of the database, convert them to unix timestamps, then back into a suitable text format for output. Job done.

The only snag is, that as we go from summer / winter time, retrospective dates will appear to change as they'll be displayed in the current local time. If this is undesirable, store everything in UTC.

Mark

whatsthis

Thanks for your help chaps.

I'm still working on it, as I need to set one time_zone for when I'm on the local and a different one for the remote.