转载

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

本文目录列表：

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

2、 mysql unix_timestamp和from_unixtime的mssql实现

3、总结语

4、参考清单列表

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

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

提供基于当前日的小时数和分钟数的功能函数，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

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

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

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

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

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