Hi,
Our application saves timestamp with TZ fields with the region not the exact time offset.
For example
01.04.2021 12:00:00,0000 Europe/Berlin
if I display this record in TOAD for Oracle (Version 14.0.75...) it ist shown as
01.04.2021 12:00:00,0000 +02:00
This cannot be changed.
Now when I update one other field in the same record also the timestamp with TZ value is update and afterwards the record has no longer the region information (Europe/Berlin) in it but the time offset (+02:00). This might not be a problem for values in the next few years, but we save values for the year 2040+. In this case it already happened, that countries decided to stop using the daylight saving switch. Now the timestamp will be no longer correct!
Could you please check and advice?
Kind regards!
Toad uses your PC's default date format + TZH:TZM for timestamps.
It's not configurable now to use TZR, but I'm looking to see if there is a way that we can add that.
I wasn't able to reproduce the problem of one field being updated when I update another field. For me, only the field values that I edited got changed. You should be able to see what Toad is doing here with Spool SQL.
Thank you for trying to add the TZR option!
Unfortunately I couldn't reproduce this problem my self (it was reported by a colleague), but face now another problem with timestamp with TZ
I have an Oracle 19c database and also Oracle 19c client installed on a Windows 10 pro PC, both timezone files are the same version (32).
If I try to modify field "TEXTFIELD " (varchar2)in a record in a table with field "STARTDATE " (TS with TZ )
I get an ORA-01805 error. If I modify the same record in Toad with an update statement everything is fine.
UPDATE TS_TEST
SET TEXTFIELD = 'test',
STARTDATE = TO_TIMESTAMP_TZ('01/01/2020 00:00:00.000000 EUROPE/BERLIN','DD/MM/YYYY HH24:MI:SS.FF TZR')
WHERE PK = 1
;
Also I'm not able to enter a region style formatted timestamp in schema browser...
In the SB you'll have to follow whatever format is displayed there, since it isn't using TO_TIMESTAMP
OK, that's OK for now, if you'll make it possible to set the TZR option...
How about the ORA-01805? Any ideas?
Maybe I'm misunderstanding what you are trying to do in SB that's causing the ORA-01805. I thought you were typing in a different format than what is shown there.
Are you getting the error even when using the same format as what's there?
Sorry for confusing: I mixed up two problems here!
The ORA-01805 happens when I'm changing any field in a record within the table with the TSTZ field.
The format problem hit me during trying to find out if I can change the TSTZ field itself. So my primary problem is, that I can't change any value in a record and leaving the TSTZ field untouched.
I see. I don't know why that's happening.
There's not a trigger on the table that's touching the TSTZ column, is there?
No, it isn't
Beside the update statement I've shown before works fine...
Even without the change of the startdate
Try turning on Spool SQL, that may give a clue.
Unfortunately not:
This is the output...
Sorry missed the top lines.
-- Session: JURAUSCH@DB
-- Timestamp: 18:30:52.210
Savepoint LOCK_
-- Session: JURAUSCH@DB
-- Timestamp: 18:30:52.212
Savepoint LOCK_
-- Session: JURAUSCH@DB
-- Timestamp: 18:30:52.212
SELECT "PK" AS "_1", "TEXTFIELD" AS "_2", "STARTDATE" AS "_3"
WHERE
"ROWID" = :"Old_ROWID"
FOR UPDATE NOWAIT
:"Old_ROWID"(VARCHAR[18],IN)='AAAT8hAAFAAABW7AAA'
-- Session: JURAUSCH@DB
-- Timestamp: 18:31:03.086
Rollback to savepoint LOCK_
-- Session: JURAUSCH@DB
-- Timestamp: 18:31:03.087
Rollback to savepoint LOCK_
I'm having the same issue as Juergen (so does all my colleagues as far as I know), we are using Toad version 16.2.98.1741 (but this is not a new issue). As soon as you try to edit anything in Schema browser you get "ORA-01805: possible error in date/time operation" if one of the columns is a timestamp despite not touching that column.
NLS settings:
| NLS_LANGUAGE |
AMERICAN |
| NLS_TERRITORY |
AMERICA |
| NLS_ISO_CURRENCY |
AMERICA |
| NLS_DATE_FORMAT |
DD-MON-RR |
| NLS_DATE_LANGUAGE |
AMERICAN |
| NLS_TIME_FORMAT |
HH.MI.SSXFF AM |
| NLS_TIMESTAMP_FORMAT |
DD-MON-RR HH.MI.SSXFF AM |
| NLS_TIME_TZ_FORMAT |
HH.MI.SSXFF AM TZR |
| NLS_TIMESTAMP_TZ_FORMAT |
DD-MON-RR HH.MI.SSXFF AM TZR |
Timestamp example as displayed in SB:2022/04/29 09:50:32,838628 +02:00 (column type is TIMESTAMP(6) WITH TIME ZONE)
I'm trying to reproduce this but for me it works fine.
What are your settings in Toad for dates
(Options -> Data Grids -> Data)
and thousand/decimal
(Options -> General)
and also windows regional short date, short time settings settings?
NLS setting is not relevant here.
It might solve the problem if you make Toad settings match window settings here
My settings:
Windows settings:
I tried changing from / to - in Toad but it did not seem to solve the problem
I've tested it again with our settings, John. Unfortunately it's still the same.
Also the TZR option does still not work in SB 
Looks like it happens because the timezone in your DB is as text and not numeric.
I can reproduce the problem if I follow the example here:
https://forums.devart.com/viewtopic.php?t=36016
I think if you convert the timezone to numeric (for example +01:00 instead of 'Europe/Berlin'), then it will work. Mine is numeric and everything works fine.
More Info:
I have found that I can convert it like this:
If I have a table called TEST_TIME, with column TSZ being timestamp with time zone,
and I can see text timezone info like this:
select tsz, TO_CHAR(TSZ, 'TZR')
from test_time;
Then I can change it like this:
ALTER SESSION SET TIME_ZONE='+01:00';
update test_time
set tsz = tsz
where TO_CHAR(TSZ, 'TZR') = 'EUROPE/BERLIN';
Commit;
Now the SB works as expected. I have an open request with our Oracle access 3rd party component (the one that we use to write queries, etc) to fix this.
You may want to make sure with others in your organization that changing the timestamp format from text to numeric won't cause some other problem.
Hi John,
I think that is not an option. If it would be only for myself then maybe it would, but I don't think I can ask my coworkers to run this alter each time they connect to the database.
And I can't set the database setting to +01:00 as it is not correct for over half a year. (+02:00 for CEST)
And finally my expectation is that I can see and enter the correct timestamp with time zone in SB. S
o there should be shown EUROPE/BERLIN and not +01:00...
Hi Juergen,
I know the workaround won't work for everyone. Just putting the info out there.
Hopefully we can get it fixed soon.
-John
I have found that if you:
- Close Toad
- Restart and connect without using an Oracle client
Then you can edit the other columns in the table. Just don't edit the timestamp column in this mode, or timezone will change (this bug is logged and we will fix it when we can).
