Re: Java (new Date()).getTime() - java.util.Timestamp("select timestamp from dual").getTime() ???

On May 24, 3:59 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On May 22, 4:34 pm, Joerg Weule <jwe..._at_arcor.de> wrote:
>
>
>
> > Hello,
>
> > I tried to retrieve a timestamp from my database independent from the
> > local TZ environment variable. The ResultSet.getTimestamp(...).getTime()
> > method should return the number of seconds after "1.1.1970 GMT" like
> > (new Date()).getTime() .
>
> > Is there any timestamp (current, local, sys, ...) to get the correct time?
>
> > Thanks in advance
>
> > Jörg
>
> > --------------------------------------------------------
> > export TZ=...
>
> > run :
>
> > PreparedStatement s = c.prepareStatement(
> > "SELECT ?????????? FROM dual");
> > ResultSet rs = s.executeQuery();
> > rs.next();
> > t3 = rs.getTimestamp(1,java.util.Calendar.getInstance());
> > System.out.println(
> > "Timestamp-Error:"
> > +rs.getTimestamp(1).getTime()-(new Date().getTime());
> > rs.close() ;
> > s.close() ;
>
> I do not code java so I have no idea if you can use this the way your
> post shows but the Oracle function current_timestamp returns a
> timestamp value equal to the current database time.
>
> HTH -- Mark D Powell --

That's current time in session time zone. Database time is returned by systimestamp. And to get it in UTC, you can select sys_extract_utc(systimestamp) from dual:

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

Session altered.

SQL> select current_timestamp, systimestamp, sys_extract_utc(systimestamp) from
dual;

CURRENT_TIMESTAMP

24.05.07 09:19:38,687000 AMERICA/NEW_YORK
24.05.07 17:19:38,687000 +04:00
24.05.07 13:19:38,687000

Note that sys_extract_utc() returns TIMESTAMP, not TIMESTAMP WITH TIME ZONE. I suppose that you can then
ResultSet.getTimestamp(...).getTime() from this and it should match Date.getTime(). Actually, current_timestamp and systimestamp should be handled correctly, too, as they contain time zone information and it can be used to convert them to UTC. I think you can use ResultSet.getTimestamp(int columnIndex) for that, no need to supply Calendar instance.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu May 24 2007 - 08:37:31 CDT