Re: Time (and time zones) in Oracle 8i

Alex,

your "help" misses the point of the original posters questions. the NEW_TIME function doesn't really address the problem, which is really that the Oracle DATE datatype does not carry with it "timezone" information, and that this must be handled separately.

the expression: NEW_TIME( date_expr, 'GMT','CST') is equivalent to: date_expr - 6/24
and
the expression: NEW_TIME( date_expr, 'GMT','CDT') is equivalent to: date_expr - 5/24

but there is no function (available in Oracle) to tell you what timezone a DATE value is expressed in. that is, given a value from column ORDER_DATE, how do you know whether that value is expressed in GMT or EST or PST ? and how do you know whether the DATE value is within a period when a daylight savings time offset is in effect ?

Oracle does not provide support for a timezone like CST5CDT, which it really can't, without carrying timezone information as a separate expression.

Your post of the contents of the SQL References is, as you pointed out, of little help.

"Alex Filonov" <afilonov_at_pro-ns.net> wrote in message news:94g6gc$i8p$1_at_nnrp1.deja.com...
> Here is little help for you.
> This is extract from Oracle documentarion, SQL language reference:
>
>
> NEW_TIME
>
> Syntax
> NEW_TIME(d, z1, z2)
>
> Purpose
> Returns the date and time in time zone z2 when date and time in time
> zone z1 are d. The arguments
> z1 and z2 can be any of these text strings:
>
> AST
> ADT
> Atlantic Standard or Daylight Time
> BST
> BDT
> Bering Standard or Daylight Time
> CST
> CDT
> Central Standard or Daylight Time
> EST
> EDT
> Eastern Standard or Daylight Time
> GMT
> Greenwich Mean Time
> HST
> HDT
> Alaska-Hawaii Standard Time or Daylight
> Time.
> MST
> MDT
> Mountain Standard or Daylight Time
> NST
> Newfoundland Standard Time
> PST
> PDT
> Pacific Standard or Daylight Time
> YST
> YDT
> Yukon Standard or Daylight Time
>
>
>
> In article <93amf3$jr$1_at_slb3.atl.mindspring.net>,
> "gnn_gnn" <gnn_gnn_at_hotmail.com> wrote:
> > 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
> >
> >
>
>
> Sent via Deja.com
> http://www.deja.com/
>
Received on Sun Jan 21 2001 - 23:06:28 CST