Skip to content

Instantly share code, notes, and snippets.

@rsim

rsim/gist:d11652a8336137832df9

Created April 7, 2015 08:01
Show Gist options
  • Star 6 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save rsim/d11652a8336137832df9 to your computer and use it in GitHub Desktop.
Save rsim/d11652a8336137832df9 to your computer and use it in GitHub Desktop.
Download ZIP
How to get the current Unix timestamp in SQL
Raw
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
@mikegolod
Copy link

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!

@tikluganguly
Copy link

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()))

@aersam
Copy link

aersam commented Sep 6, 2021

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

@Danipulok
Copy link

Thanks a lot

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment