本文暂提供英文版，中文翻译正在完善中。
Introduction
Working with timestamps in SQL databases is a fundamental skill for developers and DBAs. This guide covers conversion functions across major database platforms, performance considerations, and common pitfalls.
Quick Reference
Conversion Functions by Database
MySQL
SQL1-- Convert Unix timestamp to DATETIME 2SELECT 3 id, 4 UNIX_TIMESTAMP(created_at) AS converted_date 5FROM events; 6 7-- Convert DATETIME to Unix timestamp 8SELECT 9 id, 10 UNIX_TIMESTAMP(created_at) AS timestamp_value 11FROM events; 12 13-- Current timestamp in Unix format 14SELECT UNIX_TIMESTAMP(NOW()) AS current_unix;
Important: MySQL TIMESTAMP has a 2038 problem (range: 1970-01-01 00:00:00 to 2038-01-19 03:14:07). Use DATETIME or BIGINT for dates beyond this range.
PostgreSQL
SQL1-- Convert Unix timestamp to TIMESTAMP WITH TIME ZONE 2SELECT 3 id, 4 to_timestamp(event_timestamp) AS converted_date 5FROM events; 6 7-- Convert timestamp to TIMESTAMPTZ with timezone 8SELECT 9 id, 10 event_timestamp AT TIME ZONE 'America/New_York' AS eastern_time 11FROM events; 12 13-- Current timestamp in Unix format 14SELECT EXTRACT(EPOCH FROM NOW()) AS current_unix;
Tip: PostgreSQL TIMESTAMP WITH TIME ZONE is timezone-aware. Use
AT TIME ZONEfor conversions with timezone support.
SQL Server (T-SQL)
SQL1-- Convert Unix timestamp to DATETIME2 (higher precision) 2SELECT 3 id, 4 DATEADD(s, 1970-01-01 00:00:00, UNIX_TIMESTAMP(timestamp_column)) AS converted_date 5FROM events; 6 7-- Convert DATETIME2 to Unix timestamp 8SELECT 9 id, 10 DATEDIFF(s, 1970-01-01 00:00:00, GETDATE()) AS unix_seconds 11FROM events; 12 13-- Current timestamp in Unix format 14SELECT DATEDIFF(s, 1970-01-01 00:00:00, GETUTCDATE()) AS current_unix;
Note: DATETIME2 provides fractional seconds (3 decimal places) for higher precision. Use DATEDIFF/DATEADD combination for accurate calculations.
SQLite
SQL1-- Convert Unix timestamp to DATETIME 2SELECT 3 id, 4 datetime(timestamp_column, 'unixepoch') AS converted_date 5FROM events; 6 7-- Current timestamp in Unix format 8SELECT strftime('%s', 'now') AS current_unix;
Note: SQLite uses Unix epoch by default. Use
strftimemodifiers for custom formats.
Oracle
SQL1-- Convert Unix timestamp to DATE 2SELECT 3 id, 4 TO_DATE('1970-01-01', 'YYYY-MM-DD') AS converted_date 5FROM events; 6 7-- Current timestamp 8SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS current_timestamp 9FROM DUAL;
Important: Oracle TO_DATE format is case-sensitive. Always use uppercase format specifiers (YYYY, MM, DD, etc.).
Practical Examples
Example 1: Convert and Display
SQL1-- MySQL: Convert and format in single query 2SELECT 3 id, 4 event_name, 5 DATE_FORMAT(UNIX_TIMESTAMP(created_at), '%Y-%m-%d %H:%i') AS formatted_date, 6 UNIX_TIMESTAMP(created_at) AS unix_value 7FROM events 8WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) 9ORDER BY created_at DESC;
Example 2: Filter by Date Range
SQL1-- PostgreSQL: Efficient date range filtering with timestamp 2SELECT 3 id, 4 event_name, 5 event_timestamp AT TIME ZONE 'UTC' AS utc_time, 6 TO_CHAR(event_timestamp AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI') AS formatted_date 7FROM events 8WHERE event_timestamp >= EXTRACT(EPOCH FROM (NOW() - INTERVAL '7 days')) 9 AND event_timestamp < EXTRACT(EPOCH FROM NOW()) 10ORDER BY event_timestamp DESC;
Example 3: Batch Conversion with Multiple Formats
SQL1-- SQL Server: Provide multiple timestamp formats in one query 2SELECT 3 id, 4 event_name, 5 event_timestamp, 6 CONVERT(VARCHAR, event_timestamp, 120) AS iso8601, -- Truncate to 120 chars 7 CONVERT(DATETIME2, event_timestamp) AS datetime2, 8 CONVERT(DATETIME, event_timestamp) AS readable_date, 9 YEAR(event_timestamp) AS year, 10 MONTH(event_timestamp) AS month, 11 DAY(event_timestamp) AS day 12FROM events 13WHERE event_timestamp IS NOT NULL;
Example 4: Time Difference Calculation
SQL1-- MySQL: Calculate time difference in seconds 2SELECT 3 id1, id2, 4 timestamp1, timestamp2, 5 TIMESTAMPDIFF(SECOND, timestamp2, timestamp1) AS diff_seconds, 6 TIMESTAMPDIFF(DAY, timestamp2, timestamp1) AS diff_days, 7 SEC_TO_TIME(TIMESTAMPDIFF(SECOND, timestamp2, timestamp1)) AS time_diff 8FROM events 9WHERE id IN (1, 2);
Performance Optimization
Index Strategies
Primary Index on Timestamp Column
Best Practice: Always create a primary index on timestamp columns for range queries and sorting operations.
SQL1-- MySQL: Primary index for range queries 2CREATE TABLE events ( 3 id INT PRIMARY KEY, 4 event_name VARCHAR(255), 5 event_timestamp TIMESTAMP, 6 INDEX idx_timestamp (event_timestamp) 7); 8 9-- PostgreSQL: Partial index for range queries 10CREATE TABLE events ( 11 id BIGSERIAL PRIMARY KEY, 12 event_name VARCHAR(255), 13 event_timestamp TIMESTAMPTZ, 14 INDEX idx_timestamp_range (event_timestamp) 15); 16 17-- SQL Server: Include timestamp in covering index 18CREATE NONCLUSTERED INDEX idx_event_timestamp 19ON events (event_timestamp DESC);
Functional Indexes
Best Practice: Create functional indexes on computed columns (year, month, day) for date grouping queries.
SQL1-- MySQL: Functional index for date-based grouping 2CREATE TABLE events ( 3 id INT PRIMARY KEY, 4 event_name VARCHAR(255), 5 event_timestamp TIMESTAMP, 6 INDEX idx_year (YEAR(event_timestamp)), 7 INDEX idx_month (MONTH(event_timestamp)), 8 INDEX idx_day (DAY(event_timestamp)) 9); 10 11-- PostgreSQL: Generated column for automatic year/month/day 12CREATE TABLE events ( 13 id BIGSERIAL PRIMARY KEY, 14 event_name VARCHAR(255), 15 event_timestamp TIMESTAMPTZ, 16 event_year INTEGER GENERATED ALWAYS AS (EXTRACT(YEAR FROM event_timestamp)) STORED, 17 event_month INTEGER GENERATED ALWAYS AS (EXTRACT(MONTH FROM event_timestamp)) STORED 18);
Query Optimization
Avoid Function Calls on Columns
Common Mistake: Using functions like YEAR(), MONTH(), DAY() on indexed columns prevents index usage.
SQL1-- BAD: Function call prevents index usage 2SELECT id, event_name 3FROM events 4WHERE YEAR(event_timestamp) = 2024 5AND MONTH(event_timestamp) = 6; 6 7-- GOOD: Compare literal values to use index 8SELECT id, event_name 9FROM events 10WHERE event_timestamp >= '2024-01-01 00:00:00' 11 AND event_timestamp < '2024-07-01 00:00:00';
Use SARGABLE-able Parameters
MySQL Tip: Starting from MySQL 8.0.18, using SARGABLE parameters in prepared statements allows index usage.
SQL1-- MySQL: Create prepared statement for parameterized queries 2PREPARE stmt FROM 'SELECT * FROM events WHERE event_timestamp >= ? AND event_timestamp < ?'; 3 4-- Execute with parameters (efficient) 5EXECUTE stmt USING @start_ts, @end_ts;
Limit Result Sets
Best Practice: Always use LIMIT with range queries to return only necessary data.
SQL1-- MySQL: Paginate large datasets 2SELECT id, event_name, event_timestamp 3FROM events 4WHERE event_timestamp >= UNIX_TIMESTAMP('2025-01-01') 5ORDER BY event_timestamp ASC 6LIMIT 1000; 7 8-- PostgreSQL: Use cursor-based pagination for large result sets 9DECLARE cursor CURSOR FOR 10 SELECT id, event_name, event_timestamp 11 FROM events 12 WHERE event_timestamp >= '2025-01-01 00:00:00 UTC' 13 ORDER BY event_timestamp ASC;
Common Pitfalls
Timezone Handling
MySQL Timezone Functions
Important: MySQL TIMESTAMP does NOT store timezone information. Use TIMESTAMP WITH TIME ZONE for timezone-aware storage, or use DATETIME with separate timezone column.
SQL1-- Convert to specific timezone 2SELECT 3 id, 4 event_name, 5 event_timestamp AT TIME ZONE 'America/Los_Angeles' AS la_time, 6 event_timestamp AT TIME ZONE 'America/New_York' AS ny_time 7FROM events; 8 9-- Get current time in specific timezone 10SELECT NOW() AS la_current, CONVERT_TZ(UTC, 'America/New_York') AS ny_current;
Alternative: Store UTC timestamp and a separate timezone column for multi-timezone applications.
Data Integrity
Ensure Timestamp Consistency
SQL1-- MySQL: Add CHECK constraint for reasonable timestamp range 2CREATE TABLE events ( 3 id INT PRIMARY KEY, 4 event_timestamp TIMESTAMP NOT NULL, 5 CHECK (event_timestamp BETWEEN 6 UNIX_TIMESTAMP('1970-01-01') AND 7 UNIX_TIMESTAMP('2038-01-19') 8 ) 9); 10 11-- PostgreSQL: Use EXCLUDE constraint to prevent invalid data 12CREATE TABLE events ( 13 id BIGSERIAL PRIMARY KEY, 14 event_timestamp TIMESTAMPTZ NOT NULL, 15 EXCLUDE ( 16 event_timestamp < EXTRACT(EPOCH FROM TIMESTAMP '1970-01-01') 17 OR event_timestamp > EXTRACT(EPOCH FROM TIMESTAMP '2038-01-19') 18 ) 19);
Migration Strategies
Incremental Migration
Strategy: For large tables, migrate in batches using timestamp-based WHERE clauses to avoid long-running transactions.
SQL1-- Process 1000 rows at a time, ordered by timestamp 2UPDATE events 3SET last_processed = 1 4WHERE event_timestamp < ( 5 SELECT event_timestamp 6 FROM events 7 WHERE last_processed = 0 8 ORDER BY event_timestamp ASC 9 LIMIT 1000 10); 11 12-- Continue until all rows processed 13-- Repeat until all events marked as processed
Advanced Patterns
Handling Epoch Timestamps
Database-Specific Patterns
MySQL Patterns
Unix Timestamp with Microsecond Precision
SQL1-- MySQL: Using BIGINT for millisecond timestamps 2CREATE TABLE high_precision_events ( 3 id BIGINT PRIMARY KEY, 4 event_timestamp BIGINT, -- Milliseconds since epoch 5 event_micros INT(3), -- Microseconds (0-999) 6 INDEX idx_timestamp (event_timestamp DESC) 7); 8 9-- Convert millisecond timestamp to human-readable format 10SELECT 11 id, 12 FROM_UNIXTIME(event_timestamp / 1000) AS seconds, 13 DATE_FORMAT(FROM_UNIXTIME(event_timestamp / 1000), '%Y-%m-%d %H:%i:%s') AS formatted 14FROM high_precision_events;
PostgreSQL Patterns
Using EXTRACT for Date Components
SQL1-- PostgreSQL: Extract date components for filtering 2SELECT 3 id, 4 event_name, 5 EXTRACT(YEAR FROM event_timestamp) AS year, 6 EXTRACT(MONTH FROM event_timestamp) AS month, 7 EXTRACT(DAY FROM event_timestamp) AS day, 8 DATE_TRUNC('day', event_timestamp) AS date_only 9FROM events 10WHERE event_name = 'Daily Backup';
Best Practices Summary
Performance Checklist
TEXT1✅ Use appropriate data types (TIMESTAMP vs DATETIME) 2✅ Create indexes on timestamp columns 3✅ Use SARGABLE-able parameters when possible 4✅ Limit result sets with pagination 5✅ Avoid function calls on indexed columns 6✅ Use WHERE clauses on timestamp for range queries 7✅ Consider generated columns for date filtering 8✅ Validate timestamp ranges on input 9✅ Handle timezones explicitly (don't rely on implicit conversion) 10✅ Use CHECK/EXCLUDE constraints for data integrity 11❌ Don't store both timestamp and datetime for same data 12❌ Don't use VARCHAR for timestamp columns 13❌ Don't convert timestamp to string for every query
For high-volume timestamp applications (>1M rows), proper indexing and query patterns can reduce query time by 50-80%.
Related Tools
- Timestamp Format Converter - Convert between multiple timestamp formats
- Unix Timestamp Converter - Convert Unix timestamps to dates
- Current Timestamp - Get current timestamp in multiple formats
- Timestamp Validator - Validate timestamp formats and ranges