DB in different time zone

[flyingpylon]

I have a database and web server that are located in a different time zone than the one I'm in. I'm building a site that is focused on local topics, so I'd like to make sure I always display the correct local time on items such as comments, document release dates, etc.

My question is this: At what tier should I do my time zone shift?

- At the business/application tier as the data is entered so that it is stored as "my time" in the database?

- At the data tier as the data is requested (via stored procedures, etc.)?

- At the business/application tier as the data is presented to the user?

For whatever reason, I'm having a hard time deciding which option is best as I can see advantages/disadvantages to all three options.

Thanks for any advice.

[danfran]

My 2¢ is to always record interactions at ACTUAL server time, then present data based on client browser info. This is, of course, unless you plan to expand or change hosting providers. In THAT case, you might record both local and GMT (Greenwich Mean Time), just to be safe. Can you say for certain that all your site visitors come from the same time zone? (Like a school)

Depending on how many time zones your visitors come from, you could even put another field in the DB indicating the shift from Greenwich. (-5, -6, -7, etc..)

-->
It sounds like most of your users are only going to be "from" one time zone, giving you this "either / or" sorta situation, psychologically. As for scalability, definitely do NOT adopt this by hard-coding a +1 or -1 hour into your ASP unless you want to cut a corner and hot-fix it.

Tell us more about the circumstances of your app. I wouldn't feel right telling you one way or the other without knowing.

Dan

[swdev]

Personally, I would always store the date/time in UTC (was GMT) format.
Only when I dsplay it back to the user, do I convert it to their local time.

[flyingpylon]

Thanks for the replies.

Based on a little more research, I think I will be inserting the local database time. It's pretty unlikely that I will be switching hosts, and even if I did I suppose I could just update all of my dates by running some queries. I can understand the advantage of recording GMT, but I think it might be more work than it's worth for my particular situation.

What I will do is store a time offset value in the database, and reference that when determining which data should be pulled (e.g. by release date, etc.). On the application side I will store that same time offset value in an application variable and apply it when displaying datetime values. This seems to be the way some commercial applications do it.

As a fortunate coincidence, my host and I are both located in states that do not observe daylight savings time (at least not yet).