NoDoze Posted January 20, 2009 Share Posted January 20, 2009 ok, I have a mysql server that has a few databases on it. The server is running on local time (PST), and I need it to be on local time for the databases. However I have one database that needs to have it's timestamp on GMT. Is there a way to make the timestamp for that one DB to be GMT? Currently I have it set to 'on update current_timestamp' and 'current_timestamp'. So it's timestamping when the data is imported. This data is uploading via a URL in this format: $uweather = "http://domain.com/update.php?ID=username&PASSWORD=password&dateutc=" . $row['timestamp'] . "&tempf=" . $row['airt_f_avg'] . "&winddir=" . $row['winddir'] . "&windspeedmph=" . $row['ws_mph'] . "&windgustmph=" . $row['ws_max'] . "&rainin=" . $row['rain_in_tot'] . "&baromin=" . $row['baro'] . "&dewptf=" . $row['dewpt_f_avg'] . "&humidity=" . $row['rh_avg'] . "&weather=&clouds=&softwaretype=vws%20versionxx&action=updateraw&realtime=1&rtfreq=2.5"; Is there a way I could change the... $row['timestamp'] ...to be reformatted into GMT? Hope that makes sense and someone could help me.... Thanks. Quote Link to comment Share on other sites More sharing options...
rhodesa Posted January 20, 2009 Share Posted January 20, 2009 um...unix timestamps don't have timezone info associated with them. to convert between timezones in mysql though, use this function: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz just convert it when selecting Quote Link to comment Share on other sites More sharing options...
NoDoze Posted January 20, 2009 Author Share Posted January 20, 2009 uhmm...ok... the link shows how to do it from the command line. Do I set this for JUST that table? ...or database? ...cause I can't do this for EVERY entry, nor when data gets imported. How would I implement this? ...it's not clear. Quote Link to comment Share on other sites More sharing options...
rhodesa Posted January 20, 2009 Share Posted January 20, 2009 sorry...i misread your post...i don't know how to change the mysql default timezone config for just one db Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted January 20, 2009 Share Posted January 20, 2009 You can set or change the time zone for any/each connection - Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement: mysql> SET time_zone = timezone; Quote Link to comment Share on other sites More sharing options...
NoDoze Posted January 20, 2009 Author Share Posted January 20, 2009 mysql> SET time_zone = '-8:00'; ...I entered the above as the user who is uploading the data, but the data is still on PST... ...either I did it wrong or I'm missing something... Quote Link to comment Share on other sites More sharing options...
rhodesa Posted January 20, 2009 Share Posted January 20, 2009 um, -8 IS pst... Quote Link to comment Share on other sites More sharing options...
NoDoze Posted January 20, 2009 Author Share Posted January 20, 2009 Woops! I meant +8:00...sheeesh... ..but it still isn't working... Seams like it's not changing the user's time zone... Isn't there a way to change this line? $row['timestamp'] Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted January 20, 2009 Share Posted January 20, 2009 The setting is per-connection. The php script that is using the database server's date/time information must execute the SET time_zone = query. Quote Link to comment Share on other sites More sharing options...
NoDoze Posted January 21, 2009 Author Share Posted January 21, 2009 gotcha... I added it to the query, but I don't think it's right cause no data is showing up...this is what I have: $result = mysql_query("SELECT * FROM hicksws ORDER BY timestamp DESC LIMIT 1 SET time_zone = '+8:00'"); Quote Link to comment Share on other sites More sharing options...
rhodesa Posted January 21, 2009 Share Posted January 21, 2009 no...like this: $data = mysql_fetch_array(mysql_query("SELECT NOW()")); print "Server time (EST) is: {$data[0]}<br />"; mysql_query("SET time_zone = '+0:00'") or die(mysql_error()); $data = mysql_fetch_array(mysql_query("SELECT NOW()")); print "GMT time is: {$data[0]}<br />"; Server time (EST) is: 2009-01-21 15:19:25 GMT time is: 2009-01-21 20:19:25 Quote Link to comment Share on other sites More sharing options...
NoDoze Posted January 21, 2009 Author Share Posted January 21, 2009 Cool! Worked! Thanks!!! 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.