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!