Which format is used for TIMESTAMP WITH TIME ZONE in Grids

Toad for Oracle - Beta
1

How are columns of type TIMESTAMP WITH TIME ZONE converted into a string for display in a grid?

For example, the following SELECT returns the value in an “unfavorable” time zone for the first column.

SELECT CURRENT_TIMESTAMP, TO_CHAR(CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss.ff6 tzh:tzm (tzr)') FROM DUAL;
CURRENT_TIMESTAMP TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DDHH24:MI:SS.FF6TZH:TZM(TZR)')
2025-05-08 12:44:59,839715 +01:00 2025-05-08 13:44:59.839715 +02:00 (Europe/Berlin)

'Europe/Berlin' is my session time zone.

2

The format is fixed (but we have an enhancement request with out Oracle access provider to change that).

I'd have to do some digging to answer the question of how they determine which time zone you are in. I'm sure it depends on if you are using an Oracle client or not. Are you? If so, do you get a different result by not using a client (and vice versa)?

I do see a call to the windows API function GetTimeZoneInformation in there.

3

I'm using an Oracle Client, but I get the same result if I don't.

4

I tried a bit more.
The “problem” only occurs when I talk to an ancient 10.2 server.
With a 12.1 and a 19.0 server the SESSIONTIMEZONE seems to be used.

All testet in direct mode (no client).

5

BTW: Are you using the ODAC components from depart (https://www.devart.com/de/dac/odac/)?
Last year we found a bug in OraTimeZone.pas in the DetectTimeZoneOffset procedure and the ConvertToLocalTime function. (In V13.2)

6

Hi Dirk,

Yes, we use Devart's ODAC components. If you know of a fix we could apply in OraTimeZone, I'm all ears.

-John

7

As I said, we are currently using V1320. I don't know if this will work for you, but I'll can send you our version of OraTimeZone.pas by email or by direct message.

8

Ok, I sent you an email. Thanks.