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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.