| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Timezone question
TIMESTAMP is absolute. It's very like the date field, only it goes to 9
decimal places of second-precision (I think that's a nanosecond, by it's a
long time since I did physics. And my maths was never that good in the first
place). Type in 9 o'clock, and when queried the record will display 9
o'clock -and it will always display that, and there's no indication
whatsoever *whose* 9 o'clock that is (Mountain Time Zone, GMT, GMT+10 or
whatever)... so there's no way to compute with such a time, or work out what
time it was for someone else when you entered that record.
TIMESTAMP WITH TIMEZONE is absolute, but clever. Enter 9 o'clock MTS, and when you query the record, it will display "9:00 +MTS" (similarly, if I enter a record into your database it will display 9:00 +10, because I'm in Sydney). So it still says 9 o'clock, but there's a little piece of extra information there which would allow you to code something to 'normalise' the time. If you query both records, and ask yourself, 'what time was it on my wall clock when Howard entered his record', the record itself won't tell you (it says 9 o'clock still), but you could work it out: MTS is (say, because I don't know) GMT-6, Howard was in GMT+10, therefore although the record shows 9 o'clock is was actually (9-16hours=) 5pm of the previous day in the Mountain Time Zone when Howard entered his record.
TIMESTAMP WITH LOCAL TIMESZONE is relative. You enter 9 o'clock MTS, I enter 9 o'clock, but when *you* query the records you will see one unchanged (because you entered it) and one showing 5pm of the previous day, which is the time it was for you when I entered my record in Sydney. The server computes automatically what you would have to compute yourself with the TIMESTAMP WITH TIMEZONE. As a simple example:
SQL> create table timetest (
2 col1 timestamp,
3 col2 timestamp with time zone,
4 col3 timestamp with local time zone);
Table created.
SQL> insert into timetest values (sysdate,sysdate,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from timetest;
COL1
29/JAN/04 06:58:23.000000 AM 29/JAN/04 06:58:23.000000 AM +11:00 29/JAN/04 06:58:23.000000 AM
Here's column 1 telling you nothing other than someone, somewhere gets out of bed too early. But you don't know where. Column 2 tells you that it is someone near the Internation Date Line (but on the left-hand side of it) that gets out of bed too early. Column 3 is a bit of a disappointment, because it looks no different from Column 1.
But now come on a quick trip around the world with me:
SQL> alter session set time_zone='America/New_York'; Session altered.
My session now believes itself to be sitting on the East Coast. Now what do we see:
SQL> select * from timetest;
COL1
29/JAN/04 06:58:23.000000 AM 29/JAN/04 06:58:23.000000 AM +11:00 28/JAN/04 02:58:23.000000 PM
Column 1 hasn't changed at all, and still tells you nothing. Column 2 hasn't changed at all, but the "+11" there tells you all the information you need to work things out. And column 3 has worked it out for you. Whoever entered the record, and whatever time it was for them, on the clock in Grand Central Station is said it was 2:58PM on the 28th when that record was entered. It is showing you the time it was in New York when the data entry took place, even though the data entry itself may have taken place some place completely different. No mental gymnastics required, therefore, and -unless I'm very much mistaken- it's the third data type you'd want to implement in your database.
All of which is sadly irrelevant if, as you suspect, the application really stores these things in varchar2 columns! The only thing I can think of doing in that case would be to create a before insert and update trigger which does roughly what the TIMESTAMP WITH LOCAL TIMEZONE does automatically. Your user types in one time, the trigger fires, lops off three hours (or whatever) and stores the resulting time in the field. I dislike that as a viable option, though, because you can bet that as soon as it is implemented users will complain that 'that's not what I typed in'. It also presupposes you are permitted by the app's vendor to muck around with it like that.
HTH,
Regards
HJR
-- ------------------------------------ Oracle insights at www.dizwell.com ------------------------------------ "Ed Stevens" <nospam_at_noway.nohow> wrote in message news:imkf10lin6o4d14c1g0k7psq7237hk3ak6_at_4ax.com...Received on Wed Jan 28 2004 - 14:12:38 CST
> Platform: Oracle 9.2 on Solaris 8.
>
> We have a packaged app that running in two U.S. locations, both in
> the Central time zone. The db is located in Mountain time zone. The
> only date/time datatype in the application's schema is DATE. It is
> also rumored that a lot of the application date/time data is actually
> stored in VARCHAR2 or NUMBER columns. Users are complaining about
> having to mentally adjust when dealing with times. Managent (as
> always!) is looking for a magic parm I can set to make the problem go
> away.
>
> I've read all of the relevant docs on tahiti, as well as several
> promising threads from the ng archives. Quite frankly, I'm having
> trouble getting my head around it all, but it appears that there is no
> way out short of going into the app itself. Resetting the time or
> timezone in Solaris itself is not an option.
>
> Can anyone give me a reasonably concise description of what our
> options are? Meanwhile, I'm continuing to re-re-read all the hits I
> get from searching tahiti for 'timezone'.
>
> Thanks.
 |
 |