UNIX_TIMESTAMP in SQL Server

If you're not bothered about dates before 1970, or millisecond precision, just do:

-- SQL Server
SELECT DATEDIFF(s, '1970-01-01 00:00:00', DateField)

Almost as simple as MySQL's built-in function:

-- MySQL
SELECT UNIX_TIMESTAMP(DateField);

Other languages (Oracle, PostgreSQL, etc): How to get the current epoch time in ...

If you need millisecond precision (SQL Server 2016/13.x and later):

SELECT DATEDIFF_BIG(ms, '1970-01-01 00:00:00', DateField)

Try this post: https://web.archive.org/web/20141216081938/http://skinn3r.wordpress.com/2009/01/26/t-sql-datetime-to-unix-timestamp/

CREATE FUNCTION UNIX_TIMESTAMP (
@ctimestamp datetime
)
RETURNS integer
AS 
BEGIN
  /* Function body */
  declare @return integer

  SELECT @return = DATEDIFF(SECOND,{d '1970-01-01'}, @ctimestamp)

  return @return
END

or this post:

http://mysql.databases.aspfaq.com/how-do-i-convert-a-sql-server-datetime-value-to-a-unix-timestamp.html

code is as follows:

CREATE FUNCTION dbo.DTtoUnixTS 
( 
    @dt DATETIME 
) 
RETURNS BIGINT 
AS 
BEGIN 
    DECLARE @diff BIGINT 
    IF @dt >= '20380119' 
    BEGIN 
        SET @diff = CONVERT(BIGINT, DATEDIFF(S, '19700101', '20380119')) 
            + CONVERT(BIGINT, DATEDIFF(S, '20380119', @dt)) 
    END 
    ELSE 
        SET @diff = DATEDIFF(S, '19700101', @dt) 
    RETURN @diff 
END

Sample usage:

SELECT dbo.DTtoUnixTS(GETDATE()) 
-- or 
SELECT UnixTimestamp = dbo.DTtoUnixTS(someColumn) 
    FROM someTable

Sql Server 2016 and later have a DATEDIFF_BIG function that can be used to get the milliseconds.

SELECT DATEDIFF_BIG(millisecond, '1970-01-01 00:00:00', GETUTCDATE())

Create a function

CREATE FUNCTION UNIX_TIMESTAMP()
    RETURNS BIGINT
AS
BEGIN
    RETURN DATEDIFF_BIG(millisecond, '1970-01-01 00:00:00', GETUTCDATE())
END

And execute it

SELECT dbo.UNIX_TIMESTAMP()

I often need a unix timestamp with millisecond precision. The following will give you the current unixtime as FLOAT; wrap per answers above to get a function or convert arbitrary strings.

The DATETIME datatype on SQL Server is only good to 3 msec, so I have different examples for SQL Server 2005 and 2008+. Sadly there is no DATEDIFF2 function, so various tricks are required to avoid DATEDIFF integer overflow even with 2008+. (I can't believe they introduced a whole new DATETIME2 datatype without fixing this.)

For regular old DATETIME, I just use a sleazy cast to float, which returns (floating point) number of days since 1900.

Now I know at this point, you are thinking WHAT ABOUT LEAP SECONDS?!?! Neither Windows time nor unixtime really believe in leap seconds: a day is always 1.00000 days long to SQL Server, and 86400 seconds long to unixtime. This wikipedia article discusses how unixtime behaves during leap seconds; Windows I believe just views leap seconds like any other clock error. So while there is no systematic drift between the two systems when a leap second occurs, they will not agree at the sub-second level during and immediately following a leap second.

-- the right way, for sql server 2008 and greater
declare @unixepoch2 datetime2;
declare @now2 Datetime2;
declare @days int;
declare @millisec int;
declare @today datetime2;
set @unixepoch2 = '1970-01-01 00:00:00.0000';
set @now2 = SYSUTCDATETIME();
set @days = DATEDIFF(DAY,@unixepoch2,@now2);
set @today = DATEADD(DAY,@days,@unixepoch2);
set @millisec = DATEDIFF(MILLISECOND,@today,@now2);
select (CAST (@days as float) * 86400) + (CAST(@millisec as float ) / 1000)
  as UnixTimeFloatSQL2008

-- Note datetimes are only accurate to 3 msec, so this is less precise 
-- than above, but works on any edition of SQL Server.
declare @sqlepoch datetime;
declare @unixepoch datetime;
declare @offset float;
set @sqlepoch = '1900-01-01 00:00:00';
set @unixepoch = '1970-01-01 00:00:00';
set @offset = cast (@sqlepoch as float) - cast (@unixepoch as float);
select ( cast (GetUTCDate() as float) + @offset) * 86400 
  as UnixTimeFloatSQL2005;

-- Future developers may hate you, but you can put the offset in
-- as a const because it isn't going to change. 
declare @sql_to_unix_epoch_in_days float;
set @sql_to_unix_epoch_in_days = 25567.0;
select ( cast (GetUTCDate() as float) - @sql_to_unix_epoch_in_days) * 86400.0 
  as UnixTimeFloatSQL2005MagicNumber;

FLOATs actually default to 8-byte doubles on SQL Server, and therefore superior to 32-bit INT for many use cases. (For example, they won't roll over in 2038.)

UNIX_TIMESTAMP in SQL Server

Related

Recent Posts