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.
This was first published in July 2002