SQL Server時間粒度系列----第6節基於當前日的小時數和分鐘數與mysql unix_timestamp和from_unixtime的mssql實現

本文目錄列表：1、基於當前日的小時數和分鐘數2、mysqlunix_timestamp和from_unixtime的mssql實現3、總結語4、參考清單列表基於當前日的小時數和分鐘數平時工作中遇到過一天內個時間段的用戶登錄情況的需求，也有針對每個小時內的分鐘段內的用戶的活躍度的需求，很多類似的需求...

本文目錄列表： 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

執行後的查詢結果如下圖：

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實現參考了網上的實現方案，具體的參考網頁忘記啦，如有博友指出我在加上。