在MySQL中按照当地时间的时段、星期几统计

缘起

BT群创建了一个基于status.net的话痨群。我一时手痒，做了个后台，进行一个基本的统计。比如：一天24个时段中每个时段的话痨数量、一周7天中每天的话痨数量等。

这些统计都是要用到基于时间的统计。

我先看了一下后台数据库，我们所使用的这个服务程序将“话痨”贴存放在notice表格中，用字段created来记录。不过它记录的是GMT时间（或者说UTC）时间。这个时间和北京时间存在着8个小时的时差，会造成时段统计的偏移和周内统计的偏移。例如，我在20号上午7点发的话痨，会被储存为19号23时发布的。

一开始，我是用调整服务程序的时区来解决的。虽然在部分程度上解决了“本站点”的问题，但是造成了其他各位BT使用的客户端无法正确获得时间的问题。这个后果是严重的。

解决方案

解决方案只能从MySQL里面去找。通过查阅MySQL参考手册，发现了这么个函数：

CONVERT_TZ(dt,from_tz,to_tz)

通过这个函数就可以将一个时区的时间转换到另外一个时间。于是，相应的按照时段统计的SQL语句就是：

select count(*) nc, hour(convert_tz(created, '$stz','$etz' )) dh from notice group by dh

其中的$stz和$etz是两个时区的时间偏移。在本程序中，用的是\’+00:00\’和\’+08:00\’。 经过调试，成功！

题外话

在CodeIgniter中，如果使用内置的helper函数来生成SQL语句，那么会不成功。提示生成的SQL有错误，或者是找不到\’+00:00\’这个字段。解决方法是，改写model中的查询过程为：

public function getNoticeCountHour()
{
    $stz='+00:00';
    $etz='+08:00';
    $selectsql=select count(*) nc, hour(convert_tz(created, '$stz','$etz' )) dh from notice group by dh;
    //下面的代码无法执行
    //$this->db->select(count( *  ) nc, hour(convert_tz(created, '$stz','$etz' ) ) dh);
    //$this->db->group_by('dh');
    //$q=$this->db->get('notice');
    return $this->db->query($selectsql)->result();
}

这个应该是CodeIgniter的问题。暂时没有时间去搞了。

另外，根据MySQL的文档，使用了convert_tz函数后，这个查询将不能被缓存，会影响执行效率。