# Timestamps with local timezone not working in 10.5 GA

**URL:** <https://forums.toadworld.com/t/timestamps-with-local-timezone-not-working-in-10-5-ga/9932>\
**Category:** Toad for Oracle & DevOps Toolkit\
**Created:** [October 14, 2010, 9:32am UTC](https://forums.toadworld.com/t/timestamps-with-local-timezone-not-working-in-10-5-ga/9932 "2010-10-14T09:32:14Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\
**Post date:** [October 14, 2010, 9:32am UTC](https://forums.toadworld.com/t/timestamps-with-local-timezone-not-working-in-10-5-ga/9932/1 "2010-10-14T09:32:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![GTDG](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/gtdg/32/140_2.png) [@GTDG](https://forums.toadworld.com/u/GTDG)\
**Post date:** [October 14, 2010, 9:38am UTC](https://forums.toadworld.com/t/timestamps-with-local-timezone-not-working-in-10-5-ga/9932/2 "2010-10-14T09:38:34Z")

</div>

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

Greg