Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: NLS_DATE_FORMAT with GMT postfix

Re: NLS_DATE_FORMAT with GMT postfix

From: Chris O'Sullivan <itoys_at_tpg.com.au>
Date: Wed, 28 Nov 2001 01:52:50 +1000
Message-ID: <3c03a855@dnews.tpgi.com.au>


I would love a resolution of this issue. I am currently designing an application that must support multiple time zones.

First let me say that in the views xxx_TAB_COLUMNS there are data types TIME, TIMESTAMP and INTERVAL. I cannot find anything on these in the 8.1.5 SQL doco. Assuming these types are NOT available and we are stuck with the regular Oracle DATE then read on.

Let me point out that contrary to some DBA exams, the Oracle server does NOT store date-times as Julian Dates but allows you to convert DATEs to Julian DAYs [accurate only for A.D. dates]. Since a Julian DAY is effectively a Julian Date with the time truncated this is not much help for you problem. Oracle actually stores the date-time in a DATE type [code=12] as 7 bytes by storing the Gregorian/Julian calendar elements of Century, Year, Month, Day, Hour, Minute and Second. Unlike UNIX etc. this date-time is stored "as entered" and is NOT adjusted to UTC. Thus, here in Brisbane, Australia where we are UTC+10:00 if I store a date of "Nov 28, 2001 12:35:42", Oracle stores Century&Year=2001 (sort of), Month=11, Day=28, Hour=12[+1], Minute=35[+1], Second=42[+1]. What this means is that the date-time is stored as entered, that is in local time.

What I am proposing as a solution [again assuming that the TIME & TIMESTAMP types are not available] is as follows.
1) Adjust all date-times to UTC by subtracting the time difference of the local date-time's time zone.
2. Store this adjusted date-time along with the adjustment factor. In Oracle 9 [assuming Oracle have improved their support of objects] this would be a good candidate for a User defined Type say a DATE_TIME type. For now, assuming objects are not worth the trouble, I would store it as two columns, a DATE and a NUMBER.

As an example, let's assume the table is EMP and the column HIREDATE is to be used to store our adjusted date-time value. Let's add an extra column and call it HIREDATE_ADJ of type NUMBER. Also assuming we are storing adjustments in days (and fractions of a day) we could insert a new hire date in Brisbane local time (UTC+10:00) as follows:

Insert into EMP (...,HIREDATE, HIREDATE_ADJ) values (...,sysdate-10 / 24,10 / 24)

Now to retrieve a date in the original local time (i.e. Brisbane time) we could use:

Select ..., HIREDATE + HIREDATE_ADJ as "HIREDATE" from EMP

If all dates are adjusted to UTC then normal date arithmetic applies without needing to add in the adjustment value. This is primarily for presentation.

An important point to note is that end user query tools are going to be very "confused". Every product that I know of expects Oracle dates in local time and not UTC. You could provide your users with view that do the above adjustments but unless you create function indexes to go with these views, you might cop a lot of complaints about performance!!

I would appreciate any comments from anyone out there [positive and negative]. What do you think? Any other ideas?

"Lee Crampton" <lee_at_avbrief.com> wrote in message news:o85L7.1174$L.252055_at_news6-win.server.ntlworld.com...
> Hi All
> try as I might I can't seem to find the picture format to allow dates with
a
> GMT postfix.
> Say I have a date represented as "2001-11-22 09:01:02 GMT".
> I want to set the NLS_DATE_FORMAT and/or use on the fly formats with
> to_char.
> My picture format is currently "YYYY-MM-DD HH24:MI:SS" but I want to allow
> for the GMT bit.
> Any clues gratefully received.
> Thanks
> Lee
>
>
Received on Tue Nov 27 2001 - 09:52:50 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US