sql server 生成uuid sql server 生成时间时间戳

SQL Server 时间转时间戳（毫秒）报错 Msg 535, Level 16, State 0, Line 1 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

错误场景：

查询时间戳（毫秒）时报错

SELECT DATEDIFF(MS, '1970-01-01 08:00:00.000', '2022-06-06 12:30:30.555');

Msg 535, Level 16, State 0, Line 1

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

 

 

 

---

错误原因：

DATEDIFF函数的返回值是一个int类型的数据

在SQL Server中int数据类型的取值范围为-2^31 (-2,147,483,648) ~ 2^31-1 (2,147,483,647)

时间2022-06-06 12:30:30.555的时间戳（毫秒）为1654489830555

报错的原因就是因为DATEDIFF函数计算得出的时间戳（毫秒）值已经超出了int类型的取值范围，所以才导致的报错。

其中官网也给出了获取毫秒以及秒的最大时间差

https://docs.microsoft.com/zh-cn/sql/t-sql/functions/datediff-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16

MS（毫秒）的最大时间差为 24 天 20 小时 31 分钟 23.647 秒

S（秒），的最大时间差为 68 年 19 天 3 小时 14 分 7 秒

 

 

 

---

解决方案1：

先计算距离1970-01-01间隔多少天，然后再计算距离当天00:00:00.000间隔多少毫秒，然后将他们相加再减去8小时的毫秒差，就得到了时间戳毫秒值。

为什么不是秒和毫秒相加，而是天和毫秒相加呢？因为秒的最大时间差为68年，现在已经过了52年了，如果是计算秒的话，隔个16年就会超出了，到时候又用不了，垃圾SQL Server ……

DECLARE @v_date DATETIME2(3); -- 总时间

DECLARE @v_date_d DATETIME2(3); -- 天

DECLARE @v_date_time BIGINT; -- 总时间戳

DECLARE @v_date_time_d BIGINT; -- 间隔多少天

DECLARE @v_date_time_d_ms BIGINT; -- 当天间隔多少毫秒




-- 设置总时间

SET @v_date = CONVERT( DATETIME2(3), '2022-06-06 12:30:30.555');

-- 去除时分秒毫秒精度

SET @v_date_d = CONVERT( DATETIME2(3), FORMAT( @v_date, 'yyyy-MM-dd'));




-- 打印总时间：2022-06-06 12:30:30.555

SELECT @v_date '总时间';

-- 打印天：2022-06-06 00:00:00.000

SELECT @v_date_d '天';




-- 计算出1970-01-01 00:00:00.000距离2022-06-06 00:00:00.000总共间隔多少天

SET @v_date_time_d = DATEDIFF(dd, '1970-01-01 00:00:00.000', @v_date_d);

-- 计算2022-06-06 00:00:00.000距离2022-06-06 12:30:30.555间隔多少毫秒

SET @v_date_time_d_ms = DATEDIFF(ms, @v_date_d, @v_date);




-- 打印总共间隔多少天：19149

SELECT @v_date_time_d '总共间隔多少天';

-- 打印当天间隔多少毫秒：45030555

SELECT @v_date_time_d_ms '当天间隔多少毫秒';




-- 总间隔毫秒值= (总共间隔多少天 * 24 * 60 * 60 * 1000) + 当天间隔多少毫秒 - (8 * 60 * 60 * 1000)

-- (8 * 60 * 60 * 1000)：因为咱们中国的无产阶级住在的东八区（UTC+08:00），所以需要减去8小时的毫秒数

SET @v_date_time = (@v_date_time_d * 24 * 60 * 60 * 1000) + @v_date_time_d_ms - (8 * 60 * 60 * 1000);




-- 打印总间隔毫秒值，这个值就是时间戳：1654489830555

SELECT @v_date_time '总间隔毫秒值';

 

简化一下SQL语句后可以这样写：

DECLARE @v_date DATETIME2(3); -- 时间

-- 设置时间

SET @v_date = CONVERT( DATETIME2(3), '2022-06-06 12:30:30.555');




-- 打印总间隔毫秒值，这个值就是时间戳：1654489830555

SELECT

    (CAST(DATEDIFF(dd, '1970-01-01 00:00:00.000', @v_date) AS BIGINT) * 24 * 60 * 60 * 1000)

        +

    CAST(DATEDIFF(ms, CONVERT( DATETIME2(3), FORMAT( @v_date, 'yyyy-MM-dd')), @v_date) AS BIGINT)

        -

    (8 * 60 * 60 * 1000)

    AS '总间隔毫秒值';

 

 

 

---

解决方案2：

使用DATEDIFF_BIG函数，该函数为SQL Server 2016 (13.x)版本新增的函数返回值是BIGINT不会因为取值范围过小而导致溢出了。

官方文档：https://docs.microsoft.com/zh-cn/sql/t-sql/functions/datediff-big-transact-sql?view=sql-server-ver16

示例：

将'1970-01-01 00:00:00.000增加8小时'1970-01-01 08:00:00.000就不需要减8小时的毫秒数了

SELECT DATEDIFF_BIG(ms, '1970-01-01 08:00:00.000', '2022-06-06 12:30:30.555');