Re: DATE with time zones

Mark D Powell wrote:
> You can select the date columns using a function that applies the
> local timezone's offset from the database timezone using the Oracle
> provided function: new_time.
>
> select to_char(sysdate,'YYYYMMDD HH24:MI:SS') as "Eastern Daylight"
> ,to_char(new_time(sysdate,'EDT','GMT'),'YYYYMMDD HH24:MI:SS')
> GMT
> from sys.dual
>
> You will need a method to get the user's time zone: table,
> sys_context, or other source, but it can be done.

That looks promising. I'll investigate further. Ad hoc, I see two issues:

1. NEW_TIME doesn't accept all time zone ids found in V$TIMEZONE_NAMES - in fact the set of accepted names seems rather small...
2. I'm not sure how good this will work in a view as both time zone parameters should be calculated dynamically (we can of course use dbtimezone and sessiontimezone but then 1 comes into play again).

Kind regards

    robert Received on Thu Oct 27 2005 - 07:27:32 CDT