How to define a time zone in 8i that Oracle will accept

I am fairly new to Oracle (8.1.7) and as the new courses are done for 9i, I have some questions. Recently it became neccessary for me to extract data from a table (database in GMT time zone) and have the data formatted to GMT +2 time. A SQL function called NEW_TIME allows you to substitute a given time zone for another, however, I am unable to find the 3 letter abbreviated code that stands for GMT +2. For example:
select TIMECOLUMN,
new_time(TIMECOLUMN,'EST','GMT') as NEWTIME_COLUMN
from TABLE_NAME;
This example substitutes the GMT zone for EST zone. There are roughly 18 time zones according to my Oracle Complete Reference book, but no three-letter abbreviation for a time zone equal to GMT +2. Can you help me please? How do I define a time zone in 8i that Oracle will accept as a valid time zone?

    Requires Free Membership to View

    Login

The time zones for use in NEW_TIME are pre-defined and I don't know of any way to add any others to that list. But....if what you want to do is convert the time to GMT + 2, you could simply use the statement as you have it shown in your example except simply add 2 hours to the NEW_TIME result. Your statement would have this simple revision:

select TIMECOLUMN, new_time(TIMECOLUMN,'EST','GMT') + (7200/86400) as
NEWTIME_COLUMN
  from TABLE_NAME;
Note how I used the total number of seconds in two hours (2 hrs * 60 min * 60 secs = 7200) divided by the number of total seconds in a day (24 hrs * 60 min * 60 secs = 86400).

It may not be pretty but it will get you the answer you want!

For More Information


This was first published in July 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top