CODEWITHSUNDEEP
phpjavascriptjquerymysqlhtml
Hirvesh
Hirvesh

Reputation: 7982

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

Upvotes: 0

Views: 205

Answers (3)

Jerome WAGNER
Jerome WAGNER

Reputation: 22442

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.

Upvotes: 0

awm
awm

Reputation: 6570

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.

Upvotes: 1

Derek Downey
Derek Downey

Reputation: 1532

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

Upvotes: 1

Related Questions