The date/time returned by the server is in the timezone of the server. If you need to convert it to a different timezone, you must do this within your application. It can be done directly in the SELECT statement. PHP example:
$usertimezone = get_user_prefs('timezone'); $res = mysql_query("SELECT *,". "DATE_ADD(datecol,INTERVAL $usertimezone HOUR) AS userdatecol ". "FROM tablename");
The 'datecol' would still hold the server date/time, while the 'userdatecol' column would be the same date/time in the user timezone.
The $usertimezone contains an integer between -12 and +12, representing the offset in hours of the user timezone compared to the server timezone. A user in the same timezone as the server would have $usertimezone=0.
Some locations have timezone offsets that are not full hours, and with the addition of DST can be more as 12 hours.
If the server(s) also can exist in different timezones, you would need an additional $servertimezone integer, and you should use GMT as the zero point for both the server and the user timezone settings.
Not GMT, but UTC.
As an alternative you could set all your servers to use GMT time, regardless of the geographical placement of the server. That way you would only need an $usertimezone offset.
The proper way to program this would require MySQL to support the AT TIME ZONE construct, but I haven't found it on the MySQL roadmap yet.
Jochem
--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
