DB Timezone

[oravijay]

Hi,

Basically we will have one server wich will have multiple instances belonging to different countries.

So we planned for creating each individual database with their respective time zone.(We are using Oracle9i Release 1).

So I created a database with time zone as '+09:30'.

But when I give select to_char(sysdate,'hh24:mi:ss') from dual ; it is giving the server's time.

How do I accomplish this?

Thanks.
Vijay.
But when I g

[prakashs43]

1. At creation time
SQL> CREATE DATABASE ...
SET TIME_ZONE='Europe/London';
If not specified with the CREATE DATABASE statement, the database time zone
defaults to the server’s O/S timezone offset.

2. After database creation, use the ALTER DATABASE SET TIME_ZONE statement and
then shut down and restart the database.
SQL> ALTER DATABASE SET TIME_ZONE = '-05:00';
SQL> ALTER DATABASE SET TIME_ZONE = 'Europe/Lisbon';
The change will not take effect until the database is bounced.

select dbtimezone from dual;
-04:00
select sessiontimezone from dual;
+03:00.


select new_time(sysdate,to_char(current_timestamp,'TZD'),'GMT') from dual; -- this is for US/Easter Time Stamp

[dknight]

Avoiding hardcoding of the staggering has one other advantage:

The dates on which Daylight Savings Time in the US and Sommerzeit in Germany (and i believe other Euro Zone countries) is different by one week.

Good luck

[oravijay]

Hi,

Is it possible to get like this:

One server will have many instances which will point to different database.

Ex: India, Singapore and Australia.

When I connect to Singapore database and give select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual, it should give me India time and if I do the same thing from Singapore instance then, it should give me Singapore time.

Is this possible.

According to Prakashs43,
I did the following:

1. SQL> select dbtimezone from dual ;

DBTIME
------
+05:30

The database was created without specifying the TIME_ZONE clause.

2. SQL> select sessiontimezone from dual ;

SESSIONTIMEZONE
--------------------------------------------
+05:30

The client session was also started from India.

3. SQL> select new_time(sysdate,to_char(current_timestamp,'TZD'),'GMT') from dual;

NEW_TIME(
---------

This statement produces a NULL.

4. SQL> alter session set time_zone='Asia/Calcutta' ;

Session altered.

SQL> select sessiontimezone from dual ;

SESSIONTIMEZONE
---------------------------------------------------------------------------
Asia/Calcutta

5. SQL> select new_time(sysdate,to_char(current_timestamp,'TZD'),'GMT') from dual;
select new_time(sysdate,to_char(current_timestamp,'TZD'),'GMT') from dual
*
ERROR at line 1:
ORA-01857: not a valid time zone

How can I overcome this error message.

Thanks.

[oravijay]

Hi,

Let me give brief you the requirement:

Basically we are planning to have a single database server which will have 3 instances(belonging to different countries).
Ex: Singapore, India and Australia.

The server is going to be located in India.

Our application uses SYSDATE a lot. So whenever I give a query like 'select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual' I would like to see the country-specific time.

I did a bit of research on this and found out that,

1. Start OracleServiceIndia by setting the Time Zone to India.
2. Start OracleServiceAustralia by setting the Time Zone to Australia and the same method for Singapore.

This gives a impression that whenever the service is started it is picking up the time zone from the OS and based on that
it sets the time zone and the sysdate. If we follow this method then it works fine.

But if someone starts the service(without changing the time zone) then the entire application will go for a toss.

Can we specify the time zone while starting the service or is there any other work around for this.

Thanks.