* Hassan Shaikh
> How do I change datetime from one time zone to another? I've a
> column in one of my tables having the type DATETIME and I need to
> show date AND time to end-user based on his/her preference.
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.
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. To calculate user time
you would take current server time plus server timezone offset minus user
timezone offset:
"DATE_ADD(datecol,INTERVAL ".($servertimezone-$usertimezone)." HOUR) AS
userdatecol "
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.
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
