Toad World® Forums

Timestamp with timezone in schema browser not correct

Toad for Oracle
#1

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!

#2

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.

#3

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...

#4

In the SB you'll have to follow whatever format is displayed there, since it isn't using TO_TIMESTAMP

#5

OK, that's OK for now, if you'll make it possible to set the TZR option...
How about the ORA-01805? Any ideas?

#6

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?

#7

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.

#8

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?

#9

No, it isn't
Beside the update statement I've shown before works fine...
Even without the change of the startdate

#10

Try turning on Spool SQL, that may give a clue.

#11

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_