dre26973 2015-12-17 11:33
浏览 214

按固定日期间隔选择和分组

I'm working on a big MySQL time based statistic table.

I have a fixed time range (start and end date time objects) and get an interval string in ISO-8601 interval string like P1DT6H as PHP object where the start date in the range is also the start point of the interval and also defines the timezone used for the interval.

Now I want to select all data within the given time range grouped by this interval but I can't make it work even after lot of hours :(

For example I get a time range of 2015-09-01/2015-09-06 and an interval of P1DT6H and the following example table:

TIMESTAMP           | count
2015-09-01 00:00:00 | 1
2015-09-01 02:00:00 | 1
2015-09-01 04:00:00 | 1
2015-09-01 06:00:00 | 1
2015-09-01 08:00:00 | 1
2015-09-01 10:00:00 | 1
2015-09-01 12:00:00 | 1
2015-09-01 14:00:00 | 1
2015-09-01 16:00:00 | 1
2015-09-01 18:00:00 | 1
2015-09-01 20:00:00 | 1
2015-09-01 22:00:00 | 1
2015-09-03 00:00:00 | 1
2015-09-03 02:00:00 | 1
2015-09-03 04:00:00 | 1
2015-09-03 06:00:00 | 1
2015-09-03 08:00:00 | 1
2015-09-03 10:00:00 | 1
2015-09-03 12:00:00 | 1
2015-09-03 14:00:00 | 1
2015-09-03 16:00:00 | 1
2015-09-03 18:00:00 | 1
2015-09-03 20:00:00 | 1
2015-09-03 22:00:00 | 1
2015-09-05 00:00:00 | 1
2015-09-05 02:00:00 | 1
2015-09-05 04:00:00 | 1
2015-09-05 06:00:00 | 1
2015-09-05 08:00:00 | 1
2015-09-05 10:00:00 | 1
2015-09-05 12:00:00 | 1
2015-09-05 14:00:00 | 1
2015-09-05 16:00:00 | 1
2015-09-05 18:00:00 | 1
2015-09-05 20:00:00 | 1
2015-09-05 22:00:00 | 1

With that I want to have the following result:

TIMESTAMP           | count
2015-09-01 00:00:00 | 12
2015-09-02 06:00:00 | 6
2015-09-03 12:00:00 | 6
2015-09-04 18:00:00 | 12

For sure the interval can be more complicated, the time range can be very big and the data table is also a big table.

This needs to handle months where nearly every month have a different amount of days incl. leap year and also dst changes where a day can have 23, 24 or 25 hours. (Means a one day interval is different than a 24 hours interval)

It would be really really helpful if someone has a solution or can me point to the right direction for that kind of problem.

Thanks!

PS: I have a script that creates SQL an expression in base of a given db column, the start, end and interval objects but it only works for very simple intervals like P1D. I don't past it here as I don't want to ping all great brains into a non working direction that I already have ;)

What I have right now but it doesn't work with mixed intervals. Examples:

Timezone handling:

if ($db->getTimezone()->getName() !== $start->getTimezone()->getName()) {
    $col = 'CONVERT_TZ(' . $col
        . ', ' . $this->quote($this->getTimezone()->getName())
        . ', ' . $this->quote($start->getTimezone()->getName())
        . ')';
}

P1M:

$m = ($interval->y * 12) + $interval->m;
if ($m) {
    if ($m > 1) {
        $mod  = $start->format('Ym') % $m;
        $mod  = $mod ? ' + ' . $mod : '';
        $expr = 'EXTRACT(YEAR_MONTH FROM ' . $col . ')';
        $expr = $mod ? '(' . $expr . $mod . ')' : $expr;
        $expr = ' - INTERVAL ' . $expr . ' % ' . $m . ' MONTH';
        $sqlIntervalMonth = $expr;
    }
    $sqlIntervalDay = ' - INTERVAL DAY(' . $col . ') - 1 DAY';
    if ($start->format('d') > 1) {
        $sqlIntervalDay .= ' + INTERVAL ' . ($start->format('d') - 1) . ' DAY';
    }
}

P1D:

$d = $interval->d;
if ($d) {
    $days = $start->diff(new DateTime('0000-00-00'))->days;
    $mod  = $days % $d;
    $mod  = $mod ? ' + ' . $mod : '';
    $expr = 'TO_DAYS(' . $col . ')';
    $expr = $mod ? '(' . $expr . $mod . ')' : $expr;
    $expr = ' - INTERVAL ' . $expr . ' % ' . $d  . ' DAY';
    $sqlIntervalDay = $expr;
}

EDIT 1: pointed out timezone, dst and leap year requirement. EDIT 2: added PHP snippets

  • 写回答

2条回答 默认 最新

  • dounan4479 2015-12-17 11:46
    关注

    One idea is to convert the value of the time stamp to seconds and then round that value to the appropriate interval (36 hours in your case). Something like this:

    select min(timestamp) as timestamp, sum(count)
    from t
    group by floor(to_seconds(timestamp) / 60 * 60 * 36)  -- * 60 * 60 * 36
    order by timestamp;
    

    This uses the min() of the datetime value, because you seem to have that value in the table. Alternatively, you can convert the rounded seconds back to a datetime value.

    评论

报告相同问题?

悬赏问题

  • ¥50 树莓派安卓APK系统签名
  • ¥15 maple软件,用solve求反函数出现rootof,怎么办?
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗