mysql数据库中以时间戳记录的值如何按小时查询

xiaomingtt

比如有一个数据库，有id，username，time三列。
其中time以10位时间戳记录用户注册时间.
现在要查询每周七天每天每小时注册的总人数（不是分出每个周，也不是特定的哪个周）。
比如数据库所有记录中周一0：00~0：59注册的用户数量是多少；
1：00~1：59注册的用户数量是多少；
……
周二0：00~0：59注册的用户数量是多少；
……
周日：23：00~23：59注册的用户数量是多少。
这样的SQL语句应该怎样写？


感谢大家回复，可能上面表述不太清楚。
目前已经用PHP解决。
https://www.52pojie.cn/forum.php?mod=redirect&goto=findpost&ptid=1089921&pid=29498697

shuye216

定义和用法
DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

语法
DATE_FORMAT(date,format)
date 参数是合法的日期。format 规定日期/时间的输出格式。

可以使用的格式有：

格式        描述
%a        缩写星期名
%b        缩写月名
%c        月，数值
%D        带有英文前缀的月中的天
%d        月的天，数值(00-31)
%e        月的天，数值(0-31)
%f        微秒
%H        小时 (00-23)
%h        小时 (01-12)
%I        小时 (01-12)
%i        分钟，数值(00-59)
%j        年的天 (001-366)
%k        小时 (0-23)
%l        小时 (1-12)
%M        月名
%m        月，数值(00-12)
%p        AM 或 PM
%r        时间，12-小时（hh:mm:ss AM 或 PM）
%S        秒(00-59)
%s        秒(00-59)
%T        时间, 24-小时 (hh:mm:ss)
%U        周 (00-53) 星期日是一周的第一天
%u        周 (00-53) 星期一是一周的第一天
%V        周 (01-53) 星期日是一周的第一天，与 %X 使用
%v        周 (01-53) 星期一是一周的第一天，与 %x 使用
%W        星期名
%w        周的天 （0=星期日, 6=星期六）
%X        年，其中的星期日是周的第一天，4 位，与 %V 使用
%x        年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y        年，4 位
%y        年，2 位
实例
下面的脚本使用 DATE_FORMAT() 函数来显示不同的格式。我们使用 NOW() 来获得当前的日期/时间：

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
DATE_FORMAT(NOW(),'%m-%d-%Y')
DATE_FORMAT(NOW(),'%d %b %y')
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')
结果类似：

Dec 29 2008 11:45 PM
12-29-2008
29 Dec 08
29 Dec 2008 16:25:46.635

13450774262

select from_unixtime(time,'yyyy-MM-dd HH'),count(*) from _ where 1=1 group by from_unixtime(time,'yyyy-MM-dd HH')

viptech

获取起始时间戳 和截止时间时间戳  select * from table where time > start_time and time < end_time  

VioletKiss

一般不建议使用sql语言来处理复杂逻辑，建议把需要的7天所有数据查出来然后用编程语言处理

VioletKiss

[SQL] 纯文本查看 复制代码

SELECT FROM_UNIXTIME(u.time,'%Y-%m-%d %H'),count(*) FROM user u 
WHERE FROM_UNIXTIME(u.time,'%Y-%m-%d %H:%i:%s') >= DATE_FORMAT('2020-01-13 00:00:00','%Y-%m-%d %H:%i:%s')
AND FROM_UNIXTIME(u.time,'%Y-%m-%d %H:%i:%s') < DATE_FORMAT('2020-01-20 00:00:00','%Y-%m-%d %H:%i:%s')
GROUP BY from_unixtime(u.time,'%Y-%m-%d %H')

你要的语句 mysql

其中的字符串日期你可以替换成你想要的七天，第二个日期你也可以用data_add来处理

xiaomingtt

[PHP] 纯文本查看 复制代码

	$sql="select time from user where status=2";
	$requ=mysqli_query($con,$sql);
	$date=array(
			array(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
			array(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
			array(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
			array(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
			array(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
			array(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
			array(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
			);
	while($rs=mysqli_fetch_array($requ)){
		$t=$rs['time'];
		if($t!=0){
			$w=date('w',$t);
			$h=date("G",$t);
			$date[$w][$h]++;
		}
	}
	$d='[';
	for($i=0;$i<7;$i++){
		for($j=0;$j<24;$j++){
			$dd="[$i,$j,".$date[$i][$j]."]";
			$d.="$dd,";
		}
	}
	$d=rtrim($d,',');
	$d.=']';

xiaomingtt

xiaomingtt 发表于 2020-1-13 11:04
[mw_shl_code=php,true]        $sql="select time from user where status=2";
        $requ=mysqli_query($con,$sql); ...

目前没有使用SQL，使用PHP感觉也很方便

xiaomingtt

13450774262 发表于 2020-1-12 22:55
select from_unixtime(time,'yyyy-MM-dd HH'),count(*) from _ where 1=1 group by from_unixtime(time,'yy ...

这个是不是每小时的数量，没有按一周七天区分？

xiaomingtt

viptech 发表于 2020-1-13 08:37
获取起始时间戳 和截止时间时间戳  select * from table where time > start_time and time < end_time

不是这样的。