SQL Server时间粒度系列----第6节基于当前日的小时数和分钟数与mysql unix_timestamp和from

SQL Server时间粒度系列----第6节基于当前日的小时数和分钟数与mysql unix_timestamp和from_unixtime的mssql实现

本文目录列表：

1、基于当前日的小时数和分钟数
2、mysql unix_timestamp和from_unixtime的mssql实现

基于当前日的小时数和分钟数

平时工作中遇到过一天内个时间段的用户登录情况的需求，也有针对每个小时内的分钟段内的用户的活跃度的需求，很多类似的需求都是针对更小时间刻度比如小时、分钟来进行数据分析的。针对这样类似的需求提供获取指定日期时间的基于所在当前日午夜零时的小时数或分钟数的功能函数。

提供基于当前日的小时数和分钟数的功能函数，T-SQL代码如下：

 1 IF OBJECT_ID(N'dbo.ufn_HoursOfDay', 'FN') IS NOT NULL
 2 BEGIN
 3     DROP FUNCTION dbo.ufn_HoursOfDay;
 4 END
 5 GO
 6  
 7 --==================================
 8 -- 功能: 获取指定的日期日期基于所在当期日午夜零时的小时数
 9 -- 作者: 结果值从0开始计数，包括0、1、2、……、23
10 -- 作者: XXX
11 -- 创建: yyyy-MM-dd
12 -- 修改: yyyy-MM-dd XXX 修改内容描述
13 -- 调用: SET @tintHoursOfDay = dbo.ufn_HoursOfDay(GETDATE());
14 --==================================
15 CREATE FUNCTION dbo.ufn_HoursOfDay
16 (
17     @dtmDate DATETIME            -- 指定的日期时间
18 )
19 RETURNS TINYINT
20 AS
21 BEGIN
22     RETURN DATEPART(HOUR, @dtmDate);
23 END
24 GO
25  
26  
27 IF OBJECT_ID(N'dbo.ufn_MinutesOfDay', 'FN') IS NOT NULL
28 BEGIN
29     DROP FUNCTION dbo.ufn_MinutesOfDay;
30 END
31 GO
32  
33 --==================================
34 -- 功能: 获取指定的日期时间基于所在当前日午夜零时的分钟数
35 -- 作者: 结果值从0开始计数，包括0、1、2、3、1439
36 -- 作者: XXX
37 -- 创建: yyyy-MM-dd
38 -- 修改: yyyy-MM-dd XXX 修改内容描述
39 -- 调用: SET @sintMinutesOfDay = dbo.fn_MinutesOfDay(GETDATE());
40 --==================================
41 CREATE FUNCTION dbo.ufn_MinutesOfDay
42 (
43     @dtmDate DATETIME                    -- 指定的日期时间
44 )
45 RETURNS SMALLINT
46 AS
47 BEGIN
48     RETURN DATEPART(HOUR, @dtmDate) * 60 + DATEPART(MINUTE,@dtmDate);
49 END
50 GO

测试以上功能函数的效果，T-SQL代码如下：

 1 DECLARE @dtmDateTime AS DATETIME;
 2 SET @dtmDateTime = '2017-01-13 00:00:00'
 3  
 4 SELECT 
 5     @dtmDateTime AS 'The Current DateTime'
 6     ,dbo.ufn_HoursOfDay(@dtmDateTime) AS 'HoursOfDay'
 7     ,dbo.ufn_MinutesOfDay(@dtmDateTime) AS 'MinutesOfDay';
 8  
 9 SET @dtmDateTime = '2017-01-13 12:01:00'
10 SELECT
11     @dtmDateTime AS 'The Current DateTime'
12     ,dbo.ufn_HoursOfDay(@dtmDateTime) AS 'HoursOfDay'
13     ,dbo.ufn_MinutesOfDay(@dtmDateTime) AS 'MinutesOfDay';
14  
15 SET @dtmDateTime = '2017-01-13 23:59:00'
16 SELECT
17     @dtmDateTime AS 'The Current DateTime'
18     ,dbo.ufn_HoursOfDay(@dtmDateTime) AS 'HoursOfDay'
19     ,dbo.ufn_MinutesOfDay(@dtmDateTime) AS 'MinutesOfDay';
20 GO

执行后的查询结果如下图：

mysql unix_timestamp和from_unixtime的mssql实现

在mysql中，有一对unix_timestamp和from_unixtime的一对函数，将带有小时分钟表的日期时间和整数实现相互转换，基于“1970-01-01"这个UTC基准日期的。之前处理过将mysql的数据建议到mssql时，迁移过来的mysql的数据中有关日期时间的全部是8字节整数保存的，当时的处理方案没有在源数据增加此整数对应的日期时间的字段列，而是在mssql中将这个整数转换为日期时间，所以将mysql中unix_timestamp和from_unixtime的功能在mssql中实现。

MSSQL实现的针对功能函数，T-SQL代码如下：

 1 IF OBJECT_ID(N'dbo.ufn_UnixTimestamp', 'FN') IS NOT NULL
 2 BEGIN
 3     DROP FUNCTION dbo.ufn_UnixTimestamp;
 4 END
 5 GO
 6  
 7 --==================================
 8 -- 功能: 获取UnixTimestamp(unix日期时间戳)
 9 -- 说明: 结果值从0开始计数，基于
10 -- 作者: XXX
11 -- 创建: yyyy-MM-dd
12 -- 修改: yyyy-MM-dd XXX 修改内容描述
13 -- 调用: SELECT dbo.ufn_UnixTimestamp(GETDATE());
14 --==================================
15 CREATE FUNCTION dbo.ufn_UnixTimestamp
16 (
17     @dtmDateTime DATETIME                    -- 指定的日期时间
18 ) RETURNS BIGINT
19     --$Encode$-- 
20 AS
21 BEGIN    
22     DECLARE @dtmUnixBasedate AS DATETIME;
23     SET @dtmUnixBasedate = '1970-01-01';
24     DECLARE @tintCurrentTimeZone AS TINYINT;
25     SET @tintCurrentTimeZone = 8;
26  
27     IF @dtmDateTime IS NULL OR @dtmDateTime < DATEADD(HOUR, @tintCurrentTimeZone, @dtmUnixBasedate)
28     BEGIN
29         RETURN 0;
30     END
31  
32     SET @dtmDateTime = CONVERT(DATETIME, CONVERT(VARCHAR(23), @dtmDateTime, 120));
33  
34     RETURN DATEDIFF(SECOND, @dtmUnixBasedate, DATEADD(HOUR, -1*@tintCurrentTimeZone, @dtmDateTime));
35 END
36 GO
37  
38  
39 IF OBJECT_ID(N'dbo.ufn_FromUnixTimestamp', 'FN') IS NOT NULL
40 BEGIN
41     DROP FUNCTION dbo.ufn_FromUnixTimestamp;
42 END
43 GO
44  
45 --==================================
46 -- 功能: 获取UnixTimestamp(unix日期时间戳)
47 -- 说明: 具体实现阐述 
48 -- 作者: XXX
49 -- 创建: yyyy-MM-dd
50 -- 修改: yyyy-MM-dd XXX 修改内容描述
51 -- 调用: SELECT dbo.ufn_FromUnixTimestamp(2);
52 --==================================
53 CREATE FUNCTION dbo.ufn_FromUnixTimestamp
54 (
55     @bintUnixTimestamp BIGINT                            -- 指定的整数
56 ) RETURNS DATETIME
57     --$Encode$-- 
58 AS
59 BEGIN
60     DECLARE @dtmUnixBasedate AS DATETIME;
61     SET @dtmUnixBasedate = '1970-01-01';
62     DECLARE @tintCurrentTimeZone AS TINYINT;
63     SET @tintCurrentTimeZone = 8;
64  
65     IF @bintUnixTimestamp >= 1
66     BEGIN
67         RETURN DATEADD(HOUR, @tintCurrentTimeZone, DATEADD(SECOND, @bintUnixTimestamp, @dtmUnixBasedate))
68     END
69  
70     RETURN @dtmUnixBasedate;
71 END
72 GO

测试以上功能函数的效果，T-SQL代码如下：

 1 DECLARE @dtmDateTime AS DATETIME;
 2 SET @dtmDateTime = '1970-01-01'; 
 3  
 4 SELECT
 5     @dtmDateTime AS 'The Current DateTime'
 6     ,dbo.ufn_UnixTimestamp(@dtmDateTime) AS 'Bigint Value Base-on"1970-01-01"'
 7     ,dbo.ufn_FromUnixTimestamp(dbo.ufn_UnixTimestamp(@dtmDateTime)) AS 'The DateTime Mapping';
 8  
 9 SET @dtmDateTime = '2016-01-11';
10  
11 SELECT
12     @dtmDateTime AS 'The Current DateTime'
13     ,dbo.ufn_UnixTimestamp(@dtmDateTime) AS 'Bigint Value Base-on"1970-01-01"'
14     ,dbo.ufn_FromUnixTimestamp(dbo.ufn_UnixTimestamp(@dtmDateTime)) AS 'The DateTime Mapping';
15 GO

执行后的查询结果如下图：

总结语

本文简单提供了获取指定的日期时间基于所在当前日的小时数和分钟数的功能函数，也提供了类似mysql unixtimestamp和from_unixtime针对功能函数的mssql实现。

参考清单列表

1、https://msdn.microsoft.com/zh-cn/library/ms174420(v=sql.90).aspx

2、基于mysql unix_timestamp和from_unixtime的mssql实现参考了网上的实现方案，具体的参考网页忘记啦，如有博友指出我在加上。