Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Time conversion problems (time math)

Time conversion problems (time math)

From: William B Ferguson <>
Date: Thu, 25 May 2006 08:00:00 -0600
Message-ID: <>

First, I'm using 10.2.  

I have a table with a field called user_timezone that has values like -6, 5.5, -7, etc. Basically a numeric version of the timezone offset from UTC.  

I also have a table (odf_thread) with a field called updated_on (timestamp with time zone). There is also another field called thread_message.  

Now, when a user updates a record (thread_message), I need to take the systimestamp value, and convert that to the user's timezone, and then append
the user's timestamp (nicely formatted of course) into the thread_message. I'm just having a ton of problems trying to get anything to work correctly,
using the table structures I've been dealt (and I can't change them).  

The data in v_$timezone_names doesn't have the numeric (or date) offest from
UTC. I've tried using new_time(), but most of the tzabbrev's in that view generate an error of 'not a valid time zone', so I'm not sure what the usefullness of that view is.  

I've tried all kinds of variations of extract, from_tz, etc., and I either get some sort of error about mismatched datatypes, wrong number of arguments, invalid conversion, etc. whenever I can get the pieces (hours and
minutes) parsed out and try to do addition with the data I have in the user_timezone. I've even re-parsed the user_timezone data into the 'hh:mm' format.  

The documentation on performing time math is sparse at best, and what little
there is has terrible examples.  

Using the data structures I have, is there any easy way to simply convert from the systimestamp value to whatever the user's local time would be?  


                               Bill Ferguson
            U.S. Geological Survey - Minerals Information Team
                           PO Box 25046, MS-750
                           Denver Federal Center
                          Denver, Colorado 80225
           Voice (303)236-8747 ext. 321     Fax   (303)236-4208
      ~ Think on a grand scale, start to implement on a small scale ~

-- Received on Thu May 25 2006 - 09:00:00 CDT

Original text of this message