Toad World® Forums

Timestamps with local timezone not working in 10.5 GA


#1

I know, I know, I’m behind in my upgrades - sorry! This might already be
fixed in the 11 betas, however…

Apologies for the following length …

Summary:

  1. Why does F5 crash Toad?
  2. Why does Toad & QSR display Timestamp with local time zone
    incorrectly for different session time zones?

Evidence:

Toad 10.5.1.3.
Oracle 10.2.0.4 database running on 32 bit Suse Linux Enterprise Server.
Oracle 10.2.0.1 client. (From Oracle XE).

set lines 400
set trimspool ON

– Make sure we can see the data.
ALTER SESSION set NLS_TIMESTAMP_TZ_FORMAT=‘dd/mm/yyyy hh24:mi:ssxff
TZH:TZM’ ;
ALTER SESSION set NLS_TIMESTAMP_FORMAT=‘dd/mm/yyyy hh24:mi:ssxff’ ;

– Clean up.
DROP TABLE mytimetable;

– Create a table to store the timestamp info.
CREATE TABLE mytimetable (
ts TIMESTAMP,
tz TIMESTAMP WITH TIME ZONE,
ltz TIMESTAMP WITH LOCAL TIME ZONE);

– Make sure we are correctly timezoned.
ALTER SESSION set TIME_ZONE = ‘Europe/London’;

– Check.
SELECT sessiontimezone FROM dual;

– Insert some data.
INSERT INTO mytimetable VALUES(
current_timestamp, current_timestamp, current_timestamp);
COMMIT;

– Retrieve the data in London.
SELECT ltz FROM mytimetable;

– Alter the session’s time zone.
ALTER SESSION set TIME_ZONE=‘Australia/Brisbane’;

– Check.
SELECT sessiontimezone FROM dual;

– Retrieve the data in Brisbane.
SELECT ltz FROM mytimetable;

– Reset.
ALTER SESSION set TIME_ZONE = ‘Europe/London’;

Problem 1:

When I run each SQL statement above, using F9, it all works fine except
the date displayed for the Brisbane LTZ column is still showing as if we
were in London:

– Retrieve the data in London.
SELECT LTZ FROM mytimetable;
14/10/2010 15:09:16.674535
^^^^^^^^

– Retrieve the data in Brisbane.
SELECT ltz FROM mytimetable;
14/10/2010 15:09:16.674535
^^^^^^^^

As you can see, both display the same date, regardless of the session
time zone.

Problem 2:

Running as a script bombs out horribly:

SESSIONTIMEZONE


#2

Not fixed for 11 (yet). Not sure why the scripts are bombing out but I’ll check into it.

Greg