DB Timezone
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: DB Timezone

Hybrid View

  1. #1
    Join Date
    Mar 2002
    Posts
    301
    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
    Say No To Plastics

  2. #2
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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


    siva prakash
    DBA

  3. #3
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    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
    David Knight
    OCP DBA 8i, 9i, 10g

  4. #4
    Join Date
    Mar 2002
    Posts
    301
    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.
    Say No To Plastics

  5. #5
    Join Date
    Mar 2002
    Posts
    301
    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.
    Say No To Plastics

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width