Q

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?

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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close