How to Query SQL Databases With Unix Timestamps (PostgreSQL, MySQL, SQLite)
Learn the exact SQL syntax to convert between Unix epoch timestamps and human dates across PostgreSQL, MySQL, and SQLite databases.
Each SQL dialect provides distinct functions for converting epoch timestamps: PostgreSQL uses to_timestamp(epoch), MySQL uses FROM_UNIXTIME(epoch) and UNIX_TIMESTAMP(), while SQLite uses strftime('%s', 'now') and datetime(epoch, 'unixepoch').
Solve this problem now with our free Epoch / Unix Timestamp Converter
Try it instantly in your browser — zero tracking, 100% client-side privacy.
Specification & Architecture Reference
| Database | Convert Epoch to Human Timestamp | Convert Current Time to Epoch Seconds |
|---|---|---|
| PostgreSQL | to_timestamp(created_at_epoch) | EXTRACT(epoch FROM NOW())::bigint |
| MySQL / MariaDB | FROM_UNIXTIME(created_at_epoch) | UNIX_TIMESTAMP(NOW()) |
| SQLite | datetime(created_at_epoch, 'unixepoch') | strftime('%s', 'now') |
| SQL Server (T-SQL) | DATEADD(s, created_at_epoch, '19700101') | DATEDIFF(s, '19700101', GETUTCDATE()) |
Converting Timestamps in SQL
Databases often store event times as integers for fast indexing, but generating reports requires converting these values into human-readable dates.
1. PostgreSQL In PostgreSQL, `to_timestamp()` accepts seconds and returns a `TIMESTAMPTZ`:
-- Convert seconds to timestamp-- Query records created in the last 24 hours SELECT id, email FROM users WHERE created_at_epoch >= EXTRACT(epoch FROM (NOW() - INTERVAL '24 hours')); ``` *Note: If your database stores milliseconds, divide by 1000.0 first:* ```sql SELECT to_timestamp(created_at_ms / 1000.0); ```
2. MySQL / MariaDB MySQL provides native `FROM_UNIXTIME` and `UNIX_TIMESTAMP`:
-- Convert epoch seconds to date string-- Convert human date to epoch seconds SELECT UNIX_TIMESTAMP('2026-10-21 05:00:00'); ```
3. SQLite SQLite stores dates as text, real, or integer values:
-- Convert epoch seconds to UTC date string-- Convert to local device time SELECT datetime(1729486800, 'unixepoch', 'localtime'); ```
Indexing Tip Avoid wrapping indexed columns in functions within your `WHERE` clause: ```sql -- ❌ Slow: Prevents index usage on created_at_epoch WHERE FROM_UNIXTIME(created_at_epoch) > '2026-01-01'
-- ✅ Fast: Uses the B-tree index directly WHERE created_at_epoch > 1767225600 ```
Convert your SQL timestamps instantly with Mickytool's **Epoch / Unix Timestamp Converter**.
Solve this problem now with our free Epoch / Unix Timestamp Converter
Execute this workflow immediately without saving data on remote servers.
Frequently Asked Questions
Why does wrapping an indexed column in a function slow queries down?
When a function is called on a column in the WHERE clause, the database must evaluate the function for every single row in the table, disabling index scans unless a functional index was created.
How do I extract milliseconds in PostgreSQL?
Use (EXTRACT(epoch FROM NOW()) * 1000)::bigint to get current epoch milliseconds.