MakeTimestamp logo
MakeTimestamp

Tutorial

데이터베이스 간 타임스탬프 마이그레이션: 전체 가이드

소개

서로 다른 데이터베이스 시스템 간에 타임스탬프 데이터를 마이그레이션하는 것은 애플리케이션 개발 및 데이터 웨어하우스 프로젝트에서 일반적인 과제입니다. 각 데이터베이스에는 고유한 타임스탬프 데이터 유형, 기능 및 모범 사례가 있습니다. 이 종합 가이드는 데이터 손실 없이 MySQL, PostgreSQL, SQLite, Oracle 및 SQL Server 간에 타임스탬프를 마이그레이션하는 방법을 알려줍니다.

타임스탬프 저장 전략 이해

옵션 1: Unix 타임스탬프(정수)

가장 이식성이 뛰어난 접근 방식은 타임스탬프를 Unix epoch 초 또는 밀리초를 정수로 저장하는 것입니다.

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

장점:

  • 모든 데이터베이스에 걸친 범용 형식
  • 시간대 문제 없음
  • 어떤 디스플레이 형식으로도 쉽게 변환 가능
  • 색인화 및 비교에 효율적입니다.

단점:

  • 원시 형식에서는 사람이 읽을 수 없음
  • 디버깅을 위해서는 변환이 필요합니다.

옵션 2: ISO 8601 문자열

타임스탬프를 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
);

장점:

  • 사람이 읽을 수 있는
  • 시간대 정보를 포함합니다.
  • 표준 형식

단점:

  • 더 큰 저장 공간
  • 느린 비교
  • 지수 효율성은 다양함

옵션 3: 기본 타임스탬프 유형

각 데이터베이스의 기본 타임스탬프 유형을 사용합니다.

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

장점:

  • 날짜 연산에 있어서 최고의 성능
  • 내장된 검증
  • 풍부한 기능 생태계

단점:

  • 마이그레이션 복잡성
  • 시간대 처리가 다양함

마이그레이션 시나리오

시나리오 1: MySQL에서 PostgreSQL로

MySQL의 DATETIME을 PostgreSQL의 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;

시나리오 2: Oracle에서 SQL Server로

Oracle의 DATE에서 SQL Server의 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;

시나리오 3: 시간대 간 변환

시간대 요구사항이 서로 다른 데이터베이스 간에 마이그레이션하는 경우.

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

모범 사례

1. 저장 시 항상 UTC를 사용하세요.

-- 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. 마이그레이션 전 유효성 검사

-- 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. 준비 테이블 사용

-- 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. Null 값 처리

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

언어별 코드 예

자바스크립트/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]
        );
    }
}

파이썬

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

검증 체크리스트

생산에 들어가기 전에:

  • 데이터 유형 호환성: 대상 열이 모든 소스 값을 보유할 수 있는지 확인
  • 시간대 처리: 모든 타임스탬프가 UTC 또는 문서 시간대 정책인지 확인하세요.
  • 범위 검증: 1970년 이전 또는 2038년 이후의 타임스탬프를 확인하세요.
  • NULL 처리: NULL/누락된 타임스탬프를 처리하는 방법 결정
  • 성능: 대표 데이터 볼륨에 대한 마이그레이션 테스트
  • 롤백 계획: 검증된 롤백 절차를 갖습니다.

일반적인 함정

1. 밀리초 무시

-- 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. 시간대 구성 오류

-- 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비트 정수 오버플로

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

결론

데이터베이스 간에 타임스탬프를 마이그레이션하려면 신중한 계획과 실행이 필요합니다. 주요 내용은 다음과 같습니다.

  1. 이식성을 극대화하려면 Unix 타임스탬프를 사용하세요
  2. 마이그레이션 전에 항상 데이터 유효성을 검사하세요
  3. 프로덕션 수준의 데이터 볼륨으로 철저하게 테스트
  4. 시간대 처리 전략을 문서화하세요
  5. 검증된 롤백 계획을 가지고 있습니다

이러한 방법을 따르면 타임스탬프 마이그레이션이 성공적이고 유지 관리 가능해집니다.