SQL 时间截按月分组查询

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

按月分组，查高询每月的记录总数，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"