MySQL Timezone Adjustment

software development

#1

Hello, I’ve started using PHP and MySQL in my websites and I had a question. I just finished my own forum software for my site, but the only thing I don’t know how to do is set up a default time zone. Right now, everything is posted hours before me in a western time zone (not really sure which time zone it is in, nor have I really cared until now), but I’m in the eastern time zone. Is there a way to change the global variable for the timezone that (I think) I saw in phpmyadmin so I can use ‘CURRENT_TIMESTAMP’ or do I have to modify the time somehow and then insert it into my database? Sorry if this has been asked before, but the search feature on these forums either isn’t the greatest or I am not using it properly


#2

Your forum software should have that option in the admin panel and in your user profile settings.
So there really shouldn’t be any need to modify your PHP settings, and you definitely can’t modify the mysql settings.

Which forum software is it?


#3

I’m using dreamhost’s hosting service, so its MySQL via panel.dreamhost.com and via my domain/dh_phpmyadmin/mysql.my domain/. I just didn’t really know where to change it at I guess. Everything is just 3 hours behind.


#4

Like I said, you don’t modify MySQL settings as a user.
You can modify either create a custom PHP.ini (it’s in the wiki), or change your forum settings… which would be infinitely easier.


#5

The thing is, I designed the entire forum software myself, so I guess my question is - how do I modify the time? Is there a way to just add three hours to my variable?

Edit: Let me be more specific. I have a MySQL statement that looks like this “INSERT INTO forums VALUES ( … ,CURRENT_TIMESTAMP, … )” where the dots mean more pointless stuff that isn’t really needed in this forum post. Is there a way to add three hours to it in that statement?


#6

If you designed the software yourself, then you should have plenty of knowledge of PHP to add in a timezone modification.
As a suggestion, you might take a look at how PhpBB set their’s up and use that as a reference.

Otherwise I can’t help you any further, as that’s the only way you’re really going to be able to go about it. You COULD change the timezone in your PHP.ini file (you’d have to consult the DH wiki on how to set that up first), but as a public forum software (assuming that’s its intended purpose), I strongly suggest not doing so and coding your own timezone modifier into it instead.


#7

Thanks for the help, I just started out with PHP not too long ago, so I guess I just need to search online for more information on how to do it. When you say I “should have plenty of knowledge of PHP to add in a timezone modification,” I wish that was true :). I only know a little bit - enough to get me to where I am now. And yes, it is a public forum.

I guess I just figured modifying the software I was using would be easier and more efficient than modifying my code to change the time.


#8

No worries, I’m not PHP coder myself, but I’ve been “hacking up” forum systems for the last 5 years whenever someone goes “I want this feature!” or “OMG this broke!” :stuck_out_tongue:

I found checking out code from other, similar systems helped me get an idea of what I needed to be looking at/for, and went from there :wink:

Good luck in your project!


#9

Thanks very much. It isn’t really a big deal if it works or not as it does not affect the functionality of the website. I’ll figure it out sooner or later. Actually, chances are its something very simple like usual :D.


#10

i handle time zones on my site using two fields in my users table: tzadjust and dst. dst is a yes/no field which is whether the user should see times changed for daylight savings time. i use date() if dst is yes, or gmdate() if dst is no – dreamhost’s servers use daylight savings time, but of course gmdate doesn’t. tzadjust is a value that can be added directly to timestamp values passed to gmdate or date.

of course users have to specify these settings in order to get something other than server time. i do that by giving them a field showing the current time which they can edit to what they think the current time is, and a daylight savings time checkbox. on the server, i add ’ GMT’ to the end of the time the user entered if dst is unchecked, convert to a timestamp using strtotime, subtract off time(), then round to the nearest multiple of 1800 (30 minutes).