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

PRO+

Content

Find more PRO+ content and other member only offers, here.

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close