Java Database Connectivity (JDBC)

Hi,

I seem to have a difficult to solve problem, and I have a few ways to solve it, but would like an opinion.

My application will serve clients in multiple time zones. Each client will have their own database to store their site's data. We had originally decided that to keep things consistent across all of our servers, all datetime values in the database would be stored at GMT (the time all web servers will be tuned to). However, I have run into a problem that I don't believe is easy to solve.

Let's say I insert a record, and I store the add date in a column called add_date. Let's say this record was inserted on Monday, 8/14/06 11:30PM (which is GMT - 7 during DST for the Pacific Time Zone). In our database we would store this as Tuesday, 8/15/06 6:30AM. This looks great, and things were working. UNTIL, I had to run a report that said "show me all records that were inserted on Monday's".

I originally ran a query that said something like:
SELECT * FROM t_table WHERE DATEPART(Weekday, add_date) = 2

I expected to see data, but of course there was none. The add_date in GMT time is Tuesday, and the DATEPART value for the database would return 3, not 2.

So then I tried to be smart, and I changed my query to something like:
SELECT * FROM t_table WHERE DATEPART(Weekday, DATEADD(Hour, -7, add_date)) = 2

In the above listed SQL, I have added the DST offset to the calculation. But then I realized that this offset is only valid for half the year, and the other half of the year, the offset value should be -8. At first I thought I could just have a CASE block that handled the offset calculation, but then I quickly realized that the DST dates change every year, and this was not feasible, lest I end up with a dozen CASE statements per SQL query.

I have read others post very complex stored procedures and lookup tables to handle DST offsets. I have read responses to those posts that have said something like "the stored procedures killed my server performance across 1000000's of rows". I believe the latter would be the case for us. Not to mention the fact that no one wants to baby sit lookup tables with time zone data every year.

So it appears that storing dates as GMT values will be problematic for me. Am I wrong in assuming this?

Therefore, here are my questions:

1. Is there an easy way to solve this that I am missing? Am I right to assume that putting things as GMT in the database loses some precision?

2. Is the best way to solve this to dump the data in the database in the client's time zone only (in this case, Pacific Standard Time)? In this case, all time zone conversion is done in Java prior to entering into the database.

3. I can get around this somewhat by just tuning the JVM to PST for PST clients using the TimeZone.setDefault("") method. Has anyone done it this way before? If I do this approach, I would need a JVM instance for each time zone. I'm not sure that's a huge drawback, but it is slightly cumbersome.

All in all, what I thought would be a walk in the park to solve has now turned into one of the more complicated problems I have faced in my system. I don't think I ever gave time zones and DST considerations enough weight during design. My goal is to handle this in the way that is easiest to implement, with the least amount of risk.

Thanks for any advice you can give in advance!

Michael