Dealing with dates

Question

So basically I am storing a bunch of calendar events in a mysql table. And I store the day of the week they are supposed to happen, as well as the time. Right now the time is stored as the number of seconds from midnight calculated in GMT. But when I have people log on and check the calendar and they are from a different timezone other than GMT I'm having problems with calculating which events are within the day in their timezone. Any suggestions?

Answer 1

Here's many functions that can help ya out.

Answer 2

You can convert the current date in their timezone to GMT before doing the comparison (not necessarily within MySQL, but you can probably use CONVERT_TZ for this).

As for finding their timezone, bullet-proof method is probably to ask user and save his choice.

Answer 3

I'd store the unix timestamp for the event and use the time functions in PHP to calculate the correct display. That makes the database data independent of the timezone you're actually in. The user still needs to provide the timezone he's in.

Answer 4

Use the database' datetime datatypes and use the database' date/time functions. PHPs functions are rather limited, and you should not simply treat time as an integer, if you're dealing with real dates. A good strategy is to store all datetimes in UTC, and then you can have an additional field for the location (Eg. local timezone offset), if that is relevant. Often though, it is the viewers timezone that is relevant, and not the one that provided the data (Confused yet?)

Answer 5

When you are dealing with timezones, you do not want to do offset calculations yourself, especially if you deal with units of time smaller than 1 day. One simple reason: Daylight-saving.

Both MySQL and PHP have a multitude of date/time functions for manipulating dates. They know about daylight-saving and other quirks of moving dates and times around. And they get it correct more often than you will if you try to hand-roll a solution. At the very least, use (or create) a date object that includes the timezone. The PHP functions can be a bit tricky and are not as well documented, unfortunately, but all the capability is there. You can still use unix timestamps as your 'canonical' format, but all code that works with it has to know or understand what timezone it is 'in'.

In a past job, I had to deal with a school time-table and converting that format (term, week, day, period...) to and from real dates. We built an object that did any necessary conversions on demand and cached results. It was an effort, but having the PHP date functions do all conversions and adjustments saved a lot of problems with timezones and daylight-saving.

Another caveat with calenders: you may have to distinguish between timezones of events and timezones of users. And when events' timezones stay fixed and when they move with users'. Failure to handle all the nuances of this is why the calendar in Exchange/Outlook still has problems with daylight saving changes. :-)