mysql 跨年周问题总结

公司有个需求,针对数据 按天/周/季度/年的纬度进行统计

SELECT
        <if test="type != null">
            <choose>
# 天  2020-07-31
                <when test="type == 1">DATE(t.first_public_date) </when>
# 周  2020-51
                <when test="type == 2">date_format(t.first_public_date, '%x-%v') </when>
#月  2020-10
                <when test="type == 3">DATE_FORMAT(t.first_public_date,'%Y-%m')</when>
#季度 2020-04
                <when test="type == 4">CONCAT(YEAR(t.first_public_date),'-',QUARTER(t.write_date))</when>
#年  2020
                <when test="type == 5">YEAR(t.first_public_date)</when>
            </choose>
            periodName,
        </if>
        COUNT(DISTINCT id) `count` FROM article t
        <where>
            article_status=10
            <choose >
                <when test="totalDate !=null">
                    AND first_public_date =#{totalDate}
                </when>
                <otherwise>
                    AND first_public_date is not null
                </otherwise>
            </choose>
            <if test="totalDateStart !=null">
                AND first_public_date>=#{totalDateStart}
            </if>
            <if test="totalDateEnd !=null">
                <![CDATA[ AND first_public_date<#{totalDateEnd} ]]>
            </if>
        </where>
        GROUP BY periodName
        ORDER BY periodName DESC

mysql日期格式化参数介绍如下:

参考文章; https://blog.csdn.net/u010004317/article/details/54426836

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微妙
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

用mysql统计出来的周与java计算出来的周有差异, 比如 2016-12-02 ,  2014-12-29 得到的当年周是有出入的,

不能使用mysql统计的周进行统计,否则转换java对象时,当周计算出的日期数据对不上,

解决方案:

用周统计时,不要使用周,使用每周一进行分组,在到java中通过周一的日期计算当周数

select
date_add('1900-01-01',interval floor(datediff(first_public_date,'1900-01-01')/7)*7 day) as week_start,
COUNT(id) from article
WHERE first_public_date>='2016-12-26'
AND first_public_date<'2017-01-16'
group by week_start;

java转换日期结果

    private String buildPeriodName(Integer queryType, String periodName) {
        ArticleTotalEnum.Type type = ArticleTotalEnum.Type.getEnumOfCode(queryType);
        if (StrUtil.isNotBlank(periodName)) {
            switch (type) {
                case WEEK:
                    String[] weekNum = StrUtil.split(periodName, "-");
                    if (weekNum.length > 1) {
                        StringBuilder weekSb = new StringBuilder("");
                        Integer year = new Integer(weekNum[0]);
                        Integer week = new Integer(weekNum[1]);
                        Calendar calendar = CalendarUtil.calendar();
                        calendar.set(Calendar.YEAR, year);
                        calendar.set(Calendar.WEEK_OF_YEAR, week);
                        Date begin = CalendarUtil.beginOfWeek(calendar).getTime();
                        Date end = CalendarUtil.endOfWeek(calendar).getTime();
                        weekSb.append(DateUtil.format(begin, "yyyy-MM-dd"));
                        weekSb.append("~" + DateUtil.format(end, "yyyy-MM-dd"));
                        periodName = weekSb.toString();
                    }
                    break;
                case MONTH:
                    String[] monthNum = StrUtil.split(periodName, "-");
                    if (monthNum.length > 1) {
                        StringBuilder monthSb = new StringBuilder("");
                        monthSb.append(monthNum[0] + "年");
                        monthSb.append(monthNum[1] + "月");
                        periodName = monthSb.toString();
                    }
                    break;
                case QUARTER:
                    String[] quarterNum = StrUtil.split(periodName, "-");
                    if (quarterNum.length > 1) {
                        StringBuilder quarterSb = new StringBuilder("");
                        quarterSb.append(quarterNum[0] + "年");
                        quarterSb.append("第" + NumberChineseFormatter.format(new Double(quarterNum[1]), false) + "季度");
                        periodName = quarterSb.toString();
                    }
                    break;
                case YEAR:
                    periodName = periodName + "年度";
                    break;
                default:
            }
        }
        return periodName;
    }