Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Time conversion problems (time math)
William,
A select dbtimezone from dual will return your own timezone (if set - check the globalization guide) as a signed HH:MI format that shouldn't be too difficult for you to parse. That should make easy to you to compute the time difference between the database and user timezones. Convert to days, and regular date arithmetic should do the trick.
HTH Stéphane Faroult
William B Ferguson wrote:
>
> 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.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu May 25 2006 - 10:19:03 CDT
![]() |
![]() |