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.

Copy
SQL
1-- MySQL
2CREATE TABLE events (
3    id INT PRIMARY KEY,
4    created_at BIGINT NOT NULL  -- Unix timestamp in seconds
5);
6
7-- PostgreSQL  
8CREATE TABLE events (
9    id INT PRIMARY KEY,
10    created_at BIGINT NOT NULL
11);
12
13-- SQLite
14CREATE TABLE events (
15    id INTEGER PRIMARY KEY,
16    created_at INTEGER NOT NULL
17);

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.

Copy
SQL
1-- MySQL
2CREATE TABLE events (
3    id INT PRIMARY KEY,
4    created_at VARCHAR(26) NOT NULL  -- '2025-01-15T10:30:00.000Z'
5);
6
7-- PostgreSQL
8CREATE TABLE events (
9    id INT PRIMARY KEY,
10    created_at TIMESTAMPTZ NOT NULL
11);

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.

Copy
SQL
1-- MySQL
2CREATE TABLE events (
3    created_at DATETIME
4);
5
6-- PostgreSQL
7CREATE TABLE events (
8    created_at TIMESTAMP WITH TIME ZONE
9);
10
11-- SQL Server
12CREATE TABLE events (
13    created_at DATATIME2
14);

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.

Copy
SQL
1-- Source (MySQL)
2SELECT id, created_at FROM events;
3
4-- Migration Script (PostgreSQL)
5-- Option A: Using TO_TIMESTAMP()
6INSERT INTO events (id, created_at)
7SELECT id, TO_TIMESTAMP(created_at, 'YYYY-MM-DD HH24:MI:SS')
8FROM events_source;
9
10-- Option B: Using Unix timestamp (recommended)
11-- First, convert MySQL DATETIME to Unix timestamp
12SELECT UNIX_TIMESTAMP(created_at) FROM events;
13
14-- Then insert into PostgreSQL
15INSERT INTO events (id, created_at)
16SELECT id, TO_TIMESTAMP(unix_ts)
17FROM events_source;

Scenario 2: Oracle to SQL Server

Migrating from Oracle's DATE to SQL Server's DATETIME2.

Copy
SQL
1-- Source (Oracle)
2SELECT hire_date FROM employees;
3
4-- Migration Script (SQL Server)
5-- Option A: Direct conversion
6INSERT INTO employees (hire_date)
7SELECT CAST(hire_date AS DATETIME2)
8FROM employees_source;
9
10-- Option B: Using Unix epoch (preferred)
11-- Oracle: Get epoch seconds
12SELECT (hire_date - DATE '1970-01-01') * 86400 AS epoch_seconds
13FROM employees;
14
15-- SQL Server: Convert epoch to datetime
16INSERT INTO employees (hire_date)
17SELECT DATEADD(SECOND, epoch_seconds, '1970-01-01')
18FROM employees_source;

Scenario 3: Converting Between Timezones

When migrating between databases with different timezone requirements.

Copy
SQL
1-- PostgreSQL: Convert UTC to specific timezone
2INSERT INTO events (id, created_at)
3SELECT id, created_at AT TIME ZONE 'America/New_York'
4FROM events_source;
5
6-- MySQL: Convert using CONVERT_TZ
7INSERT INTO events (id, created_at)
8SELECT id, CONVERT_TZ(created_at, '+00:00', '-05:00')
9FROM events_source;
10
11-- SQL Server: Convert using AT TIME ZONE
12INSERT INTO events (id, created_at)
13SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'
14FROM events_source;

Best Practices

1. Always Use UTC for Storage

Copy
SQL
1-- Store all timestamps in UTC
2ALTER TABLE events 
3ADD COLUMN created_at_utc TIMESTAMP WITH TIME ZONE;
4
5UPDATE events 
6SET created_at_utc = created_at AT TIME ZONE 'UTC';
7
8-- Drop the old column after verification
9ALTER TABLE events DROP COLUMN created_at;

2. Validate Before Migration

Copy
SQL
1-- Check for invalid timestamps
2SELECT COUNT(*) as invalid_count
3FROM events_source
4WHERE created_at IS NULL 
5   OR created_at < '1970-01-01'::timestamp
6   OR created_at > '2038-01-19'::timestamp;  -- 32-bit overflow

3. Use Staging Tables

Copy
SQL
1-- Create staging table
2CREATE TABLE events_staging (LIKE events);
3
4-- Load data
5INSERT INTO events_staging 
6SELECT * FROM events_source;
7
8-- Validate and clean
9UPDATE events_staging 
10SET created_at = NOW() 
11WHERE created_at IS NULL;
12
13-- Verify row counts
14SELECT 
15    (SELECT COUNT(*) FROM events_source) as source_count,
16    (SELECT COUNT(*) FROM events_staging) as staging_count,
17    (SELECT COUNT(*) FROM events) as target_count;

4. Handle Null Values

Copy
SQL
1-- PostgreSQL
2INSERT INTO events (id, created_at)
3SELECT id, COALESCE(created_at, NOW())
4FROM events_source;
5
6-- MySQL
7INSERT INTO events (id, created_at)
8SELECT id, IFNULL(created_at, NOW())
9FROM events_source;
10
11-- SQL Server
12INSERT INTO events (id, created_at)
13SELECT id, ISNULL(created_at, GETDATE())
14FROM events_source;

Code Examples by Language

JavaScript/Node.js

Copy
JAVASCRIPT
1// Migrate timestamps using Node.js
2const mysql = require('mysql2/promise');
3const { Pool } = require('pg');
4
5async function migrateTimestamps() {
6    const mysqlPool = await mysql.createPool({
7        host: 'mysql-source',
8        database: 'source_db'
9    });
10    
11    const pgPool = new Pool({
12        host: 'postgres-target',
13        database: 'target_db'
14    });
15    
16    // Get all records
17    const [rows] = await mysqlPool.query('SELECT * FROM events');
18    
19    // Transform and insert
20    for (const row of rows) {
21        const unixTimestamp = Math.floor(row.created_at.getTime() / 1000);
22        await pgPool.query(
23            'INSERT INTO events (id, created_at) VALUES ($1, to_timestamp($2))',
24            [row.id, unixTimestamp]
25        );
26    }
27}

Python

Copy
PYTHON
1# Migrate timestamps using Python
2import pymysql
3import psycopg2
4from datetime import datetime
5
6def migrate_timestamps():
7    # Source connection
8    mysql_conn = pymysql.connect(
9        host='mysql-source',
10        database='source_db'
11    )
12    
13    # Target connection
14    pg_conn = psycopg2.connect(
15        host='postgres-target',
16        database='target_db'
17    )
18    
19    with mysql_conn.cursor() as cursor:
20        cursor.execute('SELECT id, created_at FROM events')
21        rows = cursor.fetchall()
22        
23        with pg_conn.cursor() as pg_cursor:
24            for row in rows:
25                # Convert MySQL datetime to Unix timestamp
26                unix_ts = int(row[1].timestamp())
27                pg_cursor.execute(
28                    'INSERT INTO events (id, created_at) VALUES (%s, to_timestamp(%s))',
29                    (row[0], unix_ts)
30                )
31        
32        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

Copy
SQL
1-- Wrong: Losing millisecond precision
2INSERT INTO target SELECT created_at FROM source;
3
4-- Correct: Preserve milliseconds
5INSERT INTO target 
6SELECT created_at AT TIME ZONE 'UTC' FROM source;

2. Timezone Misconfiguration

Copy
SQL
1-- Wrong: Assuming local time
2INSERT INTO target (created_at)
3SELECT created_at FROM source;
4
5-- Correct: Explicit UTC conversion
6INSERT INTO target (created_at)
7SELECT COALESCE(
8    created_at AT TIME ZONE 'UTC',
9    NOW()
10) FROM source;

3. 32-bit Integer Overflow

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

Conclusion

Migrating timestamps between databases requires careful planning and execution. The key takeaways are:

1. Use Unix timestamps for maximum portability
2. Always validate data before migration
3. Test thoroughly with production-like data volumes
4. Document your timezone handling strategy
5. Have a verified rollback plan

Following these practices will ensure your timestamp migration is successful and maintainable.