Server Time Problem

I've got a little script I'm working on which saves a comment to a mysql database, the database having a field of type "timestamp" and default set to "CURRENT_TIMESTAMP"

On the client side, I'm using the timeago jquery plugin (http://timeago.yarp.com/) wihch takes a UTC timestamp and converts it to a relative time format like "5 minutes ago"

However, my server time is set in a different time zo开发者_如何学JAVAne. This means that, if I'm posting a comment at 4 o'clock, the timestamp being put in the timestamp field is that of the server time, which is behind my local time. Thus when I'm posting a comment which is only a few minutes old, the jquery timeago plugin is showing something like "four hours ago"

How do I solve this problem. I'm stumped.

Use the UTC_TIMESTAMP function: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_utc-timestamp

All your timestamps should be in UTC, regardless of your server's local time. It's probably being converted somewhere along the way... e.g. as you're retrieving it.

I have all my timestamps stored as unixtime (unsigned integers) and convert them when they need to be displayed.

You must tell mysql to not be dependant on the timezone of your server. One way to do that is to execute the following command :

mysql> SET time_zone = '+00:00';

this way, your requests with time will all be on the UTC timezone.

check this documentation for further explanations.

your problem comes from the fact that CURRENT_TIMESTAMP gives you the timestamp of the current timezone of the mysql server. If not set, the timezone of the mysql server inherits the timezone of the server.