MakeTimestamp logo
MakeTimestamp

Tutorial

Datenbankübergreifende Zeitstempelmigration: Vollständiger Leitfaden

Einführung

Die Migration von Zeitstempeldaten zwischen verschiedenen Datenbanksystemen ist eine häufige Herausforderung bei Anwendungsentwicklungs- und Data-Warehouse-Projekten. Jede Datenbank verfügt über eigene Zeitstempel-Datentypen, Funktionen und Best Practices. In diesem umfassenden Leitfaden erfahren Sie, wie Sie Zeitstempel zwischen MySQL, PostgreSQL, SQLite, Oracle und SQL Server ohne Datenverlust migrieren.

Strategien zur Speicherung von Zeitstempeln verstehen

Option 1: Unix-Zeitstempel (Ganzzahl)

Der portierbarste Ansatz besteht darin, Zeitstempel als Unix-Epochensekunden oder Millisekunden als Ganzzahlen zu speichern.

-- 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
);

Vorteile:

  • Universelles Format für alle Datenbanken
  • Keine Zeitzonenprobleme
  • Einfache Konvertierung in jedes Anzeigeformat
  • Effizient für Indizierung und Vergleich

Nachteile:

  • Im Rohformat nicht für Menschen lesbar
  • Erfordert Konvertierung zum Debuggen

Option 2: ISO 8601-Zeichenfolge

Speichern Sie Zeitstempel als ISO 8601-formatierte Zeichenfolgen.

-- 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
);

Vorteile:

  • Für Menschen lesbar
  • Enthält Zeitzoneninformationen
  • Standardformat

Nachteile:

  • Größere Speichergröße
  • Langsamere Vergleiche
  • Die Indexeffizienz variiert

Option 3: Native Zeitstempeltypen

Verwendung der nativen Zeitstempeltypen jeder Datenbank.

-- 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
);

Vorteile:

  • Beste Leistung für Datumsoperationen
  • Integrierte Validierung
  • Ökosystem mit reichhaltigen Funktionen

Nachteile:

  • Migrationskomplexität
  • Die Handhabung der Zeitzone variiert

Migrationsszenarien

Szenario 1: MySQL zu PostgreSQL

Konvertierung von MySQLs DATETIME in PostgreSQLs TIMESTAMPTZ.

-- 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;

Szenario 2: Oracle zu SQL Server

Migration von Oracles DATE zu SQL Servers DATETIME2.

-- 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;

Szenario 3: Konvertierung zwischen Zeitzonen

Bei der Migration zwischen Datenbanken mit unterschiedlichen Zeitzonenanforderungen.

-- 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;

Best Practices

1. Verwenden Sie immer UTC für die Speicherung

-- 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. Vor der Migration validieren

-- 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. Verwenden Sie Staging-Tabellen

-- 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. Behandeln Sie Nullwerte

-- 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;

Codebeispiele nach Sprache

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()

Validierungscheckliste

Bevor Sie mit der Produktion beginnen:

  • Datentypkompatibilität: Überprüfen Sie, ob die Zielspalte alle Quellwerte enthalten kann
  • Zeitzonenbehandlung: Bestätigen Sie, dass alle Zeitstempel in UTC oder in der Zeitzonenrichtlinie des Dokuments vorliegen
  • Bereichsvalidierung: Auf Zeitstempel vor 1970 oder nach 2038 prüfen
  • NULL-Behandlung: Entscheiden Sie, wie mit NULL/fehlenden Zeitstempeln umgegangen werden soll
  • Leistung: Testmigration auf repräsentativem Datenvolumen
  • Rollback-Plan: Verifiziertes Rollback-Verfahren

Häufige Fallstricke

1. Millisekunden ignorieren

-- 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. Zeitzonen-Fehlkonfiguration

-- 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. 32-Bit-Ganzzahlüberlauf

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

Fazit

Die Migration von Zeitstempeln zwischen Datenbanken erfordert eine sorgfältige Planung und Ausführung. Die wichtigsten Erkenntnisse sind:

  1. Verwenden Sie Unix-Zeitstempel für maximale Portabilität
  2. Daten vor der Migration immer validieren
  3. Gründlich mit produktionsähnlichen Datenmengen testen
  4. Dokumentieren Sie Ihre Zeitzonen-Handhabungsstrategie
  5. Verfügen Sie über einen verifizierten Rollback-Plan

Wenn Sie diese Vorgehensweisen befolgen, stellen Sie sicher, dass Ihre Zeitstempelmigration erfolgreich und wartbar ist.