Jonob Posted October 20, 2009 Share Posted October 20, 2009 Hi all, I have a timestamp field in a mysql database with a default value of 'CURRENT_TIMESTAMP'. My server is located in GMT, but I may have a user in a different timezone, lets say GMT + 10. So, he saves a record into the table at 09.00AM on 20 Oct 2009, but db records this as 19 Oct 2009 23:00:00. User then wants to see when record was created, and it says 19 October..and then scratches his head because he is sure that he saved it on 20 October. Is there any way to get around this, other than having to save every user's timezone in the db? i.e. would I have to manually calculate the time for each insert in php, rather than relying on Mysql's CURRENT_TIMESTAMP as a default? I am hoping that there is a simpler way to get around this... Quote Link to comment Share on other sites More sharing options...
Mchl Posted October 20, 2009 Share Posted October 20, 2009 Just recalculate the timestamp according to users timezone when displaying (not when saving). Quote Link to comment Share on other sites More sharing options...
bugz-2849 Posted October 20, 2009 Share Posted October 20, 2009 I too am having this problem. When data is submitted, it submits it under the server's current timezone. I want my website to display the data with how long ago the data was submitted. For example: The server is 3 hours ahead of me. If I submit data at 13:00, and I view the data on my website at 3 hours later, I want it to say "Age = 3 hours old". At the moment, it says it is 3 hours old as soon as it's submitted. Anyone help? Quote Link to comment Share on other sites More sharing options...
Jonob Posted October 20, 2009 Author Share Posted October 20, 2009 Just recalculate the timestamp according to users timezone when displaying (not when saving). That assumes that the user's timezone has been saved by the user in the db, right? Quote Link to comment Share on other sites More sharing options...
Mchl Posted October 20, 2009 Share Posted October 20, 2009 Or that it is otherwise known at the moment of displaying data. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 20, 2009 Share Posted October 20, 2009 Actually, if you simply set the timezone when you connect to the DB, mysql will take care of all of this for you. Quote Link to comment Share on other sites More sharing options...
bugz-2849 Posted October 20, 2009 Share Posted October 20, 2009 How do we do this? Quote Link to comment Share on other sites More sharing options...
bugz-2849 Posted October 20, 2009 Share Posted October 20, 2009 This is my command: $sql = mysql_query("UPDATE $dbname.$astroTable SET Time = NOW() WHERE astroLoc = '$db_astroLoc'"); I read that I can convert timezones but not sure how to do this. Anyone know? Quote Link to comment Share on other sites More sharing options...
Mchl Posted October 20, 2009 Share Posted October 20, 2009 http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html SET time_zone = 'GMT'; etc. Quote Link to comment Share on other sites More sharing options...
Jonob Posted October 21, 2009 Author Share Posted October 21, 2009 Right, but you still need to know the timezone of the client. Quote Link to comment Share on other sites More sharing options...
Mchl Posted October 21, 2009 Share Posted October 21, 2009 Yes you do Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.