This content has been marked as final. Show 5 replies
sql developer timezone TZ format is defined in the preferences/database/nls settings.
If the mask ends with TZR, it means it will display the time zone region rather than a timezone offset.
Probably sqlplus settings, either as environment variable or in the registry (in Windows) are defined differently.
I can't check myself right now, but this should explain the behavior.
I fiddled with the settings but it doesn't seem to do anything. To clarify my ?, I ran this in both SQL Developer and SQL*Plus:
In SD (ran as a script) I got these values:
alter session set time_zone = local ; select sessiontimezone from dual ; alter session set time_zone = '-06:00' ; select sessiontimezone from dual ; alter session set time_zone = 'US/Pacific' ; select sessiontimezone from dual ; alter session set time_zone = '04:00' ; select sessiontimezone from dual ;
In SQL*Plus I got these values:
I didn't change any settings between statements. Notice that the only one that displays differently is the one that was set with "Local".
I'm still puzzled.
Edited by: SQL-Something on Sep 24, 2010 2:04 PM
Here is the rather obscure definition taken from the SQL reference, ALTER SESSION chapter:
+"Specify LOCAL to set the default local time zone offset of the current SQL session to the original default local time zone offset that was established when the current SQL session was started."+
I must admit that at this hour of the night (for me) it's becoming increasingly difficult to decipher certain explanations.
I think I've figured part of this out...
There's a client side environment variable ORA_SDTZ. By default it wasn't even there, but setting it causes SQLplus to use it as the default TZ (offset or named TZ). If it's not there, I SUSPECT that it's pulling the TZ from the DB-server -- but I'd like to know for sure.
Now for SQL Developer... the environment variable seems to have no effect on it. Again I'm SUSPECTING that it is because SD is using a JDBC thin connection and it bypasses the ora-client on my machine as well as the env-vars. So the question remains, where does SD pick up the default / "Local" TZ setting? It doesn't seem to be from my PC nor the DB server (of course it has to be in there somewhere -- it just doesn't match any of the settings that I'm seeing).
did you read the following section of the [Database Globalization Guide|http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1006728]?
what do you get from this query?
SELECT sessiontimezone FROM DUAL;
My current understanding is that it's a way of reverting the session time zone to the same value it had when the session commenced, that, in turn, is affected by the setting of ORA_SDTZ, if any.
If ORA_SDTZ is not specified, it will default to '+00:00' that, i guess, is like saying UTC time.