DATEDIFF函数小问题

函数语法如下DATEDIFF ( datepart , startdate , enddate )，返回指定的 startdate 和 enddate 之间所跨的指定 datepart 边界的计数（带符号的整数）。下面来说说我碰到的这个问题。用户表里user里面有个字段Create_Time记录用户注册日期，Last_Login_Time 记录用户最后登录的时间，要统计注册用户在注册后，按注册日期统计第一天、第二天、第三天......第七天，七天以后登录人数。用来了解注册用户的回流信息。

代码Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-- DECLARE @cmdText VARCHAR(8000);

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;

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

 GROUP BY U.Create_Time

 ORDER BY U.Create_Time;

--PRINT @cmdText 

EXEC (@cmdText);

 

本来如果到此为止，问题就完了，但是这个数据库实例所在服务器是美国时间，我们要按中国时间来统计，所以我就把上面其中的一段的脚本做了如下改写

代码Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-- IF (@userIndex != 29)

 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这里犯了错误。本意为了提高效率着想，却犯了

一个错误

SELECT DATEDIFF(D, 2010-7-25 0:30, 2010-7-26 23:59)

--加上15个小时后

SELECT DATEDIFF(D, 2010-7-25 15:30, 2010-7-27 15:59)

 上面查询结果是1 和 2 ，问题就出在这里。所以把脚本改写后，就OK了。如果以后碰到类似问题，一定要注意！

Psqlgresql Time function时间函数 Psqlgresql Time function时间函数 select now() 获取的时间为什么一直不变 pg中获取时间的方式有多种 如果放在事务中，now()获取的就是事务开始的时间，事务不结束，时间不变；而clock_timestamp()显示的时间会实时变化。
一种获取当前时间，以及时间格式化的模块 time模块在Python原生安装中就存在，直接使用即可，无需额外的安装操作 == 导入方式：import time == # -*- coding: utf-8 -*- import time import locale 设置本地语言类型为中文 locale.
潇湘隐者 网名潇湘隐者/潇湘剑客、英文名Kerry，兴趣广泛，广泛涉猎，个性随意，不善言辞。执意做一名会写代码的DBA，混迹于IT行业