Time (and time zones) in Oracle 8i

Hi:

I am having some trouble getting a clear understanding of time and time zones in Oracle (8i). There are functions to transform date-times from one time zone to another, etc., but if I want to load data with a date-time format string like 'YYYY-DD-MM HH24:MI:SS', there is no timezone information for the template string. It seems like all times entered are assumed to be in the Oracle server's time zone? What does this mean for enterprises with servers in different time zones? (Although I don't have this, I am curious). This is true for SQLLDR as well. There is no way to tell the timezone for the date-times.

With the above in mind, and given that Oracle is used in many global enterprise databases around the world, how does one have remote sites do something like log events, and have users perform queries that do date/time boundary searches correctly? For instance, if something happens at 3:00pm in California, and something happens at noon in DC the same day, a temporal query SHOULD find both of those events occured at the same time. But if the CA log just enters "3:00pm" and the DC log enters "12:00pm", it won't work (will it??). Even if Oracle keeps time like unix, seconds since Jan 1 <pic a year>, that IMPLIES a timezone, does it not?

It seems like it is up to the application(s) to all convert to some standard time zone (Zulu, EST, or whatever) and I would think this is exactly what one does NOT want. It means you count on all the distributed sites doing the conversion properly, etc. It would seem that each app should enter "xx:xx TZ" where TZ is a time zone, and let the server convert it to it's time, (or zulu, etc.)

Am I right or wrong? How is this type of situation normally handled? Where can I find out more? My searches have not come up with anything definitive.

Thanks,

     Mike Received on Sun Jan 07 2001 - 15:21:21 CST