有時(shí)需要按周統(tǒng)計(jì)信息，或者將沒有數(shù)據(jù)的日期補(bǔ)齊返給前端形成統(tǒng)計(jì)圖：條形圖、餅圖等

一、java方式

獲取當(dāng)前日期上一個(gè)自然周內(nèi)數(shù)據(jù)，注意默認(rèn)的是星期六到星期一，如果想讓星期一是第一天~星期日，就要獲取本周的星期日，上周的星期一，再通過sql傳入計(jì)算的開始時(shí)間截止時(shí)間：

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class DateUtil {

    public static final String DATE_PATTERN = "yyyy-MM-dd";
    public static final String DATE_TIME_PATTERN = "yyyy-MM-dd HH:mm:ss";

    /**
     * 獲取星期的第一天，最后一天
     * n為推遲的周數(shù)，1本周，-1向前推遲一周，2下周，依次類推
     * m為星期幾
     */
    public static String getWeek(int n, int m) {
        Calendar cal = Calendar.getInstance();
        cal.add(Calendar.DATE, n * 7);
        cal.set(Calendar.DAY_OF_WEEK, m);
        return new SimpleDateFormat(DateUtil.DATE_PATTERN).format(cal.getTime());
    }

    /**
     * 獲取兩個(gè)日期（含時(shí)分秒）相差的天數(shù)，包含今天
     *
     * @param startDate
     * @param endDate
     * @return
     * @throws ParseException
     */
    public static int dateBetweenIncludeToday(Date startDate, Date endDate) throws ParseException {
        return dateBetween(startDate, endDate) + 1;
    }

    public static int dateBetween(Date startDate, Date endDate) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        startDate = sdf.parse(sdf.format(startDate));
        endDate = sdf.parse(sdf.format(endDate));
        Calendar cal = Calendar.getInstance();
        cal.setTime(startDate);
        long time1 = cal.getTimeInMillis();
        cal.setTime(endDate);
        long time2 = cal.getTimeInMillis();
        long between_days = (time2 - time1) / (1000 * 3600 * 24);
        return Integer.parseInt(String.valueOf(between_days));
    }

    /**
     * 將秒值轉(zhuǎn)換為時(shí)分秒工具類
     *
     * @param second
     * @return
     */
    public static String secToTime(int second) {
        int hour = second / 3600;
        int minite = second % 3600 / 60;
        int sec = second % 60;
        String timeStr = "";
        if (hour > 0) {
            timeStr += hour + "小時(shí)";
        }
        if (minite > 0) {
            timeStr += minite + "分";
        }
        if (sec > 0) {
            timeStr += sec + "秒";
        }
        return timeStr;
    }
     public static void main(String[] args) {
        String startTime = DateUtil.getWeek(-1, Calendar.MONDAY);
        String endTime= DateUtil.getWeek(0, Calendar.SUNDAY);
    }
}

二、數(shù)據(jù)庫(kù)方式

統(tǒng)計(jì)上一周的每天用戶注冊(cè)數(shù)量，沒有數(shù)量的補(bǔ)齊日期，mysql為例：

  SELECT
            t1.timeDay as date,ifnull(t2.num,0) num
        FROM (
            SELECT
                date_format(lastWeek.`timeDay`,'%m-%d') as 'timeDay'
            FROM (
                select DATE_SUB(NOW(),interval 13 day) as 'timeDay'
                UNION ALL
                select DATE_SUB(NOW(),interval 12 day) as 'timeDay'
                UNION ALL
                select DATE_SUB(NOW(),interval 11 day) as 'timeDay'
                UNION ALL
                select DATE_SUB(NOW(),interval 10 day) as 'timeDay'
                UNION ALL
                select DATE_SUB(NOW(),interval 9 day) as 'timeDay'
                UNION ALL
                select DATE_SUB(NOW(),interval 8 day) as 'timeDay'
                UNION ALL
                select DATE_SUB(NOW(),interval 7 day) as 'timeDay'
                UNION ALL
                select DATE_SUB(NOW(),interval 6 day) as 'timeDay'
                UNION ALL
                select DATE_SUB(NOW(),interval 5 day) as 'timeDay'
                UNION ALL
                select DATE_SUB(NOW(),interval 4 day) as 'timeDay'
                UNION ALL
                select DATE_SUB(NOW(),interval 3 day) as 'timeDay'
                UNION ALL
                select DATE_SUB(NOW(),interval 2 day) as 'timeDay'
                UNION ALL
                select DATE_SUB(NOW(),interval 1 day) as 'timeDay'
            ) as lastWeek
            WHERE YEARWEEK(lastWeek.`timeDay`,1) = YEARWEEK(NOW(),1)-1
        ) as t1
        LEFT JOIN (
            SELECT
                count( DISTINCT user_id ) num,
                DATE_FORMAT( a.create_time, "%m-%d" )  date
            FROM
               user_info a
             
            GROUP BY
                DATE_FORMAT (a.create_time,"%Y-%m-%d")
        ) as t2 ON t1.timeDay = t2.date
        ORDER BY t1.timeDay