looking for Mr. GoodIdea

Thought I’d toss this out and see if anybody had any ideas as to a beautiful, surgical approach to the following challenge:

Our company has several legacy apps (some web based, some not) that are part of a large warehousing and logistics process. These apps have been happily sharing data between themselves in the PST zone for quite some time via web services, ftp, and triggers and sprocs using database synonyms.

Well, corporate has decided to relocate some of these apps to a server farm in another time zone. In performing some in depth testing and data-chasing, I’ve initially identified dozens of points in all of the apps where the datetime stamps used for reporting and for performance indicators will be based on different time zones. I’m sure there are dozens more that I probably missed.

It seems to me that the most surgical fix would be to somehow overload Oracle’s sysdate function to utilize a specified time zone, but corporate IT says they’ve researched it and can find no way of doing that. They DID discover an alternative function to sysdate that provides the timestamp based on a specified timezone, but that doesn’t address the hundreds of tables hither and yon that use sysdate as default values, the sprocs that use it, the adhoc reports, etc.

Anybody feeling any sudden sparks of genius that might make rectifying the issue at hand somewhat painless?

Thanks for any ideas!

as long as you know where the timezones are, determine an offset for each.

or pass dates as UTC. then UTC -> local when you save.

Are you using Oracle’s sysdate function or are you using ColdFusion’s Now() function to get the Date/Time? If you are using the Oracle function, then Oracle will always use the Date/Time of the server it is running on. If you are using the ColdFusion Now() function, this is where it can get interesting if you have app servers in different time zones. We use Tomcat as our app servers and in the JAVA_OPTS settings we specify “-Duser.timezone=GMT” to tell Tomcat/ColdFusion to use the Date/Time as if the server was in the GMT time zone. In your case just make sure you set the time zone to what makes sense in your distributed environment.

-Ed Andrade

Apologies if I am oversimplifying this too much, but why not leave the date the same on the servers that are going to move, even if they are in a different time zone? Is that not possible? Also, while not sure how you can overcome this on the db site with Oracle, you could change the Timezone on the datasource configuration of the cf server - especially easy if you are using Railo.