[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4485
  • Last Modified:

Session time zone and db timezone

My 10g db timezone is set to GMT.
MY sessiontimezone it returns as -06:00 (CST). Where does it getting from ?
Say somebody from FL opens sqlplus session to my database what sessiontimezone will he see? Will be it -05:00 (EST)?
In short sessiontimezone is dependent on,  timezone the connection is getting from or does it take the Oracle Server/Host unix box timezone and it returns that to anybody across the world?
0
shasta123
Asked:
shasta123
  • 7
  • 4
  • 4
  • +2
3 Solutions
 
paquicubaCommented:
<< does it take the Oracle Server/Host unix box timezone and it returns that to anybody across the world? >>

Yeap!


Use TIMESTAMP WITH LOCAL TIME ZONE otherwise
0
 
shasta123Author Commented:
why my dbtimezone and sessiontimezone are different? They should be same to -06:00
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
paquicubaCommented:
If you're located where the server resides, then the database you're accessing was created with +00:00 offset. Issue an ALTER DATABASE:

SYSTEM@PROD > ALTER database SET TIME_ZONE = 'EST'
  2  /
ALTER database SET TIME_ZONE = 'EST'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns


In my case I will have to eliminate the TIMESTAMP WITH LOCAL TIME ZONE columns or recreate the database.
0
 
shasta123Author Commented:
ok, IN SHORT TO REITERATE, Please confirm.

Dbtimezone is clear which either set at the db creation time or using alter database command.

sessiontimezone can be changed with alter session command , but my question is say for eg: i installed brand new database with say default db timezone as GMT. I have'nt touched anything to the session.

Now the user in CST time zone  connects and queries session time zone what will it give?
Same if the user in EST time zone connects and queries sessiont timezone what will it give?
0
 
RCorfmanCommented:
It is my understanding that when a client connects, they pick up the database time zone. Their session has to be altered before the time zone changes and you start getting a different session time zone. You can do this via login trigger when you set the user up, if different users are using different logins to the database, or your application can do this if the application knows who they are, or they can do it manually if they had an sql prompt.

The database doesn't actually know what timezone the user is loggin in from, your application, or your login triggers have to tell the database that by altering their session.
0
 
paquicubaCommented:
Look at this example:

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 29 09:10:05 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

TEST@PROD > SELECT DBTIMEZONE FROM DUAL;            

DBTIME
------
+00:00                                            <<----------------- Database Time Zone is GMT

Elapsed: 00:00:00.00
TEST@PROD > SELECT SESSIONTIMEZONE FROM DUAL;

SESSIONTIMEZONE
---------------------------------------------------------------------------
-05:00                                            <<-----------------  Since I'm in the EST zone my session picks up my PC's time

Elapsed: 00:00:00.00
TEST@PROD >


However, as RCorfman explained above, any timestamp entered in the database will show you the database's timestamp unless you use TIMESTAMP WITH LOCAL TIME ZONE as your datatype.
0
 
RCorfmanCommented:
paquicuba, this is an area that I'm fuzzy on... how does the database know your local timezone when you login?  I figured it didn't unless you did an altersession, but you are showing that isn't the case. I'm wondering about the mechanism...
0
 
RCorfmanCommented:
I'm working with someone else that can get to my database that is in the central time zone (I'm in the Pacific). We are both going to log into the same database login and run the sql
select sessiontimezone from dual;
to see if we get different results or not...  I'll repost when I hear from my compatriot.
0
 
RCorfmanCommented:
I've decided I don't know the answer. When my co-worker logs in and runs, they get this:
SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
-----------------------------------------
+00:00

I get this:
SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------
-08:00

SQL>

I log in with sqlplus on windows.  Turns out so did my co-worker. So, there is definitely something at work to set mine, but it seems to not be consistent.  The other login should have been -06:00
0
 
shasta123Author Commented:
I am in CST i logged in sqlplus and it gave me -6:00 session timezone.

I changed my PC timezone to EST and I logged into another sqlplus window it gave me     -5:00 session timezone.

My earlier session still gives me CST.

So is it safe to confirm that the sessiontimezone is the timezone of the logging in session rather than dbtimezone?
0
 
actonwangCommented:
>>So is it safe to confirm that the sessiontimezone is the timezone of the logging in session rather than dbtimezone?

     I think so. It should not matter much because you can always use "alter session" to change it.

    Actually, session timezone is not that very ciritical in term of how data is stored, specifically for "timestamp with local timezone".  Data with that type will be stored inside database using database timezone. Both can be altered one way or the other.


   
0
 
paquicubaCommented:
Sorry guys I was busy...

@RCorfman  
     In Windows
       --> Double click the clock on the bottom right side of my computer screen
            --> Time Zone tab [ change it to a different time zone ]
               --> Open a new SQL*Plus session and you'll see that the sessiontimezone variable
                     gets its value from the calling environment.

Dbtimezone always gets its value from the server where the database is located.

Now, when storing timestamps in the database, the time zone of the session inserting the values will be used.


                                           ------ Database located in the Atlantic Time Zone ----------


------- Session in the EST zone:

SELECT DBTIMEZONE FROM DUAL;

DBTIME
------
-04:00


SELECT SESSIONTIMEZONE FROM DUAL;

SESSIONTIMEZONE
---------------------------------------------------------------------------
-05:00

CREATE TABLE TZ( COL1 TIMESTAMP );

Table created.

 INSERT INTO TZ VALUES( SYSTIMESTAMP );

1 row created.

SELECT * FROM TZ;

COL1
---------------------------------------------------------------------------
29-MAR-06 03.30.48.859000 PM


COMMIT;

Commit complete.      


                                                                                                        ---- Session in the Atlantic Time Zone

                                                                                                      SELECT SESSIONTIMEZONE FROM DUAL;

                                                                                                      SESSIONTIMEZONE
                                                                                                      ----------------------------------------------------
                                                                                                      -04:00

                                                                                                      SELECT * FROM TZ;

                                                                                                     COL1
                                                                                                     --------------------------------------------
                                                                                                    29-MAR-06 03.30.48.859000 PM

DELETE FROM TZ;

1 row deleted.

ALTER TABLE TZ MODIFY( COL1 TIMESTAMP WITH LOCAL TIME ZONE );

Table altered.

 INSERT INTO TZ VALUES( SYSTIMESTAMP );

1 row created.

COMMIT;

Commit complete.


SELECT SESSIONTIMEZONE FROM DUAL;

SESSIONTIMEZONE
---------------------------------------------------------------------------
-05:00

SELECT * FROM TZ;

COL1
---------------------------------------------------------------------------
29-MAR-06 03.33.09.593000 PM

                                                                                                                         


                                                                                                    SELECT * FROM TZ;

                                                                                                   COL1
                                                                                                   -----------------------------
                                                                                                   29-MAR-06 04.33.09.593000 PM
0
 
RCorfmanCommented:
I understand, but apparently this doesn't always work as my co-worker isn't having their time set correctly in thier database session, but it appears to work most of the time.  I know mine's correct and it seems that most of the people that have commented have it come up correct. BUT, can you always rely on it being correct based on where in the world they are logging in from, I'd have to say it looks like not.... can you usually... looks like yes.
0
 
actonwangCommented:
I don't think that session timezone matters a lot in any event. Besides you always can change it.
0
 
RCorfmanCommented:
since it seemed strange that my co-worker had +00:00 for their sessiontimezone, I had them confirm their windows settings...

Me>> On your PC, if you double-click on your pc clock in the task bar, press the time zone tab, what timezone does it say you are in?
Response>> Central – and the option is set to adjust for Daylight Savings Time.

I think this shows pretty clearly that there are other factors at play. I believe their Oracle client install is not local on their computers, but uses a network install, but I'm hesitant to continue interupting their work with questions in this regard.

I think it suffices to say that the sessiontimezone will NOT always be set upon connection to the database based on the clients time zone.
0
 
RCorfmanCommented:
I wonder if there is a setting in the sqlnet.ora that helps control this - indicates the client's timezone should be forwarded on login...? (I don't know, just stimulating more thought).
0
 
shasta123Author Commented:
BTW, alter session lets you change it anytime but you need to know the timezone the client is in.

With the sessiontimezone automatically determining that and if you use TIMESTAMP WITH LOCAL TIMEZONE you basically transparently changes the display of time to the clients time with no tweaks or coding , which I feel is very cool  feature and wanted to confirm.
0
 
actonwangCommented:
>>f you use TIMESTAMP WITH LOCAL TIMEZONE you basically transparently changes the display of time to the clients time with no tweaks or coding , which I feel is very cool  feature and wanted to confirm.

yup. that is what I am talking about. Oracle takes care of you automatically.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now