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

xiaomingtt 发表于 2020-1-12 22:16

本帖最后由 xiaomingtt 于 2020-1-13 11:18 编辑

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

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

shuye216 发表于 2020-1-12 22:28

定义和用法
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 发表于 2020-1-12 22:55

本帖最后由 13450774262 于 2020-1-13 00:07 编辑

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

viptech 发表于 2020-1-13 08:37

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

VioletKiss 发表于 2020-1-13 10:45

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

VioletKiss 发表于 2020-1-13 10:57

本帖最后由 VioletKiss 于 2020-1-13 11:00 编辑

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 发表于 2020-1-13 11:04

$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 发表于 2020-1-13 11:06

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

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

xiaomingtt 发表于 2020-1-13 11:08

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 发表于 2020-1-13 11:09

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

不是这样的。

页: [1] 2

吾爱破解 - 52pojie.cn's Archiver

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