Re: Daylight Savings Time Translation Error with Oracle 8i

Arun,

Thanks for the response.

I believe that I have narrowed this issue down to the TZ environ variable. If not there specifically, then Time Zone is _defintely_ a factor.

The conversions that you show are spot on for multi-TZ conversion, but my good fortune is that I only have to provide reports in a single timezone for consumers within that timezone. My Misfortune is that daylight savings time have snarled up all of my plans.

Your code is very good, but not what I need. I really do appreciate your giving time to answer my question!

Regards,
Dave

Arun Mathur wrote:
> Hi David,
>
> Using the number you retrieve for the epoch time, you can can derive
> the date, and then convert it from GMT to the time zone you want. You
> can use Oracle's new_time function, provided it supports your time
> zone.
>
> http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/function.htm#78068
>
> For example, let's say you retrieve a row where the epoch date is
> 1000000. Converting it to days gives you:
>
> SQL> select 1000000/(24*60*60) from dual;
>
> 1000000/(24*60*60)
> ------------------
> 11.5740741
>
> So, a little over 11 days past the 1st of January:
>
> SQL> select to_date('JAN-01-1970','MON-DD-YYYY')+(1000000/(24*60*60))
> from dual;
>
>
> TO_DATE('
> ---------
> 12-JAN-70
>
> 1* select
> to_char(to_date('JAN-01-1970','MON-DD-YYYY')+(1000000/(24*60*60)),'M
> ON-DD-YYYY HH:MI:SS AM') from dual
> SQL> /
>
> TO_CHAR(TO_DATE('JAN-01
> -----------------------
> JAN-12-1970 01:46:40 PM
>
> My timezone is currently EDT (GMT-4). So, using the new_time function,
> I can retrieve the date in a particular time zone and then convert it
> to a character representation, such as MON-DD-YYYY HH:MI:SS AM:
> SQL> select
> to_char(new_time(to_date('JAN-01-1970','MON-DD-YYYY')+(1000000/(24*60*60)),'GMT','EDT'),'MON-DD-YYYY
> HH:MI:SS AM') from dual;
>
> TO_CHAR(NEW_TIME(TO_DAT
> -----------------------
> JAN-12-1970 09:46:40 AM
>
> Here's what happens if I try EST (-5) and PST (-8):
>
> SQL> select
> to_char(new_time(to_date('JAN-01-1970','MON-DD-YYYY')+(1000000/(24*6
> 0*60)),'GMT','EST'),'MON-DD-YYYY HH:MI:SS AM') from dual;
>
> TO_CHAR(NEW_TIME(TO_DAT
> -----------------------
> JAN-12-1970 08:46:40 AM
>
> 1* select
> to_char(new_time(to_date('JAN-01-1970','MON-DD-YYYY')+(1000000/(24*6
> 0*60)),'GMT','PST'),'MON-DD-YYYY HH:MI:SS AM') from dual
> SQL> /
>
> TO_CHAR(NEW_TIME(TO_DAT
> -----------------------
> JAN-12-1970 05:46:40 AM
>
> If the function doesn't include your time zone, you can still get the
> date in the format you want by adding the quotient of your time zone's
> gmt offset and 24. So, let's say I want to display a date in the
> Australian Eastern Standard Time format (GMT +10):
>
> 1* select
> to_char(to_date('JAN-01-1970','MON-DD-YYYY')+(1000000/(24*60*60))+(1
> 0/24),'MON-DD-YYYY HH:MI:SS AM') from dual
> SQL> /
>
> TO_CHAR(TO_DATE('JAN-01
> -----------------------
> JAN-12-1970 11:46:40 PM
>
> Is this what you're looking for?
>
> Regards,
> Arun
>
Received on Tue Apr 12 2005 - 15:24:58 CDT