Sorry your browser is not supported!

You are using an outdated browser that does not support modern web technologies, in order to use this site please update to a new browser.

Browsers supported include Chrome, FireFox, Safari, Opera, Internet Explorer 10+ or Microsoft Edge.

Geek Culture / php, mysql, and timezones ???

Author
Message
Phaelax
DBPro Master
15
Years of Service
User Offline
Joined: 16th Apr 2003
Location: Metropia
Posted: 7th May 2018 20:05
Never really dealt with timezones before, never had to. Right now, as I insert a new item into my database, my datetime field defaults to mysql's CURRENT_TIMESTAMP. When I retrieved my entries, I realized they were off by 5 hours which would be accurate given my timezone of UTC-5.

Some googling lead me to this solution when retrieving the dates in PHP:



Problem is, it's only subtracting 4 hours instead of 5 like it should given the selected timezone. For instance, I retrieve a time from the database that says 6pm when it was actually inserted at 1pm. However, I apply the timezone and end up with 2pm.

Sure, I could change the timezone to chicago and get the right answer, but it shouldn't be that way. Any thoughts?
Tiled TMX Importer V.2
XML Parser V.2
Base64 Encoder/Decoder
Purple Token - Free online hi-score database
Legend of Zelda

"I like offending people, because I think people who get offended should be offended." - Linus Torvalds
Ortu
DBPro Master
10
Years of Service
User Offline
Joined: 21st Nov 2007
Location: Austin, TX
Posted: 9th May 2018 02:10
Are you accounting for daylight savings? This can easily throw you off by 1 hour
http://games.joshkirklin.com/sulium

A single player RPG featuring a branching, player driven storyline of meaningful choices and multiple endings alongside challenging active combat and intelligent AI.
Phaelax
DBPro Master
15
Years of Service
User Offline
Joined: 16th Apr 2003
Location: Metropia
Posted: 9th May 2018 13:00
I didn't think about that. And my server is in a different country, like cypress or something weird I think.
Tiled TMX Importer V.2
XML Parser V.2
Base64 Encoder/Decoder
Purple Token - Free online hi-score database
Legend of Zelda

"I like offending people, because I think people who get offended should be offended." - Linus Torvalds
Jeku
Moderator
14
Years of Service
User Offline
Joined: 4th Jul 2003
Location: Vancouver, British Columbia, Canada
Posted: 18th May 2018 16:35 Edited at: 18th May 2018 16:37
Not sure if you're still looking for help with this... this is what I've done before. I'll have my servers set to UTC, and my site is going by EST. You can choose any two that you want, but UTC is good because it doesn't change with daylight savings.



I put these defines somewhere that all the code on my site can access, and whenever I want the EST timestamp, I can always use TIMESTAMP_ET .... when I store dates and timestamps in the database, I'll always store it as the server's current timestamp (UTC) which is just time()

Not sure if that can help in any way. Basically you need an anchor timezone like UTC and then change the hours to whatever timezone you want the user's to see (or just your own local timezone for convenience). Figure out the day that your local timezone changes and set the hour change there. There's probably better time management handling in PHP out there but this has worked great.
Senior Software Engineer - RotoGrinders

Login to post a reply

Server time is: 2018-06-19 13:22:10
Your offset time is: 2018-06-19 13:22:10