Tutorials
Cross-Database Timestamp Migration: Complete Guide
Introduction
Migrating timestamp data between different database systems is a common challenge in application development and data warehouse projects. Each database has its own timestamp data types, functions, and best practices. This comprehensive guide will teach you how to migrate timestamps between MySQL, PostgreSQL, SQLite, Oracle, and SQL Server without data loss.
Understanding Timestamp Storage Strategies
Option 1: Unix Timestamp (Integer)
The most portable approach is storing timestamps as Unix epoch seconds or milliseconds as integers.
-- 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
);
Advantages:
- Universal format across all databases
- No timezone issues
- Easy to convert to any display format
- Efficient for indexing and comparison
Disadvantages:
- Not human-readable in raw form
- Requires conversion for debugging
Option 2: ISO 8601 String
Store timestamps as ISO 8601 formatted strings.
-- 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
);
Advantages:
- Human-readable
- Includes timezone information
- Standard format
Disadvantages:
- Larger storage size
- Slower comparisons
- Index efficiency varies
Option 3: Native Timestamp Types
Using each database's native timestamp types.
-- 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
);
Advantages:
- Best performance for date operations
- Built-in validation
- Rich function ecosystem
Disadvantages:
- Migration complexity
- Timezone handling varies
Migration Scenarios
Scenario 1: MySQL to PostgreSQL
Converting from MySQL's DATETIME to PostgreSQL's 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;
Scenario 2: Oracle to SQL Server
Migrating from Oracle's DATE to SQL Server's 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;
Scenario 3: Converting Between Timezones
When migrating between databases with different timezone requirements.
-- 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;
Best Practices
1. Always Use UTC for Storage
-- 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. Validate Before Migration
-- 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 Staging Tables
-- 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. Handle Null Values
-- 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;
Code Examples by Language
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()
Validation Checklist
Before going to production:
- Data Type Compatibility: Verify target column can hold all source values
- Timezone Handling: Confirm all timestamps are in UTC or document timezone policy
- Range Validation: Check for timestamps before 1970 or after 2038
- NULL Handling: Decide how to handle NULL/missing timestamps
- Performance: Test migration on representative data volume
- Rollback Plan: Have a verified rollback procedure
Common Pitfalls
1. Ignoring Milliseconds
-- 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. Timezone Misconfiguration
-- 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-bit Integer Overflow
-- Check for timestamps that will overflow 32-bit
SELECT * FROM events
WHERE created_at > '2038-01-19 03:14:07'::timestamp;
Conclusion
Migrating timestamps between databases requires careful planning and execution. The key takeaways are:
- Use Unix timestamps for maximum portability
- Always validate data before migration
- Test thoroughly with production-like data volumes
- Document your timezone handling strategy
- Have a verified rollback plan
Following these practices will ensure your timestamp migration is successful and maintainable.