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...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.