|Watch out for the Time Zone Gotcha.|
it burned me, now I need to recode everything
| 6:42 am on Nov 8, 2009 (gmt 0)|
What's that they say about hindsight?
If you build web applications like I do, please don't learn this the hard way, as I'm about to.
I have a form where users can view things that have occurred within a particular timespan. The UI is pretty simple - it's two calendar datepickers. Just dates, no times.
Because I'm a frugal programmer, I haven't been saving occurrences individually as separate rows. I didn't care to keep such granular details; instead I've been keeping stats using a statement like this:
[quote]UPDATE stats SET counter=counter+1 WHERE date = '2009-11-08'[/quote]
Thus I keep a running total of occurrences on November 8. If anyone asks how many occurences happened, I can say there were X. The next day, I start a new counter for November 9.
But here's the problem. What if the user is in Japan? Their idea of what constitutes "November 8" is different from mine. Something that, for me, was on November 8 might be November 7 for my Japanese user. Mess that up, and a whole bunch of other things go wrong.
A user in Austin might request stats with the same criteria as our user in Tokyo, but they should see different data. Because some things that happened on Tuesday in Japan were on Wednesday in Texas.
Consequently, that counter I'm using to tally ocurrences on November 8 isn't sufficient. It doesn't matter that no one can request data more granular than a range of days; I need to keep that granular data, because the definition of "a day" differs from user to user.
I did it wrongly. The problem cascades all over my app, where my record of the timing of things won't match up with what actually happened.
So, note to self:
1) I should store all my date&time data as UNIX timestamps ('1238373794'), instead of formatted datetime strings ('2009-11-08 13:32:09').
2) I should build timezone conversion into the presentation layer, right from the start
3) Before hard-coding shortcuts in data collection, I should pause to consider whether the need for granularity is affected by dates and times.
Think about it now, before you start.
| 2:41 pm on Nov 8, 2009 (gmt 0)|
Using a server date, or server timestamp is pretty much the same thing. The timestamp is essentially just another representation of the date (down to the second of course).
We store timestamps when we need data down to the second. Otherwise we simply store dates in the format of YYYYMMDD so it can be sorted, etc.
You are right though, if you need to offer local reporting data, you need to store local reporting data.
| 6:33 pm on Nov 8, 2009 (gmt 0)|
and don't forget that daylight saving time changes on different dates in different countries.
| 7:03 pm on Nov 8, 2009 (gmt 0)|
Store all data using the UTC time zone all year round for all data pertaining to all locations.
You can then display it as a UTC date/time or as a local date/time for the user, at will.
| 8:25 pm on Nov 8, 2009 (gmt 0)|
Unix timestamp will still be relevant to the server (won't it?)
I get identical date/times . . .
I am guessing the user's time zone is extremely relevant to the site function, in which case . . . ohhhh nooooo . . . I'm sure you considered "all times are server time, -8:00 GMT" or similar.
| 8:58 pm on Nov 8, 2009 (gmt 0)|
I discovered that all my times in the database are recorded in PST (Pacific Standard) - that's the time zone where my server is located. PHP's date() function is filling those in. For the sake of clarity, I think I shall store my dates in GMT/UTC after all this mess is recoded.
In addition to all the other complicated logic involved in building a web app, I hadn't expected it to become even more complex with all this time-difference stuff. Alas, no one said it was going to be easy. (sigh)
| 9:04 pm on Nov 8, 2009 (gmt 0)|
I agree with @g1smd, using UTC is a best practice.