TO_TIMESTAMP Oracle : convertir une chaîne en timestamp

null

—

TO_TIMESTAMP Oracle : convertir une chaîne de caractères en timestamp

La fonction TO_TIMESTAMP est une fonction Oracle SQL essentielle qui permet de convertir une chaîne de caractères en valeur de type TIMESTAMP. Très utilisée dans les applications métier qui manipulent des données horodatées avec une précision inférieure à la seconde, TO_TIMESTAMP s’avère indispensable dès lors que vous travaillez avec des logs, des transactions financières ou des flux de données techniques nécessitant une précision temporelle élevée.

—

Définition et utilisation de TO_TIMESTAMP

En Oracle, le type TIMESTAMP est une extension du type DATE. Là où DATE stocke les informations jusqu’à la seconde, TIMESTAMP permet de conserver les fractions de secondes (jusqu’à 9 chiffres après la virgule). La fonction TO_TIMESTAMP sert à transformer une chaîne de texte représentant une date et une heure en ce type précis.

Cas d’usage en entreprise :

• Systèmes de logs applicatifs : horodatage précis des événements système ou applicatifs.
• Transactions bancaires : enregistrement de l’heure exacte d’une opération financière, avec les millisecondes.
• ETL et intégration de données : conversion de chaînes provenant de fichiers CSV ou de flux JSON avant insertion en base.
• Audit et traçabilité : comparaison précise d’horodatages dans des tables d’audit.

Contrairement à la fonction TO_DATE, qui retourne un type DATE, TO_TIMESTAMP retourne un TIMESTAMP, ce qui la rend plus adaptée aux contextes nécessitant une granularité fine.

—

Syntaxe de TO_TIMESTAMP

TO_TIMESTAMP( chaîne_caractères [, format_mask [, nls_params]] )

Paramètres

• chaîne_caractères (obligatoire) : la chaîne de texte à convertir. Elle doit correspondre au format spécifié dans le masque.
• format_mask (optionnel mais fortement recommandé) : le modèle de format décrivant la structure de la chaîne d’entrée. Exemples courants :
  • YYYY : année sur 4 chiffres
  • MM : mois sur 2 chiffres
  • DD : jour sur 2 chiffres
  • HH24 : heures au format 24h
  • MI : minutes
  • SS : secondes
  • FF ou FF3, FF6, FF9 : fractions de seconde (3, 6 ou 9 chiffres)
• nls_params (optionnel) : paramètre NLS permettant de préciser la langue ou le territoire pour l’interprétation des composantes textuelles (ex. noms de mois).

Si aucun masque n’est fourni, Oracle tente d’interpréter la chaîne selon le format NLS par défaut de la session, ce qui peut entraîner des erreurs ou des comportements imprévisibles. Il est donc fortement conseillé de toujours préciser le masque de format.

—

Exemples pratiques de TO_TIMESTAMP

Exemple 1 – Conversion simple d’une chaîne en timestamp

Contexte métier : Une application de gestion de commandes reçoit des horodatages au format texte depuis une API externe. Avant d’insérer ces données dans la table COMMANDES, il est nécessaire de les convertir en TIMESTAMP.

-- Conversion d'une chaîne de caractères en TIMESTAMP
-- Le masque correspond au format de la chaîne fournie par l'API
SELECT
    TO_TIMESTAMP('2024-06-15 14:32:07.654321', 'YYYY-MM-DD HH24:MI:SS.FF6') AS horodatage_commande
FROM DUAL;

Résultat :

HORODATAGE_COMMANDE
----------------------------
15/06/24 14:32:07,654321000

Ici, FF6 indique que la chaîne source contient 6 chiffres de fractions de seconde. Oracle les stocke en interne sur 9 chiffres et complète avec des zéros si nécessaire.

—

Exemple 2 – Insertion en table avec TO_TIMESTAMP

Contexte métier : Dans un système de supervision bancaire, on enregistre l’heure précise de chaque transaction dans une table TRANSACTIONS. Les données arrivent sous forme de chaînes de texte depuis un middleware.

-- Création d'une table exemple
CREATE TABLE TRANSACTIONS (
    id_transaction  NUMBER PRIMARY KEY,
    montant         NUMBER(15,2),
    horodatage      TIMESTAMP
);

-- Insertion avec TO_TIMESTAMP pour convertir la chaîne reçue
INSERT INTO TRANSACTIONS (id_transaction, montant, horodatage)
VALUES (
    1001,
    4500.75,
    TO_TIMESTAMP('15/06/2024 09:45:33.123', 'DD/MM/YYYY HH24:MI:SS.FF3')
);

-- Vérification de l'insertion
SELECT
    id_transaction,
    montant,
    horodatage
FROM TRANSACTIONS
WHERE id_transaction = 1001;

Résultat :

ID_TRANSACTION  MONTANT   HORODATAGE
--------------  --------  ----------------------------
1001            4500,75   15/06/24 09:45:33,123000000

Notez l’utilisation de FF3 pour indiquer 3 chiffres de milliseconde. La colonne horodatage est bien de type TIMESTAMP, ce qui garantit la précision de l’enregistrement.

—

Erreurs courantes avec TO_TIMESTAMP

Erreur ORA-01830 : le modèle de format se termine avant la fin de la chaîne

Cette erreur se produit lorsque le masque de format ne correspond pas exactement à la structure de la chaîne fournie en entrée. C’est l’une des erreurs les plus fréquentes lors de l’utilisation de TO_TIMESTAMP.

Exemple erroné :

-- Erreur : le masque ne couvre pas les fractions de secondes
SELECT TO_TIMESTAMP('2024-06-15 14:32:07.654', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
-- ORA-01830: le modèle de format se termine avant la fin de la chaîne entrée

Solution : Adapter le masque pour qu’il couvre l’intégralité de la chaîne, y compris les fractions de secondes :

-- Correction : ajout de FF3 pour les 3 chiffres de millisecondes
SELECT TO_TIMESTAMP('2024-06-15 14:32:07.654', 'YYYY-MM-DD HH24:MI:SS.FF3') FROM DUAL;

Conseil : Analysez toujours précisément la structure de vos chaînes source avant de définir le masque. En cas de données hétérogènes, envisagez de normaliser les chaînes en amont ou d’utiliser un bloc PL/SQL avec gestion d’exception.

—

Résumé

Bonnes pratiques Oracle

1. Spécifiez toujours le masque de format explicitement. Ne laissez pas Oracle interpréter le format selon les paramètres NLS de la session, car ceux-ci peuvent varier d’un environnement à l’autre (développement, recette, production) et provoquer des comportements incohérents ou des erreurs silencieuses.
2. Choisissez la précision des fractions de secondes adaptée à votre besoin. Utilisez FF3 pour les millisecondes, FF6 pour les microsecondes et FF9 pour les nanosecondes. Une précision excessive inutile peut alourdir les comparaisons et les tris dans vos requêtes.

—

Aller plus loin

Pour approfondir vos connaissances sur la gestion des dates et des timestamps en Oracle SQL, nous vous recommandons les articles suivants :

• TO_TIMESTAMP_TZ Oracle : apprenez à convertir des chaînes en TIMESTAMP WITH TIME ZONE pour gérer les fuseaux horaires dans vos applications internationales.
• TO_DATE Oracle : découvrez la fonction sœur de TO_TIMESTAMP pour la conversion de chaînes en type DATE, et comprenez quand utiliser l’une plutôt que l’autre.
• EXTRACT Oracle : maîtrisez l’extraction de composantes temporelles (année, mois, jour, heure, minute, seconde) depuis un TIMESTAMP ou une DATE pour vos agrégations et filtres métier.

Sur le même thème

• ROWNUM Oracle : Guide complet avec exemples SQL
• Les Verrous Oracle : Guide Complet pour Débutants
• ALTER SESSION Oracle : modifier sa session SQL facilement
• NUMTODSINTERVAL Oracle : Convertir un nombre en intervalle
• Flashback Drop Oracle : Récupérer une Table Supprimée
• SYSTIMESTAMP Oracle : Guide Complet avec Exemples SQL