教程

跨数据库时间戳迁移：完整指南

简介

在不同数据库系统之间迁移时间戳数据是应用程序开发和数据仓库项目中的常见挑战。每个数据库都有自己的时间戳数据类型、功能和最佳实践。这份综合指南将教您如何在 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. 处理空值

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

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

验证清单

投入生产前：

数据类型兼容性：验证目标列可以容纳所有源值
时区处理：确认所有时间戳均采用 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 时间戳以获得最大的可移植性
迁移前始终验证数据
使用类似生产的数据量进行彻底测试
记录您的时区处理策略
拥有经过验证的回滚计划

遵循这些实践将确保您的时间戳迁移成功且可维护。