MySQL timezone support - setting from PHP

I am using the MySQL date and time functions and they save a lot
of my time. But the server is located in a time zone 2 hours
away. I read in the SQL docs how to set the timezone on a
per-connection basis, but how would I do that from PHP ?

bill

Re: MySQL timezone support - setting from PHP

On Sun, 14 Oct 2007 20:54:40 +0200, bill wrote:

> I am using the MySQL date and time functions and they save a lot of my=
=

> time. But the server is located in a time zone 2 hours away. I read =
in =

> the SQL docs how to set the timezone on a per-connection basis, but ho=
w =

> would I do that from PHP ?

Untested:
mysql_query('SET time_zone =3D timezone_name');

Where the MySQL manual states:
timezone values can be given in several formats, none of which are case =
=

sensitive:
1. The value 'SYSTEM' indicates that the time zone should be the same as=
=

the system time zone.
2. The value can be given as a string indicating an offset from UTC, suc=
h =

as '+10:00' or '-6:00'.
3. The value can be given as a named time zone, such as 'Europe/Helsinki=
', =

'US/Eastern', or 'MET'. Named time zones can be used only if the time zo=
ne =

information tables in the mysql database have been created and populated=
..

Ideally, you could use:
mysql_query("SET time_zone =3D '".date_default_timezone_get()."'");

-- =

Rik Wasmus

Re: MySQL timezone support - setting from PHP

Rik Wasmus wrote:
> On Sun, 14 Oct 2007 20:54:40 +0200, bill wrote:
>
>> I am using the MySQL date and time functions and they save a lot of my
>> time. But the server is located in a time zone 2 hours away. I read
>> in the SQL docs how to set the timezone on a per-connection basis, but
>> how would I do that from PHP ?
>
> Untested:
> mysql_query('SET time_zone = timezone_name');
>
> Where the MySQL manual states:
> timezone values can be given in several formats, none of which are case
> sensitive:
> 1. The value 'SYSTEM' indicates that the time zone should be the same as
> the system time zone.
> 2. The value can be given as a string indicating an offset from UTC,
> such as '+10:00' or '-6:00'.
> 3. The value can be given as a named time zone, such as
> 'Europe/Helsinki', 'US/Eastern', or 'MET'. Named time zones can be used
> only if the time zone information tables in the mysql database have been
> created and populated.
>
> Ideally, you could use:
> mysql_query("SET time_zone = '".date_default_timezone_get()."'");
>
Thanks RIK, I haven't had time to test it yet, I'll let you know.

bill

Re: MySQL timezone support - setting from PHP

On Oct 14, 3:10 pm, "Rik Wasmus" wrote:
> On Sun, 14 Oct 2007 20:54:40 +0200, bill wrote:
> > I am using the MySQL date and time functions and they save a lot of my
> > time. But the server is located in a time zone 2 hours away. I read in
> > the SQL docs how to set the timezone on a per-connection basis, but how
> > would I do that from PHP ?
>
> Untested:
> mysql_query('SET time_zone = timezone_name');

tested mysql_query('SET time_zone = US/Eastern');
result: no error, no change at all.
datetime is still two hours off.

Re: MySQL timezone support - setting from PHP

bill wrote:
> On Oct 14, 3:10 pm, "Rik Wasmus" wrote:
>> On Sun, 14 Oct 2007 20:54:40 +0200, bill wrote:
>>> I am using the MySQL date and time functions and they save a lot of my
>>> time. But the server is located in a time zone 2 hours away. I read in
>>> the SQL docs how to set the timezone on a per-connection basis, but how
>>> would I do that from PHP ?
>> Untested:
>> mysql_query('SET time_zone = timezone_name');
>
> tested mysql_query('SET time_zone = US/Eastern');
> result: no error, no change at all.
> datetime is still two hours off.
>
correction, time is still one hour off - no change.
(it is too early to read an analog clock)
bill