Re: Oracle Timezone question

On Thu, 29 Jan 2004 07:12:38 +1100, "Howard J. Rogers" <hjr_at_dizwell.com> wrote:

>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
>------------------------------------------------------------
>COL2
>------------------------------------------------------------
>COL3
>------------------------------------------------------------
>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
>----------------------------------------------------
>COL2
>----------------------------------------------------
>COL3
>----------------------------------------------------
>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
Howard and Daniel,

Thanks for your replies. That's pretty much where I figured I was at, but one never knows what it is one doesn't know. And, Howard, your example certainly clarified for me the parts of the docs that I wasn't quite understanding. Thanks for your patience. Received on Thu Jan 29 2004 - 08:53:27 CST