Tutorial

データベース間のタイムスタンプの移行: 完全ガイド

はじめに

異なるデータベースシステム間でタイムスタンプデータを移行することは、アプリケーション開発やデータウェアハウスプロジェクトにおける共通の課題です。各データベースには、独自のタイムスタンプデータ型、関数、およびベストプラクティスがあります。この包括的なガイドでは、データを損失することなく、MySQL、PostgreSQL、SQLite、Oracle、SQL Server の間でタイムスタンプを移行する方法を説明します。

タイムスタンプの保存戦略を理解する

オプション 1: Unix タイムスタンプ (整数)

最も移植性の高い方法は、タイムスタンプを Unix エポック秒またはミリ秒の整数として保存することです。

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

言語別のコード例

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

パイソン

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

結論

データベース間でタイムスタンプを移行するには、慎重な計画と実行が必要です。重要なポイントは次のとおりです。

移植性を最大限に高めるために Unix タイムスタンプを使用します
移行前に必ずデータを検証してください
運用環境と同様のデータ量で徹底的にテストします
タイムゾーンの処理戦略を文書化します
検証済みのロールバック計画を立てる

これらのプラクティスに従うと、タイムスタンプの移行が成功し、保守可能になります。

データベース間のタイムスタンプの移行: 完全ガイド

はじめに

タイムスタンプの保存戦略を理解する

オプション 1: Unix タイムスタンプ (整数)

オプション 2: ISO 8601 文字列

オプション 3: ネイティブのタイムスタンプ タイプ

移行シナリオ

シナリオ 1: MySQL から PostgreSQL へ

シナリオ 2: Oracle から SQL Server へ

シナリオ 3: タイムゾーン間の変換

ベストプラクティス

1. ストレージには常に UTC を使用します

2. 移行前に検証する

3. ステージング テーブルを使用する

4. Null 値の処理

言語別のコード例

JavaScript/Node.js

パイソン

検証チェックリスト

よくある落とし穴

1. ミリ秒を無視する

2. タイムゾーンの設定ミス

3. 32 ビット整数のオーバーフロー

結論

オプション 3: ネイティブのタイムスタンプタイプ

3. ステージングテーブルを使用する