Home » RDBMS Server » Server Administration » users with different timezone
users with different timezone [message #278112] Thu, 01 November 2007 18:41 Go to next message
nil123
Messages: 6
Registered: November 2007
Junior Member
Hi,

I've a need where I need to create users which have different time zone. so when that user logs in, he/she gets their local time, when they do select sysdate... Is it possible ?

I was thinking about creating logon trigger, and then user alter session set time_zone=.....
Any problem with that ?

OR

any other way to assign different time zone ?
Thanks
Re: users with different timezone [message #278113 is a reply to message #278112] Thu, 01 November 2007 18:46 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>I've a need where I need to create users which have different time zone
What is the actual business requirement?

Since now days many applications are web based, how will Oracle "know" if I have traveled to a different timezone when I login?
Re: users with different timezone [message #278119 is a reply to message #278113] Thu, 01 November 2007 21:15 Go to previous messageGo to next message
nil123
Messages: 6
Registered: November 2007
Junior Member
The current set up is in sql server for different countries with each one in different time zone. There is a need to record certain activity at actual time in their local time. Once we move all of them in to oracle ( one db ), it's timezone will be according to US.

Now, the existing code we have won't work ( e.g. sysdate - 1 )... because there are some time sensitive data. if we continue using the same code, we'll have different results then before because now sysdate-1 will return more/less no of records.

I know column type " with local timezone " but that would require changing a lot in many tables. so I was thinking of a simple solution where each country becomes a schema and has a different timezone. ( i think postgress has that feature )


Any ideas...

Thanks
Re: users with different timezone [message #278152 is a reply to message #278112] Fri, 02 November 2007 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

create users which have different time zone. so when that user logs in, he/she gets their local time

Each connected user is in his time zone, the one declared on his system (or the one of application server in n-tiers environment).

Quote:

know column type " with local timezone " but that would require changing a lot in many tables. so I was thinking of a simple solution where each country becomes a schema and has a different timezone.

Do you think this is easier? Do you mean that each country has its data and noone in another country will see it? If not how do rewrite your query to mix the data?

Oracle has datatype to handle time zones, you have to use them if you want to take care of time zones.

Regards
Michel
Re: users with different timezone [message #278281 is a reply to message #278152] Fri, 02 November 2007 12:27 Go to previous messageGo to next message
nil123
Messages: 6
Registered: November 2007
Junior Member
Each country's data is visible to their users only, no mix up.

I was thinking setting up time_zone for each user is possible, so no need to change underlying application. but seems like no other way...

Thanks
Re: users with different timezone [message #278288 is a reply to message #278281] Fri, 02 November 2007 12:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said you don't need to set up the client time zone, it is already set and can be visible with:
SQL> SELECT SESSIONTIMEZONE FROM DUAL;
SESSIONTIMEZONE
-----------------------------------------
+01:00

1 row selected.

Regards
Michel
Re: users with different timezone [message #278329 is a reply to message #278288] Sat, 03 November 2007 01:11 Go to previous message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
As Michel pointed out.. no need to set local timezone on client.

SQL> SELECT SYSTIMESTAMP FROM dual;    -- on Server

SYSTIMESTAMP
----------------------------------------------------------
03-NOV-07 11.36.56.127252 AM +05:30

SQL> SELECT CURRENT_TIMESTAMP FROM dual;  -- on client.. my machine

CURRENT_TIMESTAMP
----------------------------------------------------------
03-NOV-07 02.07.18.342920 AM -04:00


SQL>

My System timezone: EDT


--Girish

[Updated on: Sat, 03 November 2007 01:12]

Report message to a moderator

Previous Topic: Oracle Shuts down automatically
Next Topic: Tablespaces and differing drives (merged)
Goto Forum:
  


Current Time: Sun Dec 04 14:52:07 CST 2016

Total time taken to generate the page: 0.07964 seconds