How to get the current Unix timestamp in SQL

gistfile1.md

Get the current Unix timestamp (seconds from 1970-01-01T00:00:00Z) in SQL.

• MySQL: UNIX_TIMESTAMP()
• PostgreSQL: CAST(EXTRACT(epoch FROM NOW()) AS INT)
• MS SQL: DATEDIFF(s, '1970-01-01', GETUTCDATE())
• Oracle: (CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) - DATE'1970-01-01') * 86400

CAST(EXTRACT(epoch FROM NOW()) AS BIGINT) expression is wrong. NOW() or CURRENT_TIMESTAMP() will give you TIMESTAMP type. When epoch is extracted from that it gives you Unix timestamp in your DB's (or client) time zone, not in UTC! Seems like we must configure our database to work in UTC too!

In case you want to get the value in millisecond instead of seconds in T-SQL you may use the following code

(cast(DATEDIFF(s, '1970-01-01', GETUTCDATE()) as bigint)*1000+datepart(ms,getutcdate()))

Please use DATEDIFF_BIG for MS SQL Server in order to not run into Year 2038 Problem

Thanks a lot

Thank you, that CAST to INT improves performance dramatically.