SQL 时间截按月分组查询

最新推荐文章于 2023-01-03 10:02:31 发布

iteye_264

最新推荐文章于 2023-01-03 10:02:31 发布

阅读量1.4k

点赞数

文章标签：数据库

选将时间截转换成日期格式，再执行分组查询

按月分组，查高询每月的记录总数，Yii用法

$art_summary = Yii::app()->db->createCommand()
    ->select('DATE_FORMAT(FROM_UNIXTIME(inputtime),"%Y-%m") as month, COUNT(art_id) as num')
    ->from('dr_artcle')
    ->group('month(FROM_UNIXTIME(inputtime))')
    ->queryAll();

查询每天的记录

$art_summary = Yii::app()->db->createCommand()
    ->select('*')
    ->from('dr_artcle')
    ->group('DATE_FORMAT(FROM_UNIXTIME(inputtime),"%Y-%m-%d")')
    ->queryAll();

MySQL （Unix 时间戳、日期）转换函数：

unix_timestamp(),
unix_timestamp(date),
from_unixtime(unix_timestamp),
from_unixtime(unix_timestamp,format)

下面是示例：

select unix_timestamp(); -- 1218290027
select unix_timestamp('2008-08-08'); -- 1218124800
select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800


select from_unixtime(1218290027); -- '2008-08-09 21:53:47'
select from_unixtime(1218124800); -- '2008-08-08 00:00:00'
select from_unixtime(1218169800); -- '2008-08-08 12:30:00'