Tutoriales

Migración de marcas de tiempo entre bases de datos: guía completa

Introducción

La migración de datos de marcas de tiempo entre diferentes sistemas de bases de datos es un desafío común en los proyectos de desarrollo de aplicaciones y almacenamiento de datos. Cada base de datos tiene sus propios tipos de datos de marca de tiempo, funciones y mejores prácticas. Esta guía completa le enseñará cómo migrar marcas de tiempo entre MySQL, PostgreSQL, SQLite, Oracle y SQL Server sin pérdida de datos.

Comprender las estrategias de almacenamiento de marcas de tiempo

Opción 1: Marca de tiempo de Unix (entero)

El enfoque más portátil es almacenar marcas de tiempo como segundos de época de Unix o milisegundos como números enteros.

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

Ventajas:

Formato universal en todas las bases de datos.
No hay problemas de zona horaria
Fácil de convertir a cualquier formato de visualización
Eficiente para indexación y comparación.

Desventajas:

No legible por humanos en formato crudo
Requiere conversión para depurar

Opción 2: Cadena ISO 8601

Almacene marcas de tiempo como cadenas con formato 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
);

Ventajas:

Legible por humanos
Incluye información de zona horaria.
Formato estándar

Desventajas:

Mayor tamaño de almacenamiento
Comparaciones más lentas
La eficiencia del índice varía

Opción 3: tipos de marcas de tiempo nativas

Utilizando los tipos de marcas de tiempo nativas de cada base de datos.

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

Ventajas:

Mejor rendimiento para operaciones de fechas.
Validación incorporada
Ecosistema rico en funciones

Desventajas:

Complejidad de la migración
El manejo de la zona horaria varía

Escenarios de migración

Escenario 1: MySQL a PostgreSQL

Conversión de DATETIME de MySQL a 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;

Escenario 2: Oracle a SQL Server

Migrando de DATE de Oracle a 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;

Escenario 3: Conversión entre zonas horarias

Al migrar entre bases de datos con diferentes requisitos de zona horaria.

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

Mejores prácticas

1. Utilice siempre UTC para el almacenamiento

-- 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. Validar antes de la migración

-- 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. Utilice tablas de preparación

-- 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. Manejar valores nulos

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

Ejemplos de código por idioma

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

Pitón

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

Lista de verificación de validación

Antes de pasar a producción:

[] Compatibilidad de tipos de datos: Verifique que la columna de destino pueda contener todos los valores de origen
[] Manejo de zona horaria: confirme que todas las marcas de tiempo estén en UTC o documente la política de zona horaria
[] Validación de rango: verifique las marcas de tiempo antes de 1970 o después de 2038
[] Manejo de NULL: decida cómo manejar las marcas de tiempo NULL/faltantes
[] Rendimiento: pruebe la migración en un volumen de datos representativo
[] Plan de reversión: tenga un procedimiento de reversión verificado

Errores comunes

1. Ignorar milisegundos

-- 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. Configuración incorrecta de la zona horaria

-- 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. Desbordamiento de enteros de 32 bits

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

Conclusión

La migración de marcas de tiempo entre bases de datos requiere una planificación y ejecución cuidadosas. Las conclusiones clave son:

Utilice marcas de tiempo de Unix para máxima portabilidad
Valide siempre los datos antes de la migración
Pruebe minuciosamente con volúmenes de datos similares a los de producción
Documente su estrategia de manejo de zona horaria
Tener un plan de reversión verificado

Seguir estas prácticas garantizará que la migración de su marca de tiempo sea exitosa y mantenible.

Asistente de Timestamp SQL
Funciones y consultas de timestamp SQL
Convertidor de Timestamps por Lotes
Convierte múltiples timestamps a la vez
Validador de Timestamp
Valida formatos de timestamp, detecta errores y asegura la integridad de datos

Ver todas las herramientas

Tutoriales relacionados

Cómo Convertir Timestamps en SQL
Guía completa de conversiones de timestamps en SQL (MySQL, PostgreSQL, SQL Server, SQLite, Oracle) con ejemplos y mejores prácticas.
Timestamps en bases de datos: guía de almacenamiento, indexación y conversión
Cómo almacenar e indexar timestamps en MySQL, PostgreSQL y SQLite. Elige el tipo correcto, usa UTC, evita 2038 y convierte de forma segura con SQL listo para copiar.
Referencia de funciones de marca de tiempo SQL
Referencia rápida para operaciones de marca de tiempo en MySQL, PostgreSQL, SQLite, SQL Server y Oracle

Ver todos los tutoriales