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
执行后的查询结果如下图 ：
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实现参考了网上的实现方案，具体的参考网页忘记啦，如有博友指出我在加上。
正文到此结束
热门推荐
相关文章
近期评论
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
搞个gitee的项目
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
