Re: Timestamp Problem

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Tue, 26 Mar 2002 16:59:54 +1100
Message-ID: <a7p2qq$4dk$1@lust.ihug.co.nz>

I'm no expert on Java (perish the thought), but what you describe happens to sound like the difference in 9i between the new 'timestamp with local time zone' and 'timestamp with time zone' data types. Both permit you to know that a record was entered at, say, 5pm Sydney time (in other words, 7am London time), but the former lets you know it by converting the timestamp for you into something that reads '7am', and the latter only lets you know it by displaying an invariant 5pm, but with a timezone indicator (such as "+10" or 'EDST') that lets you work it out if only you (or your app) does some calculating.

For example:

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE

+11:00

(which means I'm in the '11 hours ahead of GMT' timezone)

SQL> create table timetest (col1 timestamp with local time zone, col2 timestamp with time zone);

Table created.

(Note the two slightly different data types used for each column).

SQL> insert into timetest values (sysdate, sysdate);

1 row created.
SQL> commit;
Commit complete.

(The exact same time is now inserted into both).

SQL> select * from timetest;

COL1

COL2

26/MAR/02 04:48:37.000000 PM
26/MAR/02 04:48:37.000000 PM +11:00
(And both columns show the time as 4:48pm. However, the 'with time zone'
version also includes visible details of which timezone I'm in).

SQL> alter session set time_zone='America/New_York';

Session altered.

(Now imagine you're querying the record in New York).

SQL> select * from timetest;

COL1

COL2

26/MAR/02 12:48:37.000000 AM
26/MAR/02 04:48:37.000000 PM +11:00 You'll note that the second column hasn't changed. It shows exactly what the record would show if queried in Sydney. However, the inclusion of the "+11" means that, if you knew New York was 5 hours behind GMT, you could subtract 16 hours in total from the displayed clock time to arrive at the time it was in New York when I entered my record in Sydney. But you (and your app) have to do the conversion.

On the other hand, the 'with local time zone' version has done the conversion for you automatically, and you see 12:48am displayed without further effort on your part.

Now I don't know if you that's all a bunch of old hat to you, or whether it's got anything to do with Java at all, in which case apologies for wasting your time. But having got tripped up by accidentally missing out the one word 'local' myself, it's behaviour I've come to be frustrated about, and therefore wondered if it was of any relevance for you!

Regards
HJR

--
------------------------------------------
Resources for Oracle : www.hjrdba.com
============================

"Sagar" <vkrishn_at_us.ibm.com> wrote in message
news:ce13a44a.0203252052.472db901_at_posting.google.com...
> Hi All,
>
> I have a java.sql.Timestamp object for instance 2002-03-22
> 17:48:50.659. I get this value out when I use the regular JDK. Now I
> have an RMI server and print out this Timestamp object by using a
> toString() on it, and it prints the value just fine.
>
> Now, I pass this object to an applet (The applet calls the RMI server
> which is printing the right value if I do a System.out and retrieves
> the same object) and if I do a toString() on it with the same timezone
> setting it prints the right value too (Note: the Applet client and the
> RMI server are running on the same machine).
>
> Now if I change the timezone to CST, then the value that is printed by
> the RMI server is the same (2002-03-22 17:48:50.659), but the value
> that is printed on the applet if I do a toString on it is different
> (2002-03-22 19:48:50.659).
>
> I understand that the JRE (I'm using 1.3.1_02, but a lot of JRE's that
> I tried give the same problem) is trying the manipulate the Timestamp
> object into the local time, but how come the SDK does'nt do it? I
> guess that this is a problem with the JRE implementation, but I'm not
> able to think of any fair way by which I can get around this problem.
> Is there any way by which I can solve this?
>
> I thank you all in advance for any help that you might extend.
>
> Thanking you again,
> Sagar.

Received on Mon Mar 25 2002 - 23:59:54 CST