Tutoriels

Migration d'horodatage entre bases de données : guide complet

##Présentation

La migration des données d'horodatage entre différents systèmes de bases de données est un défi courant dans les projets de développement d'applications et d'entrepôt de données. Chaque base de données possède ses propres types de données d'horodatage, ses fonctions et ses bonnes pratiques. Ce guide complet vous apprendra comment migrer les horodatages entre MySQL, PostgreSQL, SQLite, Oracle et SQL Server sans perte de données.

Comprendre les stratégies de stockage d'horodatage

Option 1 : horodatage Unix (entier)

L'approche la plus portable consiste à stocker les horodatages sous forme de secondes d'époque Unix ou de millisecondes sous forme d'entiers.

-- MySQL
CREATE TABLE events (
    id INT PRIMARY KEY,
    created_at BIGINT NOT NULL  -- Unix timestamp in seconds
);

-- PostgreSQL  
CREATE TABLE events (
    id INT PRIMARY KEY,
    created_at BIGINT NOT NULL
);

-- SQLite
CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    created_at INTEGER NOT NULL
);

Avantages :

Format universel sur toutes les bases de données
Aucun problème de fuseau horaire
Facile à convertir vers n'importe quel format d'affichage
Efficace pour l'indexation et la comparaison

Inconvénients :

Non lisible par l'homme sous forme brute
Nécessite une conversion pour le débogage

Option 2 : Chaîne ISO 8601

Stockez les horodatages sous forme de chaînes au format ISO 8601.

-- MySQL
CREATE TABLE events (
    id INT PRIMARY KEY,
    created_at VARCHAR(26) NOT NULL  -- '2025-01-15T10:30:00.000Z'
);

-- PostgreSQL
CREATE TABLE events (
    id INT PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL
);

Avantages :

Lisible par l'homme
Comprend des informations sur le fuseau horaire -Format standard

Inconvénients :

Plus grande taille de stockage
Comparaisons plus lentes
L'efficacité de l'indice varie

Option 3 : Types d'horodatage natifs

Utilisation des types d'horodatage natifs de chaque base de données.

-- MySQL
CREATE TABLE events (
    created_at DATETIME
);

-- PostgreSQL
CREATE TABLE events (
    created_at TIMESTAMP WITH TIME ZONE
);

-- SQL Server
CREATE TABLE events (
    created_at DATATIME2
);

Avantages :

Meilleures performances pour les opérations de date
Validation intégrée
Écosystème de fonctions riche

Inconvénients :

Complexité des migrations
La gestion des fuseaux horaires varie

Scénarios de migration

Scénario 1 : MySQL vers PostgreSQL

Conversion du DATETIME de MySQL en TIMESTAMPTZ de PostgreSQL.

-- Source (MySQL)
SELECT id, created_at FROM events;

-- Migration Script (PostgreSQL)
-- Option A: Using TO_TIMESTAMP()
INSERT INTO events (id, created_at)
SELECT id, TO_TIMESTAMP(created_at, 'YYYY-MM-DD HH24:MI:SS')
FROM events_source;

-- Option B: Using Unix timestamp (recommended)
-- First, convert MySQL DATETIME to Unix timestamp
SELECT UNIX_TIMESTAMP(created_at) FROM events;

-- Then insert into PostgreSQL
INSERT INTO events (id, created_at)
SELECT id, TO_TIMESTAMP(unix_ts)
FROM events_source;

Scénario 2 : Oracle vers SQL Server

Migration du DATE d'Oracle vers le DATETIME2 de SQL Server.

-- Source (Oracle)
SELECT hire_date FROM employees;

-- Migration Script (SQL Server)
-- Option A: Direct conversion
INSERT INTO employees (hire_date)
SELECT CAST(hire_date AS DATETIME2)
FROM employees_source;

-- Option B: Using Unix epoch (preferred)
-- Oracle: Get epoch seconds
SELECT (hire_date - DATE '1970-01-01') * 86400 AS epoch_seconds
FROM employees;

-- SQL Server: Convert epoch to datetime
INSERT INTO employees (hire_date)
SELECT DATEADD(SECOND, epoch_seconds, '1970-01-01')
FROM employees_source;

Scénario 3 : Conversion entre fuseaux horaires

Lors de la migration entre des bases de données avec des exigences de fuseau horaire différentes.

-- PostgreSQL: Convert UTC to specific timezone
INSERT INTO events (id, created_at)
SELECT id, created_at AT TIME ZONE 'America/New_York'
FROM events_source;

-- MySQL: Convert using CONVERT_TZ
INSERT INTO events (id, created_at)
SELECT id, CONVERT_TZ(created_at, '+00:00', '-05:00')
FROM events_source;

-- SQL Server: Convert using AT TIME ZONE
INSERT INTO events (id, created_at)
SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'
FROM events_source;

## meilleures pratiques

1. Utilisez toujours UTC pour le stockage

-- Store all timestamps in UTC
ALTER TABLE events 
ADD COLUMN created_at_utc TIMESTAMP WITH TIME ZONE;

UPDATE events 
SET created_at_utc = created_at AT TIME ZONE 'UTC';

-- Drop the old column after verification
ALTER TABLE events DROP COLUMN created_at;

2. Valider avant la migration

-- Check for invalid timestamps
SELECT COUNT(*) as invalid_count
FROM events_source
WHERE created_at IS NULL 
   OR created_at < '1970-01-01'::timestamp
   OR created_at > '2038-01-19'::timestamp;  -- 32-bit overflow

3. Utiliser des tables intermédiaires

-- Create staging table
CREATE TABLE events_staging (LIKE events);

-- Load data
INSERT INTO events_staging 
SELECT * FROM events_source;

-- Validate and clean
UPDATE events_staging 
SET created_at = NOW() 
WHERE created_at IS NULL;

-- Verify row counts
SELECT 
    (SELECT COUNT(*) FROM events_source) as source_count,
    (SELECT COUNT(*) FROM events_staging) as staging_count,
    (SELECT COUNT(*) FROM events) as target_count;

4. Gérer les valeurs nulles

-- PostgreSQL
INSERT INTO events (id, created_at)
SELECT id, COALESCE(created_at, NOW())
FROM events_source;

-- MySQL
INSERT INTO events (id, created_at)
SELECT id, IFNULL(created_at, NOW())
FROM events_source;

-- SQL Server
INSERT INTO events (id, created_at)
SELECT id, ISNULL(created_at, GETDATE())
FROM events_source;

Exemples de code par langue

JavaScript/Node.js

// Migrate timestamps using Node.js
const mysql = require('mysql2/promise');
const { Pool } = require('pg');

async function migrateTimestamps() {
    const mysqlPool = await mysql.createPool({
        host: 'mysql-source',
        database: 'source_db'
    });

    const pgPool = new Pool({
        host: 'postgres-target',
        database: 'target_db'
    });

    // Get all records
    const [rows] = await mysqlPool.query('SELECT * FROM events');

    // Transform and insert
    for (const row of rows) {
        const unixTimestamp = Math.floor(row.created_at.getTime() / 1000);
        await pgPool.query(
            'INSERT INTO events (id, created_at) VALUES ($1, to_timestamp($2))',
            [row.id, unixTimestamp]
        );
    }
}

###Python

# Migrate timestamps using Python
import pymysql
import psycopg2
from datetime import datetime

def migrate_timestamps():
    # Source connection
    mysql_conn = pymysql.connect(
        host='mysql-source',
        database='source_db'
    )

    # Target connection
    pg_conn = psycopg2.connect(
        host='postgres-target',
        database='target_db'
    )

    with mysql_conn.cursor() as cursor:
        cursor.execute('SELECT id, created_at FROM events')
        rows = cursor.fetchall()

        with pg_conn.cursor() as pg_cursor:
            for row in rows:
                # Convert MySQL datetime to Unix timestamp
                unix_ts = int(row[1].timestamp())
                pg_cursor.execute(
                    'INSERT INTO events (id, created_at) VALUES (%s, to_timestamp(%s))',
                    (row[0], unix_ts)
                )

        pg_conn.commit()

Liste de contrôle de validation

Avant de passer en production :

Compatibilité des types de données : Vérifiez que la colonne cible peut contenir toutes les valeurs source
Gestion du fuseau horaire : confirmez que tous les horodatages sont au format UTC ou dans la politique de fuseau horaire du document.
Validation de plage : vérifiez les horodatages avant 1970 ou après 2038
Gestion des NULL : décidez comment gérer les horodatages NULL/manquants
Performances : tester la migration sur un volume de données représentatif
Plan de restauration : disposer d'une procédure de restauration vérifiée

Pièges courants

1. Ignorer les millisecondes

-- Wrong: Losing millisecond precision
INSERT INTO target SELECT created_at FROM source;

-- Correct: Preserve milliseconds
INSERT INTO target 
SELECT created_at AT TIME ZONE 'UTC' FROM source;

2. Mauvaise configuration du fuseau horaire

-- Wrong: Assuming local time
INSERT INTO target (created_at)
SELECT created_at FROM source;

-- Correct: Explicit UTC conversion
INSERT INTO target (created_at)
SELECT COALESCE(
    created_at AT TIME ZONE 'UTC',
    NOW()
) FROM source;

3. Débordement d'entier 32 bits

-- Check for timestamps that will overflow 32-bit
SELECT * FROM events 
WHERE created_at > '2038-01-19 03:14:07'::timestamp;

Conclusion

La migration des horodatages entre les bases de données nécessite une planification et une exécution minutieuses. Les principaux points à retenir sont :

Utilisez les horodatages Unix pour une portabilité maximale
Toujours valider les données avant la migration
Testez minutieusement avec des volumes de données de type production
Documentez votre stratégie de gestion des fuseaux horaires
Avoir un plan de restauration vérifié

Le respect de ces pratiques garantira que votre migration d’horodatage est réussie et maintenable.

Assistant Timestamp SQL
Fonctions et requêtes timestamp SQL
Convertisseur de Timestamps par Lots
Convertissez plusieurs timestamps à la fois
Validateur de Timestamp
Validez des formats de timestamp, détectez les erreurs et assurez l'intégrité des données

Voir tous les outils

Tutoriels associés

Convertir des Timestamps en SQL
Guide complet des conversions de timestamps en SQL (MySQL, PostgreSQL, SQL Server, SQLite, Oracle) avec exemples et meilleures pratiques.
Timestamps en bases de données : guide de stockage, d’indexation et de conversion
Comment stocker et indexer les timestamps dans MySQL, PostgreSQL et SQLite. Choisissez le bon type, restez en UTC, évitez 2038 et convertissez en toute sécurité avec du SQL prêt à copier.
Référence des fonctions d'horodatage SQL
Référence rapide pour les opérations d'horodatage dans MySQL, PostgreSQL, SQLite, SQL Server et Oracle

Voir tous les tutoriels