RTFM
rtfm / SQL / Oracle Timestamps / src/utl_package.sql
.etla.org
CREATE or REPLACE
PACKAGE utl 
IS

/*
 * Demo C package for Oracle, for didactic purposes
 * Copyright (C) 2001 Vivek Dasmohapatra <vivek@etla.org>

 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.

 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.

 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */

    FUNCTION DATE_FMT      RETURN CHAR;
    FUNCTION EPOCH_STR     RETURN CHAR;
    FUNCTION EPOCH_JULIAN  RETURN INTEGER;
    FUNCTION EPOCH_SECONDS RETURN INTEGER;
    FUNCTION EPOCH_DATE    RETURN DATE;

    FUNCTION strcasecmp (str0 CHAR, str1 CHAR) RETURN PLS_INTEGER;

    FUNCTION date_t  (p_TIME INTEGER) RETURN DATE;
    FUNCTION date_t		      RETURN DATE;
    FUNCTION time_t  (p_DATE DATE)    RETURN PLS_INTEGER;
    FUNCTION time_t		      RETURN PLS_INTEGER;
    
END utl;
/


CREATE or REPLACE
PACKAGE BODY utl
IS

/*
 * Demo C package for Oracle, for didactic purposes
 * Copyright (C) 2001 Vivek Dasmohapatra <vivek@etla.org>

 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.

 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.

 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */
 
    /* default date format */
    FUNCTION DATE_FMT
    RETURN CHAR
    IS
    BEGIN
        RETURN  'yyyy/mm/dd hh24:mi:ss';
    END DATE_FMT;

    /* C epoch in seconds */
    FUNCTION EPOCH_SECONDS
    RETURN INTEGER
    IS
    BEGIN
        RETURN  0;
    END EPOCH_SECONDS;

    /* C epoch in string form */
    FUNCTION EPOCH_STR
    RETURN CHAR
    IS
    BEGIN
        RETURN  '1970/01/01 00:00:00';
    END EPOCH_STR;

    /* C epoch as an Oracle DATE */
    FUNCTION EPOCH_DATE
    RETURN DATE
    IS
    BEGIN
        RETURN  to_date(EPOCH_STR, DATE_FMT);
    END EPOCH_DATE;

    /* C epoch in Julian form */
    FUNCTION EPOCH_JULIAN
    RETURN INTEGER
    IS
    BEGIN
        RETURN to_number(to_char(to_date(EPOCH_STR,DATE_FMT),'J'),'9999999');
    END EPOCH_JULIAN;

    /* signed case insensitive comparison */
    FUNCTION strcasecmp (str0 CHAR, str1 CHAR)
    RETURN PLS_INTEGER
    IS EXTERNAL
    LIBRARY liborautl_so
    NAME "ora_strcasecmp"
    PARAMETERS (STR0 STRING, STR1 STRING, RETURN INT);

    /* equivalent of the time() [man time(2)] */
    FUNCTION time_t
    RETURN PLS_INTEGER
    IS EXTERNAL
    LIBRARY liborautl_so
    NAME "ora_time"
    PARAMETERS (RETURN INT);

    /* cast an oracle date to a unix time_t value */
    FUNCTION time_t (p_DATE DATE)
    RETURN PLS_INTEGER
    IS
        v_DATE      DATE;    /* datetime passed in as arg 0 */
        v_JULIAN    INTEGER; /* days from julian epoch to now */
        v_SECONDS   INTEGER; /* seconds since minight component of v_DATE */
        r_TIME_T    INTEGER; /* seconds since C epoch */

    BEGIN

        if p_DATE is NULL
        then

            r_TIME_T  := time_t;

        else

	v_DATE    := p_DATE;
	v_JULIAN  := to_number(to_char(v_DATE, 'J'), '99999999');
	v_SECONDS := to_number(to_char(v_DATE,  'hh24')) * 3600;
	v_SECONDS := v_SECONDS + (to_number(to_char(v_DATE, 'mi')) * 60);
	v_SECONDS := v_SECONDS +  to_number(to_char(v_DATE, 'ss'));

	r_TIME_T  := ((v_JULIAN - EPOCH_JULIAN) * 86400) + v_SECONDS;
	
        end if;

        RETURN r_TIME_T;

    END time_t;

    /* cast a time_t value to an oracle date */
    FUNCTION date_t (p_TIME INTEGER)
    RETURN DATE
    IS
        v_TIME    INTEGER;
        v_JULIAN  INTEGER;
        v_SECONDS INTEGER;
        r_DATE    DATE;

    BEGIN

        v_TIME    := NVL(p_TIME, time_t);
        v_SECONDS := MOD(v_TIME, 86400);
        v_JULIAN  := EPOCH_JULIAN + ((v_TIME - v_SECONDS)/ 86400);
        r_DATE    := to_date(v_JULIAN, 'J') + (v_SECONDS / 86400);

        return r_DATE;

    END date_t;

    /* this is it: a canonical datetime in the UTC timezone */
    FUNCTION date_t
    RETURN DATE
    IS
    v_TIME INTEGER;

    BEGIN
        v_TIME := time_t;
        RETURN date_t(time_t);
    END date_t;

END utl;
/

Valid HTML 4.01! Valid CSS! Any Browser Debian Pepperfish