DB migration helper

Draft timestamp-prefixed migration filenames and SQL bodies. Adapt to your migration runner (Flyway, Liquibase, Prisma, etc.).

Version prefix

Short name

20250422000000_add_users_table.sql

SQL

Related Guides & Tutorials

// developers also read

Deep Dive

12 min

Unix Timestamp Precision Guide

Avoid precision loss when migrating timestamp columns between database systems.

Read guide

Tutorial

9 min

Elasticsearch Timestamp Indexing

Migrate time-series data to Elasticsearch with correct timestamp mapping and indexing.

Read guide

Knowledge Base

6 min

GraphQL Timestamps

Expose migrated timestamp columns correctly through GraphQL APIs after database changes.

Read guide

Terminal Equivalent

Inspect and migrate epoch columns with psql, mysql, sqlite3, and idempotent SQL patterns.

bash

# PostgreSQL: convert stored integers to timestamptz
psql -c "SELECT to_timestamp(created_at) FROM users LIMIT 5;"

# MySQL: convert Unix timestamps
mysql -e "SELECT FROM_UNIXTIME(created_at) FROM users LIMIT 5;"

# SQLite: timestamps are stored as integers
sqlite3 app.db "SELECT datetime(created_at, 'unixepoch') \
  FROM users LIMIT 5;"

# PostgreSQL: migrate integer column to timestamptz
psql << 'EOF'
ALTER TABLE events ADD COLUMN created_ts TIMESTAMPTZ;
UPDATE events SET created_ts = to_timestamp(created_at);
ALTER TABLE events DROP COLUMN created_at;
ALTER TABLE events RENAME COLUMN created_ts TO created_at;
EOF

Language Quick Reference

python

# Python SQLAlchemy migration
from sqlalchemy import text
with engine.connect() as conn:
    conn.execute(text(
        "UPDATE events SET created_at = "
        "to_timestamp(created_at_unix)"
    ))

How It Works

Database timestamp migrations require care because data loss is irreversible. Always add a new column rather than modifying existing data in place. Verify row counts match before dropping the old column. Consider timezone: PostgreSQL's TIMESTAMPTZ stores UTC and converts on read. MySQL's TIMESTAMP also stores UTC but DATETIME does not. SQLite has no native timestamp type — integers or ISO strings are convention only.