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. Link to comment https://forums.phpfreaks.com/topic/141645-solved-this-may-stump-some-people-timestamp-gmt/ 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 Link to comment https://forums.phpfreaks.com/topic/141645-solved-this-may-stump-some-people-timestamp-gmt/#findComment-741428 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. Link to comment https://forums.phpfreaks.com/topic/141645-solved-this-may-stump-some-people-timestamp-gmt/#findComment-741439 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 Link to comment https://forums.phpfreaks.com/topic/141645-solved-this-may-stump-some-people-timestamp-gmt/#findComment-741442 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; Link to comment https://forums.phpfreaks.com/topic/141645-solved-this-may-stump-some-people-timestamp-gmt/#findComment-741456 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... Link to comment https://forums.phpfreaks.com/topic/141645-solved-this-may-stump-some-people-timestamp-gmt/#findComment-741590 Share on other sites More sharing options...
rhodesa Posted January 20, 2009 Share Posted January 20, 2009 um, -8 IS pst... Link to comment https://forums.phpfreaks.com/topic/141645-solved-this-may-stump-some-people-timestamp-gmt/#findComment-741606 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'] Link to comment https://forums.phpfreaks.com/topic/141645-solved-this-may-stump-some-people-timestamp-gmt/#findComment-741624 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. Link to comment https://forums.phpfreaks.com/topic/141645-solved-this-may-stump-some-people-timestamp-gmt/#findComment-741650 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'"); Link to comment https://forums.phpfreaks.com/topic/141645-solved-this-may-stump-some-people-timestamp-gmt/#findComment-742512 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 Link to comment https://forums.phpfreaks.com/topic/141645-solved-this-may-stump-some-people-timestamp-gmt/#findComment-742547 Share on other sites More sharing options...
NoDoze Posted January 21, 2009 Author Share Posted January 21, 2009 Cool! Worked! Thanks!!! Link to comment https://forums.phpfreaks.com/topic/141645-solved-this-may-stump-some-people-timestamp-gmt/#findComment-742659 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.