Transformer un Timestamp UNIX en date Oracle – suite

Dans la recherche de solution pour Transformer un Timestamp UNIX en date Oracle, l’habitude de travailler uniquement avec des dates Oracle et pas avec des timestamps m’a détourné de la fonction unixts_to_date dont on peut trouver plusieurs versions sur Internet et qui renvoie un Timestamp Oracle à partir d’un timestamp Unix :

    CREATE OR REPLACE
    FUNCTION unixts_to_date(unixts IN PLS_INTEGER) RETURN TIMESTAMP WITH TIME ZONE IS
        /**
         * Converts a UNIX timestamp into an Oracle TIMESTAMP 
         */
        unix_epoch TIMESTAMP WITH TIME ZONE := TO_TIMESTAMP_TZ('19700101010000 CET','YYYYMMDDHH24MISS TZR');
        max_ts PLS_INTEGER := 2145916799; -- 2938-12-31 23:59:59
        min_ts PLS_INTEGER := -2114380800; -- 1903-01-01 00:00:00
        oracle_date TIMESTAMP WITH TIME ZONE;

        BEGIN
            IF unixts > max_ts THEN
                RAISE_APPLICATION_ERROR(
                    -20901,
                    'UNIX timestamp too large for 32 bit limit'
                );
            ELSIF unixts < min_ts THEN
                RAISE_APPLICATION_ERROR(
                    -20901,
                    'UNIX timestamp too small for 32 bit limit' );
            ELSE
                oracle_date := unix_epoch + NUMTODSINTERVAL(unixts, 'SECOND');
            END IF;

            RETURN (oracle_date);

    END;

Il suffit ensuite de forcer la conversion du timestamp oracle retourné en format date :

select cast(unixts_to_date(1000100001) as date) from dual;
CREATE OR REPLACE FUNCTION INFOCENTRE.date_to_unixts(oracle_date IN DATE) RETURN PLS_INTEGER IS
 /**
 * Converts an Oracle DATE to a UNIX timestamp
 */
 unix_epoch DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
 max_date DATE := TO_DATE('20380101000000','YYYYMMDDHH24MISS');
 min_date DATE := TO_DATE('19030101000000','YYYYMMDDHH24MISS');
 unix_ts PLS_INTEGER;

BEGIN
 IF oracle_date > max_date THEN
 RAISE_APPLICATION_ERROR( -20902,'Date too large for 32bit UNIX timestamp' );
 ELSIF oracle_date < min_date THEN
 RAISE_APPLICATION_ERROR( -20902,'Date too small for 32bit UNIX timestamp' );
 ELSE
 unix_ts := (oracle_date - unix_epoch) / (1/86400);
 END IF;

RETURN (unix_ts);

END;
 /

Pour un simple test, on réalise la requête suivante :

 select date_to_unixts(trunc(to_date('01/01/2012')))
 from dual;

ce qui donne 1325376000.

Et en vérifiant sur infowebmaster.fr, on trouve le… 1/1/2012 à 1:00:00 au lieu du 1/1/2012 à 0:00:00…
Pourtant la fonction PL ci-dessus semble correcte. Affaire à suivre..

 

2 réactions sur “ Transformer un Timestamp UNIX en date Oracle – suite ”

  1. Gabriel Réponse

    My BO version is R3.1 Linux. BusinessObjects Enterprise XI 3.1 for Linux-Supported Platforms docmneut includes Oracle Client 11.1 as supported DataSource.But last time I tried to upgrade from Oracle 9.2 to 11.1 & got the following error:/opt/busobj/bobje//ccm.sh: line 83: 11414 Floating point exception(core dumped) $CE_CMDLINE_PREFIX mozjsshell $CCMFILE $@ err: ERROR Failed to get auditing data source name. (STU00164)err: Error description: The data source name (DSN) could not be extracted from the connection string.Creating session manager Logging onto CMS err: ERROR Couldn’t logon to CMS (STU00152)err: Error description: The system xxservername can be contacted, but there is no Central Management Server running at port 6400.Is there anyone having this problem or experience a success in installing Oracle Client 11.1 to XI R3.1?Thx.

    • Rudy Réponse

      woooww such a great tutorial but untoltunafery not what I’m looking for do you have any idea how to do this in PHPoriginal arrayArray ([0] => Apple’ [1] => Banana’ [2] => Pineapple’ )sample random arrayArray ([0] => Banana’ [1] => Apple’ [2] => Pineapple’ )Array ([0] => Pineapple’ [1] => Apple’ [2] => Banana’ )Array ([0] => Apple’ [1] => Pineapple’ [2] => Banana’ )it randomize array value on every time.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *