Transformer un Timestamp UNIX en date Oracle

 

Récemment, j’ai travaillé sur la base de donnée Oracle d’un éditeur qui contient des timestamps (nombre de secondes écoulées depuis le 1er janvier 1970 à minuit UTC précise – voir timestamp.fr).
Ces timestamps sont ainsi une manière de stocker des dates avec un niveau de précision plus précis que le format “date” qui va jusqu’à la seconde.
A noter que les timestamps de cette base ne sont pas des timestamps Oracle, mais des timestamps issus d’Unix qui sont stockés dans des varchar2 (chaines).

  • traduire un timestamp en date

A première vue, obtenir des dates à partir de ces timestamp parait aisé, il suffit de prendre le 01/01/1970 et d’y ajouter le nombre de seconde correspondant au timestamp :

Pour l’exemple, je vais prendre le timestamp 1346699752 (03/09/2012 à 21:15:52).

select to_date(’01/01/1970′,’DD/MM/YYYY’) + 1346699752/3600/24
from dual;

Et déjà on peut entrevoir le problème puisque le résultat ne correspond pas au 03/09/2012 à 21:15:52 mais au 03/09/2012 à 19:15:52.

  • le problème du fuseau horaire

Il manque l’ajout du fuseau horaire puisque Le timestamp (unix) désigne le nombre de secondes écoulées depuis le 1er janvier 1970 à minuit UTC précise.

Je me dis ensuite que c’est facile qu’il suffit d’ajouter le décalage du à la time zone, que j’obtiens par exemple ainsi :

SELECT TZ_OFFSET(‘Europe/Paris’) FROM DUAL;
+02:00

Mais sous Oracle, impossible de récupérer le décalage d’heure en fonction d’une date, ce qui est essentiel avec le passage heure d’été/heure d’hivers.

  • Bug Oracle ? pas de cast de timestamp en Europe/Paris

Je ne sais pour quelle raison, la conversion (cast ci-dessous) dans la fonction ci-dessous n’a jamais voulu marcher vers certaines villes notamment Europe/Paris, ce qui m’a été confirmé sur des forums.
Comme, et ça tombe vraiment bien, depuis 1998, les changements d’heure s’effectuent donc au même moment dans tous les pays de l’Union Européenne et que je n’ai  pas de données antèrieures à 1998, j’applique la solution suivante :

Je fais une conversion de timestamp vers Europe/Dublin (qui lui fonctionne ?) et je rajoute une heure, voici donc ma solution :

WITH DATA AS (
    SELECT from_tz(cast(to_date(’01/01/1970′,’DD/MM/YYYY’) + 1346699752/3600/24 AS timestamp),’Europe/Dublin’) d1
     FROM dual
    )
    SELECT d1 + (extract(TIMEZONE_HOUR FROM d1)+1)/24 ds3
    FROM DATA;

Il ne reste plus qu’à faire une petite fonction pour encapsuler tout ça :

CREATE OR REPLACE FUNCTION INFOCENTRE.f_ts_to_date(p_timestamp number)
RETURN date IS
dateRet date;
BEGIN
   

WITH DATA AS (
    SELECT from_tz(cast(to_date(’01/01/1970′,’DD/MM/YYYY’) + p_timestamp/3600/24 AS timestamp),’Europe/Dublin’) d1
     FROM dual
    )
    SELECT d1 + (extract(TIMEZONE_HOUR FROM d1)+1)/24 ds3
    into dateRet
    FROM DATA;

  RETURN dateRet;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
    WHEN OTHERS THEN
      — Consider logging the error and then re-raise
      RAISE;
END f_ts_to_date;

Pourquoi faire simple quant on peut faire compliqué ?

Voir aussi :
TOAD : quelques raccourcis utiles et peu connus de l’éditeur SQL

 

Abonnez-vous à la newsletter pour ne pas manquer les prochains articles :

YTo3OntzOjk6IndpZGdldF9pZCI7czoyMDoid3lzaWphLW5sLTEzNDY3NjUxNzkiO3M6NToibGlzdHMiO2E6MTp7aTowO3M6MToiMSI7fXM6MTA6Imxpc3RzX25hbWUiO2E6MTp7aToxO3M6MTk6Imxpc3RlIGFib25uw6lzIGJsb2ciO31zOjEyOiJhdXRvcmVnaXN0ZXIiO3M6MTc6Im5vdF9hdXRvX3JlZ2lzdGVyIjtzOjEyOiJsYWJlbHN3aXRoaW4iO3M6MTM6ImxhYmVsc193aXRoaW4iO3M6Njoic3VibWl0IjtzOjEzOiJKZSBtJ2Fib25uZSAhIjtzOjc6InN1Y2Nlc3MiO3M6NzI6IlbDqXJpZmlleiB2b3RyZSBib8OudGUgZGUgcsOpY2VwdGlvbiBhZmluIGRlIGNvbmZpcm1lciB2b3RyZSBhYm9ubmVtZW50LiI7fQ==

 

 

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

  1. Ping Oracle : temps passés entre 2 dates (en heure minute seconde) | Cube Conseil

  2. Ping Transformer un Timestamp UNIX en date Oracle – suite | Cube Conseil

Laisser un commentaire

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