I'm trying to figure out how to correctly use Java calendars, timezones, etc. to calculate the correct information to put in my database.
Here is my situation:
1. A user enters a due date consisting of a Month, Day & Year via a web interface
2. To submit the due date to the database I need to convert the Month, Day & Year into a timestamp.
3. To accomplish this I was planning to do the following, but I'm not sure it's going to work between timezone changes, the confusing nature of java calendars & timezones, daylight saving issues and postgres timestamp conversion issues & it's own daylight saving issues:
A: create a Java Calendar object (This is auto-initialized at my servers timezone US/Eastern and the current date/time the user submitted the due date). Ex. The time calendar was created is: Jan 1, 2007 4pm EST
B. Set the timezone of the calendar to match the user's timezone Ex. use cal.setTimeZone(TimeZone.getTimeZone("US/Pacific")); thus making the calendar set to Jan 1, 2007 1pm PDT.
C. Set the month, day and year of the calendar object to match the due date submitted by the user. Ex. cal.set("2008", "4", "20") thus making the calendar set to March 20, 2008 1pm PDT.
D. Then I need to change the due time to be at 2am of the next day (for expiration purposes) Ex. add a day: cal.roll(Calendar.DATE, true) then change the time to 2am: set(Calendar.HOUR_OF_DAY, 2) thus making the calendar March 21, 2008 2am PDT.
E. Store the data in the database in a timestamp with timezone field (postgres DB). The time is stored relative to UTC, but the timezone is (US/Eastern) which fluctuates between GMT-4 and GMT-5 depending on daylight saving.
F. Then I need a way to do a check on the database every couple of hours to determine if a due date is overdue. Somehow I need to pull in the timestamp with timezone data back into the database and check it against the current time. I believe I can do this by getting the text value from the database (stored as follows: 2007-01-05 13:48:49.232279-05) and then convert it into a calendar object set to US/Eastern timezone and also create a new calendar object with the current date/time and also in the US/Eastern timezone and mark the initial submission as overdue if I compare the calendars using the "after" function.
This all seems rather difficult and possible hiding some error in conversion I am not picking up.
I've noticed that if I test some of these ideas using output to a JSP page, changing the timezone on the calendar doesn't seem to change the time or timezone when displaying <%= cal.getTime() %>, but from what I've read tis may be more of a display issue than a calendar issue. I'm not clear at all what I need to do to display things correctly either.
I had also thought of an alternate idea of storing the dates in a text field in the database to help cut down on some of the timezone/daylight saving issues, but don't know which is better. This idea came from the fact that if a due date was set for over a year from now, the database wouldn't correctly convert future daylight savings, at least I'm pretty sure it won't. Anyway....
If anyone can offer suggestions on if this works/how it can be improved/how to display, etc. I would be most greatful. Thanks,