OraFAQ Forum: Server Administration » users with different timezone

Home » RDBMS Server » Server Administration » users with different timezone

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

users with different timezone [message #278112]

Thu, 01 November 2007 18:41

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

Report message to a moderator

Re: users with different timezone [message #278113 is a reply to message #278112]

Thu, 01 November 2007 18:46

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: users with different timezone [message #278119 is a reply to message #278113]

Thu, 01 November 2007 21:15

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

Report message to a moderator

Re: users with different timezone [message #278152 is a reply to message #278112]

Fri, 02 November 2007 02:10

Michel Cadot
Messages: 68686
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

Report message to a moderator

Re: users with different timezone [message #278281 is a reply to message #278152]

Fri, 02 November 2007 12:27

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

Report message to a moderator

Re: users with different timezone [message #278288 is a reply to message #278281]

Fri, 02 November 2007 12:54

Michel Cadot
Messages: 68686
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

Report message to a moderator

Re: users with different timezone [message #278329 is a reply to message #278288]

Sat, 03 November 2007 01:11

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 16:18:05 CDT 2024