SQL Conversion Cheat Sheet

Every database handles Unix timestamps slightly differently. Here is the definitive copy-paste guide for converting seconds to dates.

Database	Function / Syntax
PostgreSQL	`to_timestamp(1704067200)` Returns TIMESTAMPTZ (UTC by default)
MySQL / MariaDB	`FROM_UNIXTIME(1704067200)` Returns DATETIME or VARCHAR depending on context
SQL Server	`DATEADD(s, 1704067200, '1970-01-01')` Classic approach. Returns DATETIME.
SQLite	`datetime(1704067200, 'unixepoch')` Returns string 'YYYY-MM-DD HH:MM:SS'
BigQuery	`TIMESTAMP_SECONDS(1704067200)`
Snowflake	`TO_TIMESTAMP(1704067200)`
Oracle	TIMESTAMP '1970-01-01 00:00:00' + NUMTODSINTERVAL(1704067200, 'SECOND')

Handling Milliseconds

If your column ts is in milliseconds, divide by 1000 (and cast to float if needed to preserve precision).

PostgreSQL Example

-- ✅ Divide by 1000.0 to keep microseconds
SELECT to_timestamp(1704067200500 / 1000.0);

SQL Server Example

-- ✅ Use 'ms' instead of 's'
SELECT DATEADD(ms, 1704067200500 % 1000, 
    DATEADD(s, 1704067200500 / 1000, '1970-01-01'))
-- (Slightly complex because DATEADD(ms) can overflow with large numbers)

Frequently Asked Questions

How do I handle milliseconds in SQL?

Most standard SQL functions expect seconds. If you have milliseconds, divide by 1000 first (e.g., to_timestamp(ts / 1000)).

What is the return type of these functions?

They typically return a TIMESTAMP or DATETIME type. In Postgres, `to_timestamp` returns `TIMESTAMPTZ` (with timezone).

Does SQL Server have a native Unix timestamp function?

Not directly in older versions. You typically use DATEADD(s, your_timestamp, "1970-01-01"). Newer versions are adding better support.