DATEDIFF函数小问题
2010-07-27 08:20 潇湘隐者 阅读(1719) 评论(0) 编辑 收藏 举报 DATEDIFF
函数语法如下DATEDIFF
( datepart , startdate , enddate ),返回指定的 startdate 和 enddate 之间所跨的指定 datepart 边界的计数(带符号的整数)。下面来说说我碰到的这个问题。用户表里user里面有个字段Create_Time记录用户注册日期,Last_Login_Time 记录用户最后登录的时间,要统计注册用户在注册后,按注册日期统计第一天、第二天、第三天......第七天,七天以后登录人数。用来了解注册用户的回流信息。

DECLARE @userIndex INT;
SET @cmdText = '';
SET @userIndex = 0;
WHILE @userIndex <30
BEGIN
IF (@userIndex != 29)
SELECT @cmdText = @cmdText +
'SELECT CONVERT(VARCHAR(10), Create_Time, 120) AS Create_Time,
DATEDIFF(D, Create_Time, Last_Login_Time) LoginDay
FROM ' + ' dbo.user' + CONVERT(VARCHAR,@userIndex)
+ ' UNION ALL' + CHAR(10); --换行
ELSE
SELECT @cmdText = @cmdText +
'SELECT CONVERT(VARCHAR(10), Create_Time, 120) AS Create_Time,
DATEDIFF(D, Create_Time, Last_Login_Time) LoginDay
FROM ' + ' dbo.user' + CONVERT(VARCHAR,@userIndex) ;
SET @userIndex = @userIndex + 1;
END;
SELECT @cmdText =
'SELECT U.Create_Time,SUM(RegisterNum) AS RegisterNum,
SUM(FirstDay) AS FirstDay, SUM(TwoDay) AS TwoDay,
SUM(ThirdDay) AS ThirdDay, SUM(FourDay) AS FourDay,
SUM(FiveDay) AS FiveDay,SUM(SixDay) AS SixDay,
SUM(SevenDay) AS SevenDay, SUM(Others) AS Others
FROM (
SELECT Create_Time, COUNT(Create_Time) AS RegisterNum,
CASE WHEN LoginDay = 0 THEN COUNT(Create_Time) ELSE 0 END AS FirstDay,
CASE WHEN LoginDay = 1 THEN COUNT(Create_Time) ELSE 0 END AS TwoDay,
CASE WHEN LoginDay = 2 THEN COUNT(Create_Time) ELSE 0 END AS ThirdDay,
CASE WHEN LoginDay = 3 THEN COUNT(Create_Time) ELSE 0 END AS FourDay,
CASE WHEN LoginDay = 4 THEN COUNT(Create_Time) ELSE 0 END AS FiveDay,
CASE WHEN LoginDay = 5 THEN COUNT(Create_Time) ELSE 0 END AS SixDay,
CASE WHEN LoginDay = 6 THEN COUNT(Create_Time) ELSE 0 END AS SevenDay,
CASE WHEN LoginDay > 6 THEN COUNT(Create_Time) ELSE 0 END AS Others
FROM
(
SELECT T.Create_Time, T.LoginDay
FROM
('
+ @cmdText +
') T
) TT
GROUP BY TT.Create_Time, TT.LoginDay
) U
GROUP BY U.Create_Time
ORDER BY U.Create_Time';
--PRINT @cmdText
EXEC (@cmdText);
本来如果到此为止,问题就完了,但是这个数据库实例所在服务器是美国时间,我们要按中国时间来统计,所以我就把上面其中的一段的脚本做了如下改写

SELECT @cmdText = @cmdText +
'SELECT CONVERT(VARCHAR(10), DATEADD(hh, 15,Create_Time), 120) AS Create_Time,
DATEDIFF(D, Create_Time, Last_Login_Time) LoginDay
FROM ' + ' dbo.user' + CONVERT(VARCHAR,@userIndex)
+ ' UNION ALL' + CHAR(10); --换行
ELSE
SELECT @cmdText = @cmdText +
'SELECT CONVERT(VARCHAR(10), DATEADD(hh, 15,Create_Time), 120) AS Create_Time,
DATEDIFF(D, Create_Time, Last_Login_Time) LoginDay
FROM ' + ' dbo.user' + CONVERT(VARCHAR,@userIndex) ;
SET @userIndex = @userIndex + 1;
当时大概想了想: DATEDIFF(D, Create_Time, Last_Login_Time) LoginDay 求相隔几天,注册时间和最后登录时间都要相加15个小时,那不是等同于没有相加,为了“效率”,我就用了上面的脚本。
下面问题来了,如图所示(执行时间时间是2007-7-26),怎么2010-7-26的数据在TwoDay列是198,2010-7-25注册的用户第三天还有148个登录,明显错了
刚开始还一头雾水,觉得自己的逻辑没有出错,后来仔细检查了并和同事讨论了后,才发现自己在上面 DATEDIFF(D, Create_Time, Last_Login_Time) LoginDay这里犯了错误。本意为了提高效率着想,却犯了
一个错误
--加上15个小时后
SELECT DATEDIFF(D, '2010-7-25 15:30', '2010-7-27 15:59')
上面查询结果是1 和 2 ,问题就出在这里。所以把脚本改写后,就OK了。如果以后碰到类似问题,一定要注意!

· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库