Storing future date/time values

MarkR

Hi all, I'm wondering how you usually store future scheduled date/time values.

The problem:

Databases store DATETIME values as a date/time excluding time zone (And even if they included it it wouldn't help much)
Time zones in many regions of the world vary due to a) Daylight saving time and b) Political intervention. The former is predictable but a bit complicated, the latter is wholly unpredictable.

So, when you store a future date/time in the database, it is necessary, if you plan to know exactly when it is in advance, to know what time zone it's in. If that is the local time zone to a particular place, then it may not be possible to determine that with complete certainty.

For example, say I schedule an event for close to the UK daylight savings time border, a few years hence. Sometime in the interim, some unexpected event happens which causes polical change, then daylight savings time is either abolished altogether, or the rules are changed.

The upshot of this is, you can't (accurately) know exactly when something scheduled in a local time zone some way in the future is really going to be.

Moreover there is no PHP function which gives you a timestamp from a date/time in the local time zone AT THAT POINT IN TIME (mktime uses the current time zone, even if the date/time you're referring to is the other side of daylight saving) - TO THE BEST OF MY KNOWLEDGE: If you know differently, PLEASE REPLY!

This means that I would normally store FUTURE date/time values in local time to the locality, as this ensures that they are always going to be right (even if we don't know when they are).

This is in contrast to PAST date/time values (e.g. when something was created) - which I always store in GMT because then I know exactly WHEN something happened, timezone notwithstanding.

This is of course a pain in the arse as I have to do several conversions to/from timestamps, some of which may work out as being wrong.

There isn't a nice solution to this. What do you do?

Mark

vaaaska

I don't have any problems storing date/time stuff. I'm lazy...and I want to be able to read it straight from the table. Use a time offset to always adjust your date/time and always use the same function to access it.

It is a little messy grabbing your date/time and converting it for other purposes, but once you have a set of tools you are ready to go.

So, I don't care what time zone people are in...I let them use a time offset to manually adjust it to whatever they want the time to reflect.

But I will say, I don't use a time offset for caching pages...I use the standard server time so it can function independently of ever needing to ask the database what the time offset should be.