Unix Timestamp in SQL
Databases differ: PostgreSQL uses to_timestamp(ts) and extract(epoch from now()), while MySQL uses FROM_UNIXTIME(ts) and UNIX_TIMESTAMP(). Both convert between epoch integers and native timestamp types.
Get & convert epoch time in SQL
-- PostgreSQL SELECT to_timestamp(1700000000); -- 2023-11-14T22:13:20.000Z -- MySQL SELECT FROM_UNIXTIME(1700000000);
Gotcha: Watch the session time zone — PostgreSQL's to_timestamp returns timestamptz, and display depends on the client's TimeZone setting.
Frequently asked questions
- How do I get the current Unix timestamp in SQL?
- PostgreSQL: SELECT extract(epoch from now())::bigint; MySQL: SELECT UNIX_TIMESTAMP();
- How do I convert a Unix timestamp in SQL?
- PostgreSQL: to_timestamp(ts); MySQL: FROM_UNIXTIME(ts).