Re: convert date to unixtime

Andy Hassall <andy_at_andyh.co.uk> wrote:
: On Thu, 02 Sep 2004 23:06:16 +0200, Peter Valicek <Sonny2_at_gmx.de> wrote:

:>it is possible and how to get intead of the date field an Unixtimestamp
:>back?
:>I know in MySQL exist an function UNIX_TIMESTAMP(fieldname) which can do
:>this. http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html
:>
:>How can I do this Oracle 8i?
:>I haven't found which fits my needs.

: Subtracting dates from each other gives you the difference in days. So:

: select (sysdate - to_date('19700101','YYYYMMDD')) * 86400 : from dual

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.

-- 
Peter Marksteiner
Vienna University Computer Center