SQL Server 中的 UNIX_TIMESTAMP

Question

我需要在 SQL Server 2008 中创建一个函数来模仿 mysql 的 UNIX_TIMESTAMP()。

Answer 1

如果您不关心 1970 年之前的日期或毫秒精度，只需执行以下操作：

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

几乎与 MySQL 的内置函数一样简单：

-- MySQL
SELECT UNIX_TIMESTAMP(DateField);

其他语言（Oracle、PostgreSQL 等）： 如何获取当前纪元时间...

---

如果您需要毫秒精度（SQL Server 2016/13.x 及更高版本）：

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

Answer 2

试试这个帖子：
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

或这篇文章：

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

代码如下：

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

示例用法：

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

Answer 3

Sql Server 2016 及更高版本有一个 DATEDIFF_BIG 函数，可用于获取毫秒。

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

创建一个函数

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

并执行它

SELECT dbo.UNIX_TIMESTAMP()

Answer 4

对于毫秒结果的时间戳，我从这里找到了这个解决方案 https://gist.github.com/rsim/d11652a8336137832df9 ：

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

@Rafe 的回答对我来说没有正确工作（MSSQL 20212) - 我有 9 秒的差异。

Answer 5

我经常需要一个毫秒精度的 unix 时间戳。下面将为您提供当前的unixtime FLOAT；包装上面的答案以获取函数或转换任意字符串。

SQL Server 上的 DATETIME 数据类型仅适用于 3 毫秒，因此我为 SQL Server 2005 和 2008+ 提供了不同的示例。遗憾的是，没有 DATEDIFF2 函数，因此即使在 2008 年以上，也需要各种技巧来避免 DATEDIFF 整数溢出。 （我不敢相信他们在没有修复这个问题的情况下引入了一个全新的 DATETIME2 数据类型。）

对于常规的旧 DATETIME，我只是使用一个低级的强制转换来浮动，它返回 (floating点）自 1900 年以来的天数。

现在我知道此时您在想闰秒怎么样？！？！ Windows 时间和 unixtime 都不真正相信闰秒：对于 SQL Server 来说一天总是 1.00000 天长，对于 unixtime 来说一天总是 86400 秒长。 这篇维基百科文章讨论了unixtime在闰秒期间的行为方式；我相信 Windows 只是像任何其他时钟错误一样查看闰秒。因此，虽然当闰秒发生时两个系统之间不存在系统漂移，但它们在闰秒期间和之后的亚秒级别上不会达成一致。

-- 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;

FLOAT 实际上在 SQL Server 上默认为 8 字节双精度数，因此在许多用例中优于 32 位INT。 （例如，它们不会在 2038 年滚动。）

Answer 6

死灵术。
ODBC方式：

DECLARE @unix_timestamp varchar(20)
-- SET @unix_timestamp = CAST({fn timestampdiff(SQL_TSI_SECOND,{d '1970-01-01'}, CURRENT_TIMESTAMP)} AS varchar(20)) 

IF CURRENT_TIMESTAMP >= '20380119' 
BEGIN 
    SET @unix_timestamp = CAST
    (
        CAST
        (
            {fn timestampdiff(SQL_TSI_SECOND,{d '1970-01-01'}, {d '2038-01-19'})} 
            AS bigint
        )  
        + 
        CAST
        (
            {fn timestampdiff(SQL_TSI_SECOND,{d '2038-01-19'}, CURRENT_TIMESTAMP)}
            AS bigint
        ) 
    AS varchar(20)
    ) 
END 
ELSE 
    SET @unix_timestamp = CAST({fn timestampdiff(SQL_TSI_SECOND,{d '1970-01-01'}, CURRENT_TIMESTAMP)} AS varchar(20))

PRINT @unix_timestamp

Answer 7

这是一个不声明任何函数或变量的单行解决方案：

SELECT CAST(CAST(GETUTCDATE()-'1970-01-01' AS decimal(38,10))*86400000.5 as bigint)

Answer 8

如果您必须处理 SQL Server 的早期版本（<2016）并且您只关心正时间戳，我在这里发布了我为非常遥远的日期找到的解决方案（这样您就可以从 @rkosegi 的答案中删除 IF 。

什么）我所做的是首先计算天数差异，然后添加剩余秒数差异。

CREATE FUNCTION [dbo].[UNIX_TIMESTAMP]
( 
    @inputDate DATETIME 
)
RETURNS BIGINT 
AS 
BEGIN
    DECLARE @differenceInDays BIGINT, @result BIGINT;
    SET @differenceInDays = DATEDIFF(DAY, '19700101', @inputDate)
    IF @differenceInDays >= 0
        SET @result = (@differenceInDays * 86400) + DATEDIFF(SECOND, DATEADD(DAY, 0, DATEDIFF(DAY, 0, @inputDate)), @inputDate)
    ELSE
        SET @result = 0
    RETURN @result
END

Answer 9

当调用标量函数时，可以使用以下语法

函数脚本：

USE [Database]
GO

/****** Object:  UserDefinedFunction [dbo].[UNIX_TIMESTAMP]  ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[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 
GO

调用函数：

SELECT dbo.UNIX_TIMESTAMP(GETDATE());