SQL Server中的UNIX时间戳

wxclj1h5  于 2022-12-28  发布在  SQL Server
关注(0)|答案(9)|浏览(241)

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

bhmjp9jg

bhmjp9jg1#

如果你不介意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)
lztngnrs

lztngnrs2#

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

carvr3hs3#

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

ddarikpa4#

我经常需要一个毫秒精度的unix时间戳,下面将给予你当前的unixtime为FLOAT;根据上面的答案进行 Package 以获取函数或转换任意字符串。
SQL Server上的DATETIME数据类型只能支持3毫秒,因此我为SQL Server 2005和2008+提供了不同的示例。遗憾的是,没有DATEDIFF2函数,因此即使在2008+中也需要各种技巧来避免DATEDIFF整数溢出。(我不敢相信他们引入了一个全新的DATETIME2数据类型而没有解决这个问题。)
对于常规的旧DATETIME,我只是使用了一个简单的float类型转换,它返回(浮点)自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年不会翻转)。

epfja78i

epfja78i5#

对于毫秒结果的时间戳,我在https://gist.github.com/rsim/d11652a8336137832df9中找到了此解决方案:

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

来自@Rafe的答案对我来说不正确(MSSQL 20212)-我得到了9秒的差异。

c9qzyr3d

c9qzyr3d6#

招魂术。
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
ddhy6vgd

ddhy6vgd7#

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

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

55ooxyrt8#

如果您必须处理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
f0brbegy

f0brbegy9#

调用标量值函数时可以使用以下语法
函数脚本:

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

相关问题