SQL插入unix时间戳以毫秒为单位

Question

unix时间戳可以包含时间的毫秒值吗?

SQL Server 2008 R2:

CREATE TABLE [dbo].[my_table_calls_log]
(
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [requestdate] [bigint] NULL,
    [partycode] [bigint] NULL,
    CONSTRAINT [PK_my_table_calls_log] 
    PRIMARY KEY CLUSTERED ([id] ASC)
)

下面的select语句以毫秒为单位给出当前日期和时间:

SELECT CONVERT(VARCHAR,SYSDATETIME(),121);

的例子:

2015-09-11 13:29:02.8239061

我如何转换长YYYY-MM-DD HH:MM:SS。将MS日期/时间值转换为unix时间戳，以便我可以使用如下方式插入它:

DECLARE @UnixDate AS bigint;
SET @UnixDate = "the unix timestamp equivalent of SELECT CONVERT(VARCHAR,SYSDATETIME(),121);"
INSERT INTO my_table_calls_log (requestdate,partycode)
VALUES (@UnixDate,123);

Answer 1

标准unix时间戳是32位带符号的整数，精度为1秒(见这里)，这就是说，如果您选择使用64位整数并使用毫秒精度，当然没有什么无效的(只要您确定所有使用/消费该时间戳值的代码都知道该约束)。大多数标准unix时间戳库假设它是1秒的精度，如果你传递一个64位带符号的整数而不是一个仍然有效的32位整数，并且假设用户只是计划更长的时间戳寿命(即跟踪2038年1月19日之后的日期/时间值，这是基于32位的unix时间戳将在默认纪元的某个点停止工作的日期，由于溢出)。

Unix时间戳通常也假设一个基本纪元为'1970-01-01 00:00:00'，因此使用这个假设，您可以使用如下所示将标准SQL Server日期时间值转换为Unix时间戳:

select datediff(second, '1970-01-01T00:00:00.000', sysdatetime());

如果你想以毫秒为基础，它会变得有点复杂，因为SQL Server中的datediff函数是基于32位整数的(即int数据类型)，默认纪元和当前日期/时间之间的毫秒差超过了该类型的上限，所以我们必须有点创意，这里有一个选项(我倾向于使用函数包装):

declare @start datetime2 = '1970-01-01T00:00:00.000',
        @end datetime2 = sysdatetime(),
        @ms_in_day bigint = 60 * 60 * 24 * 1000;
select  (@ms_in_day * datediff(day, @start, @end)) - datediff(millisecond, @end, cast(@end as date));

在这些示例中，如果您有一个基于varchar/字符的日期/时间值，只需直接插入到脚本中，以取代我使用的sysdatetime()函数。