MySQL timezone support - setting from PHP

[bill]

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

[Rik Wasmus]

On Sun, 14 Oct 2007 20:54:40 +0200, bill <no****@spamcop.netwrote:

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()."'");

--
Rik Wasmus

[bill]

Rik Wasmus wrote:

On Sun, 14 Oct 2007 20:54:40 +0200, bill <no****@spamcop.netwrote:

>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

[bill]

On Oct 14, 3:10 pm, "Rik Wasmus" <[e-mail]:

On Sun, 14 Oct 2007 20:54:40 +0200, bill <[e-mail]:

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.

[bill]

bill wrote:

On Oct 14, 3:10 pm, "Rik Wasmus" <[e-mail]:

>On Sun, 14 Oct 2007 20:54:40 +0200, bill <[e-mail]:

>>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