Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Unanswered: timezone questions

    Oracle 9i, SQL and/or Forms, running on W2000

    2 questions:

    I can not find a function that returns the timezone appreviation (ie CST, GMT, etc.) The only things I have seen return the offset in hours (-08:00). In Forms you can use get_application_property but you had better hope the db timezone is set correctly. Which leads me to ...

    Is there no way to specify the default TZ for a database except at creation?

    The documentation talks about a small and a large TZ file put I can not see how that relates to retrieving the "current" TZ.

    Any help or additional insights would be appreciated.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    you need to set parameters on your database

    Look up setting these parameters.
    Code:
    nls_time_format                      string
    nls_time_tz_format                   string
    nls_timestamp_format                 string
    nls_timestamp_tz_format              string
    example:
    PHP Code:
    15:54:14 kod:PlatformALTER SESSION SET TIME_ZONE 'EST';

    Session altered.

    Elapsed00:00:00.00
    15
    :55:09 kod:PlatformSELECT SESSIONTIMEZONE FROM DUAL;

    SESSIONTIMEZONE
    -----------------------------------------------------------
    EST 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273

    Re: timezone questions

    Originally posted by Todd Barkus
    Oracle 9i, SQL and/or Forms, running on W2000

    2 questions:

    I can not find a function that returns the timezone appreviation (ie CST, GMT, etc.) The only things I have seen return the offset in hours (-08:00). In Forms you can use get_application_property but you had better hope the db timezone is set correctly. Which leads me to ...

    Is there no way to specify the default TZ for a database except at creation?

    The documentation talks about a small and a large TZ file put I can not see how that relates to retrieving the "current" TZ.

    Any help or additional insights would be appreciated.
    Answer (1)

    I guess Oracle only returns offset But you can use V$TIMEZONE_NAME and query the region with the specific offset.

    Answer 2.

    I guess You cannot specify the default TZ (in terms of 'GMT' or 'CST' word) but instead you can specify the default timezone format in init.ora file.
    NLS_TIMESTAMP_TZ_FORMAT = 'MMDDYYHH24MISSFF TZR'
    which allow you to perform DML for specific Timezone Rigion.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  4. #4
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Thanks for you help! It gives me something to go on.

  5. #5
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    From what I can see the NLS stuff just is setting a format mask for the to_char and to_date functions. V$TIMEZONE_NAMES only lists what is available but does not contain an offset value. (I do not think V$TIMEZONE_NAMES would be accessible by normal users anyway would it?)

    What started this whole exercise is that I need to display TZ with my times in Forms and Reports. Using get_application_property(DATETIME_LOCAL_TZ) I get 'GMT'. Playing around in SQL I get "-07:00" from dbtimezone and "-06:00" from sessiontimezone. Something in the DB knows that I am in the central time zone (US), but I have not been able to figure out how to get 'CST' or whatever.

    It seems odd that Oracle would make you recreate your data base if you moved your server from one timezone to another. Maybe my only option is to recreate the DB.

    Any references to other explanations of time stuff in Oracle would be appreciated. I have looked through most or Oracle's doc but have not found an answer. If I am just being thick and you are willing to lead me gently point me back to the Oracle doc and tell me why it answers my question.

    Thanks.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    I know you are on Windows, but I use SOlaris and use the DATE command to gather the timezone and pass that into a shell script.

    There has to be a command to show the timezone at the windows command prompt but the help in windows SUX.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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