Date displaying and storing in MySQL while in wrong timezone

Aurelia

I've only used php to display the current date but I can see that I'm not in the same timezone as the server is. Its 5 hours ahead and I can't get it to show the date as Eastern Time.

I'm developing a site for a non-profit slopitch league so they can submit score sheets and track their season. 2 teams must submit a score sheet for their team within 72 hours of having played the game. Then they are not allowed to enter the score sheet without the administrator's permission.

So I need help with using dates in 4 areas:

1) Displaying the current date in Eastern rather than the server timezone.

2) Storing the date and time when a game sheet is submitted in my timezone.

3) Testing the current time a user is trying to submit a game sheet against the 72 hour time limit and refusing to submit it if they have gone over the 72 hours.

4) Locking a game (basically a CHAR(1) that is set locked and can be unlocked by an admin) when the 72 hours is up. This ties into #3. But I want to give the Administrator a message when they log on that the 72 hours is up for a game is up and not all submissions have been entered. So logic here is failing me. I can't do any server side scripts so at some point a test has to be made on the games to see which ones are past their 72 hour limit and lock them down. If each team has submitted a score sheet I can lock the game down then, that's the easy part. I'm just trying to find the best way to lock it when an outstanding score hasn't been submitted and give the admin warning so they can contact the team and get the low down on why it wasn't submitted.

But all this hinges on date() which gives me the server's date and time.

Does anyone have any suggestions? The first thing I need to do is figure out how to get the date accurate. Then how to test 2 date/times for a 72 hour limit.

ElectricRain

Hum.. with the date thing, would gmdate() do it? would that be the right time zone for you? Otherwise can you perhaps compensate:

$three_months_ago = gmdate("Y-m-d H:i:s", mktime(gmdate("H"), gmdate("i"), gmdate("s"), gmdate("m")-3, gmdate("d"), gmdate("Y")));

This is something I used to find what the date was 3 months ago from today, but you could probly manipulate it to compensate for the time difference between your server and yourself by adding/ subtracting the appropriate number of hours instead of months?

MarkR

The function time() gives you the time in seconds since 1969. This is independent of time zone, hence won't change wherever you or your server is.

Things like date(), gets the date based on the current time zone. This can be set by the TZ environment variable. I recommend that you do this.

In the PHP docs there is a list of time zones. Typically it's something like "America/New York". So if you do putenv("TZ=America/New York"); at the top of your script, this will affect the behaviour of date() etc.

You should be entirely clear about what time zone all your date columns in the database are. Calling current_timestamp() in MySQL will get the time in the MySQL server's current time zone - which is probably not what you want. Get the time in PHP isntead and insert that value.

It may be advantageous to store all dates / times in GMT and convert them as necessary; however, this doesn't work well with future event dates/time, because people generally schedule events in their local time zone, which may use daylight saving time.

If you store everything in GMT, you have the advantage that you know exactly when something happened (even if it happened during the "witching hour", because GMT, or more properly, UTC, does not have daylight saving time). But the disadvantage is, it's difficult to calculate when an event should be, because it may be in a different time zone from the current due to DST.

So normally, I'd store everything in GMT, except for the date/time of future events, which should be stored in the local time zone of the event instead, because that's what people expect, and will use.

Mark

Aurelia

Thanks for the help guys! I got the timezone worked out now 🙂 And I'm told that they will make the cutoff date a week instead of 72 hours so that helps. I am still working on how they want the underlying code to work but this is a great help! Thanks again!