Re: convert date to unixtime

Peter Marksteiner wrote:

> The Unix timestamp always refers to UTC, the timezone of the
> Oracle "date" datatype is undefined. A slightly improved version:
>
> CREATE OR REPLACE FUNCTION oracle_to_unix(in_date IN DATE)
> RETURN NUMBER
> IS
> BEGIN
> RETURN (in_date -TO_DATE('19700101','yyyymmdd'))*86400 -
> TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600;
> END;
> /
>
> This presupposes that the timezone of the input value
> is the session timezone, which may be true or not. The reverse
> function would be
>
> CREATE OR REPLACE FUNCTION unix_to_oracle(in_number NUMBER)
> RETURN DATE
> IS
> BEGIN
> RETURN TO_DATE('19700101','yyyymmdd') + in_number/86400 +
> TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))/24;
> END;
> /
>
> Both functions require slight modifications for countries
> such as India where the timezone offset from UTC is not an
> integer multiple of one hour.
>

Thx this looks like what I want.
Danke nochmal.

_peter Received on Thu Sep 02 2004 - 17:59:35 CDT