Re: Daylight Savings Time Translation Error with Oracle 8i

From: Arun Mathur <themathurs_at_gmail.com>
Date: 12 Apr 2005 11:44:14 -0700
Message-ID: <1113331454.859308.100860@z14g2000cwz.googlegroups.com>

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 - 13:44:14 CDT