如何遍历日期和周，月或自定义季度分组？ [英] How can I loop through an array of dates and group by week, month or custom quarter?

问题描述

我正在根据日期范围和日期顺序从数据库中提取行，例如

I am pulling out rows from a database based on date range and order by date, for example

SELECT date, code FROM records WHERE personid = '133'AND date >= '2017-02-10' AND date <= '2017-06-13' AND code IN ('P','T') ORDER BY date ASC 

我喜欢以特定方式对这些记录进行分组或排序。

These records I like to group or order in particular ways.

一种方法是按连续日期对记录进行分组，为此，我具有以下功能：

One way has grouped the records by consecutive dates, for this, I have the following function:

 function getRecordsWithIntervals(array $records, $interval_size)
            {
                $intervals = array();
                foreach (array_chunk($records, $interval_size) as $interval_records) {
                    $first_record = reset($interval_records);
                    $interval = array(
                        'start' => $first_record['date'],
                        'codes' => array_column($interval_records, 'code'),
                        'count' => count($interval_records),

                    );
                    $intervals[] = $interval;
                }
                return $intervals;
            }

连续5天，我都会调用该函数，例如

For a consecutive 5 days I call the function, like

getRecordsWithIntervals($records, 5);

但是现在我想添加每周，每月或自定义季度的功能。因此，例如：

But now I like to add the feature of the weekly, monthly or custom quarter. So for example:

getRecordsWithIntervals($records, 'weekly');

在2017年2月2日的日期范围内，每个Mo-Sunday的订单/分组$记录数-6月13日2017 ???

Would order/grouped $records per Mo-Sunday interval within the date range 2 Feb 2017 - 13 jun 2017???

这将是一些示例数据

This would be some sample data

我想绘制此Google图表

I like to plot this Google chart

这将是生成的JS 2月和3月的PHP

And this would be the generated JS with PHP for Feb and March

['Feb 6-12, 2017’,1,’1’],
['Feb 13-19, 2017’,4,’4’],
['Feb 20-26, 2017’,4,’4’],
['Feb 27-??, 2017’,5,’5’],
['Mar 6-12, 2017',5,'5'],
['Mar 13-19, 2017',5,'5'],
['Mar 20-26, 2017’,3,’3’],
['Mar 27-xx, 2017’,4,’4’],

推荐答案

MySQL可能是比较合适的地方以理智的方式将您的数据分组。例如，如果您想获取星期数和月份，可以将这些项目添加到选择子句中：

MySQL might be a better place to conceptually form groups with your data. For example, if you wanted to have the week number and month on tap you could just add these items to your select clause:

SELECT
    date,
    WEEK(date) AS week,
    MONTH(date) AS month,
    code
FROM records
WHERE
    personid = '133' AND
    date BETWEEN '2017-02-10' AND '2017-06-13' AND
    code IN ('P','T')
ORDER BY date;

这篇关于如何遍历日期和周，月或自定义季度分组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！