MakeTimestamp logo
MakeTimestamp

Tutorial

Migração de carimbo de data/hora entre bancos de dados: guia completo

Introdução

A migração de dados de carimbo de data/hora entre diferentes sistemas de banco de dados é um desafio comum no desenvolvimento de aplicativos e em projetos de data warehouse. Cada banco de dados tem seus próprios tipos de dados de carimbo de data/hora, funções e práticas recomendadas. Este guia abrangente ensinará como migrar carimbos de data/hora entre MySQL, PostgreSQL, SQLite, Oracle e SQL Server sem perda de dados.

Noções básicas sobre estratégias de armazenamento de carimbo de data/hora

Opção 1: carimbo de data/hora Unix (inteiro)

A abordagem mais portátil é armazenar carimbos de data/hora como segundos da época Unix ou milissegundos como números inteiros.

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

Vantagens:

  • Formato universal em todos os bancos de dados
  • Sem problemas de fuso horário
  • Fácil de converter para qualquer formato de exibição
  • Eficiente para indexação e comparação

Desvantagens:

  • Não é legível por humanos na forma bruta
  • Requer conversão para depuração

Opção 2: Cadeia ISO 8601

Armazene carimbos de data/hora como strings formatadas em 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
);

Vantagens:

  • Legível por humanos
  • Inclui informações de fuso horário
  • Formato padrão

Desvantagens:

  • Maior tamanho de armazenamento
  • Comparações mais lentas
  • A eficiência do índice varia

Opção 3: Tipos de carimbo de data/hora nativos

Usando os tipos de carimbo de data/hora nativos de cada banco de dados.

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

Vantagens:

  • Melhor desempenho para operações de data
  • Validação integrada
  • Rico ecossistema de funções

Desvantagens:

  • Complexidade da migração
  • O tratamento do fuso horário varia

Cenários de migração

Cenário 1: MySQL para PostgreSQL

Convertendo de DATETIME do MySQL para TIMESTAMPTZ do 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;

Cenário 2: Oracle para SQL Server

Migrando do DATE do Oracle para o DATETIME2 do 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;

Cenário 3: conversão entre fusos horários

Ao migrar entre bancos de dados com requisitos de fuso horário diferentes.

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

Melhores práticas

1. Sempre use UTC para armazenamento

-- 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 da migração

-- 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. Use tabelas de teste

-- 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. Lidar com 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;

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

###Píton

# 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 verificação de validação

Antes de ir para a produção:

  • Compatibilidade de tipo de dados: verifique se a coluna de destino pode conter todos os valores de origem
  • Manipulação de fuso horário: confirme se todos os carimbos de data e hora estão em UTC ou documente a política de fuso horário
  • Validação de intervalo: verifique se há carimbos de data/hora antes de 1970 ou depois de 2038
  • Tratamento NULL: Decida como lidar com carimbos de data/hora NULL/ausentes
  • Desempenho: teste a migração em volume de dados representativo
  • [] Plano de reversão: tenha um procedimento de reversão verificado

Armadilhas Comuns

1. Ignorando milissegundos

-- 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. Configuração incorreta de fuso horário

-- 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. Estouro de número inteiro de 32 bits

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

Conclusão

A migração de carimbos de data/hora entre bancos de dados requer planejamento e execução cuidadosos. As principais conclusões são:

  1. Use carimbos de data/hora Unix para máxima portabilidade
  2. Sempre valide os dados antes da migração
  3. Teste minuciosamente com volumes de dados semelhantes aos de produção
  4. Documente sua estratégia de tratamento de fuso horário
  5. Tenha um plano de reversão verificado

Seguir essas práticas garantirá que a migração do carimbo de data/hora seja bem-sucedida e possa ser mantida.