Let me show you what the requirements are like:

Display the data of each node according to the time interval to form a curve.

My first method is to use mysql functions:

SELECT
        @a :=@a + 1 AS `index`,
        DATE_FORMAT(ADDDATE( #{startDate} ,INTERVAL @a DAY),'%Y-%m-%d') AS date
        FROM
        mysql.help_topic,(SELECT @a:=-1) temp
        WHERE
        @a <  (YEAR( #{endDate} )-YEAR( #{startDate} ))*365 + (MONTH( #{endDate} )- MONTH( #{startDate} ))*30  + (DAY( #{endDate} )- DAY( #{startDate} ))

The first is to make statistics on a daily basis. First, use the help of mysql_ Topic table to generate list data with sequence number as key and time as value.

  Perform the following:

    

  Then connect our own data table (t1 is the data table alias generated above):

 LEFT JOIN
        (
        SELECT
        date_format(sel.login_time, '%Y-%m-%d') AS dateDay,
        count(*) as count
        FROM
        xxx_log sel
        WHERE
            sel.login_time > #{startDate} 
            AND sel.login_time < #{endDate}
        group by
        date_format(sel.login_time, '%Y-%m-%d')) AS t2
        ON t1.date = t2.dateDay

Finally, use select to merge the whole sql, and judge whether it is empty in the query field count. If it is empty, fill in 0, and finally sort according to the field.

SELECT
        c.date AS dateMonth,
        IFNULL(c.count,0) AS countMonth FROM (
        SELECT * FROM ( {First paragraph sql} ) AS t1
        LEFT JOIN
        ({The second paragraph sql}) AS t2  
        ON t1.date = t2.dateMonth) AS c ORDER BY DATEMONTH desc

Perform the following:

  result:

  Perfect ~ ~ from September 1, 20 to October 11, 21~~~~~

  If it is done at monthly or annual intervals, modify the sql and let me write a reference.

Year:

				
SELECT
        @a :=@a + 1 AS `index`,
        DATE_FORMAT(ADDDATE( #{startTime} ,INTERVAL @a MONTH),'%Y-%m') AS date
        FROM
        mysql.help_topic,(SELECT @a:=-1) temp
        WHERE
        @a < (YEAR( #{endTime} )-YEAR( #{startTime} ))*12+ MONTH( #{endTime} )- MONTH( #{startTime} )
        				

The second code changes the time format to yyyy mm, SQL:

  result:

  It's even simpler in:

  Although this method seems to be completed by operating the database, it is also restrictive. For example, the first paragraph of sql is actually mysql.help for query_ Topic table. The data in this table is limited

  Therefore, the sequence number generated by the first paragraph of sql is limited. If the number exceeds this number, 0 cannot be supplemented. The solution is to change a table and make a special serial number table for association, and then process it in the code. I use the second method here.

The second method is to slice the time by year / month / day:

/**
	 * Date range - slice
	 * @param startDate Start date
	 * @param endDate End date
	 * @param type Slice type 1-year 2-month 3-day 4-hour
	 * @return Slice date
	 */
	public static List<String> sliceUpDateRange(String startDate, String endDate ,int type ) {
		List<String> rs = new ArrayList<>();

		try {
			int dt = Calendar.DATE;
			String pattern = "yyyy-MM-dd";
			if (type==1) {
				pattern = "yyyy";
				dt = Calendar.YEAR;
			} else if (type==2) {
				pattern = "yyyy-MM";
				dt = Calendar.MONTH;
			} else if (type==3) {
				pattern = "yyyy-MM-dd";
				dt = Calendar.DATE;
			}else if (type==4) {
				pattern = "yyyy-MM-dd HH";
				dt = Calendar.HOUR_OF_DAY;
			}
			SimpleDateFormat sd = new SimpleDateFormat(pattern);
			Calendar sc = Calendar.getInstance();
			Calendar ec = Calendar.getInstance();
			sc.setTime(sd.parse(startDate));
			ec.setTime(sd.parse(endDate));
			while (sc.compareTo(ec) < 1) {
				rs.add(sd.format(sc.getTime()));
				sc.add(dt, 1);
			}
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return rs;
	}

Use the above method to put the cut date into the sql union. Hey hey, look at the code (take every day as an example):

select
	od.date,od.count from
		(SELECT DATE_FORMAT(SIGN_TIME, '%Y-%m-%d') as date,COUNT(*) as count
		FROM xxxx_log
		<where> 
            STATE = #{state}
			AND SIGN_TIME <![CDATA[ > ]]> #{param.createStartTime, jdbcType = VARCHAR}
			AND SIGN_TIME <![CDATA[ < ]]> #{param.createEndTime, jdbcType = VARCHAR}
		</where>
		group by DATE_FORMAT(SIGN_TIME, '%Y-%m-%d')
		union
		<foreach collection="list" item="item" index="index" separator="union">
			(select #{item},0)
		</foreach>
		)as od GROUP BY od.date ORDER BY od.date asc

The problem is that sql will be very long by the union. The last method is to supplement data in the code, but it can also be implemented by loop traversal (regardless of performance)

The second advantage of time slicing is that union can achieve year / month / day / hour compatibility, and the code is relatively neat