Time conversion problems (time math)
- From: William B Ferguson <wbfergus@xxxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx, oracle-l-bounce@xxxxxxxxxxxxx
- Date: Thu, 25 May 2006 08:00:00 -0600
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?
Thanks,
-----------------------------------------------------------------------------
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 ~
Other related posts:
