Mysql根据时间/月份进行分组,附SQL中日期字符串互转

>Mysql根据时间的月份进行分组查询

sql:

SELECT DATE_FORMAT(create_time,'%Y%m') months FROM student GROUP BY months

mapper中：

List<String> hasmonths();

xml中：

<select id="hasmonths" resultType="java.lang.String">
     SELECT DATE_FORMAT(create_time,'%Y%m') months FROM biao  GROUP BY months
</select>

实际例子:

String format = "%Y-%m";
或者
String format = "%Y-%m-%d";

mapper

List<StatisticsObjectAndDoubleDTO> findInStock(String storageCode, String storehouseCode, Date parseDate, @Param("format") String format, List<String> storageCodeList);

xml

<select id="findInStock" resultType="com.boot.reservation.entity.dto.StatisticsObjectAndDoubleDTO">
        select min(ifnull(DATE_FORMAT(unload_end_time,#{format}),"1987-01-01")) as name,ifnull(sum(settlement_num),0) as num
        from storage_in_stock
        where status = 02
        <if test="storageCode != null and storageCode != ''">
            and granary_address = #{storageCode}
        </if>
        <if test="storehouseCode != null and storehouseCode != ''">
            and house = #{storehouseCode}
        </if>
        <if test="parseDate != null">
            and unload_end_time &gt;= #{parseDate}
        </if>
        <if test="storageCodeList != null and storageCodeList.size>0">
            and granary_address in
            <foreach collection="storageCodeList" item="granaryAddress" open="(" separator="," close=")">
                #{granaryAddress}
            </foreach>
        </if>
        group by DATE_FORMAT(unload_end_time,#{format}) order by DATE_FORMAT(unload_end_time,#{format})
    </select>

实体类:

package com.boot.reservation.entity.dto;

/**
 * 统计页
 */
public class StatisticsObjectAndDoubleDTO {

    private Object name;

    private Double num;
    get/set(略)

对返回数据进行处理:

>>list的forEach用法

List<StatisticsObjectAndDoubleDTO> inStockList = null;

if (inStockList != null && !inStockList.isEmpty()) {
            Map<String,Double> inStockMap = new HashMap<>();
            inStockList.forEach(inStock -> {
                inStockMap.put((String) inStock.getName(),inStock.getNum());
            });
            statisticsChartVO.setInStockMap(inStockMap);
        }

>[mybatis]时间范围 处理时间格式问题 + 查询当天 本月 本年 + 按当天 当月 范围 查询 分组…

1. mybatis中查询时间范围处理：

例如2018-05-22 ~2018-05-23

则查出来的数据仅能查到2018-05-22的，查不到2018-05-23的数据！

为什么会这样？

明明时间字段在spring MVC中已经处理了，已经没有2018-05-23 00:00:00.0，已经获取到了日期，去掉了时分秒，为什么传入mybatis进行sql查询的时候依旧会变成这样？

<if test="saleDateStart != null">
    and DATE_FORMAT(info.sale_date,'%Y-%m-%d') &gt;= #{saleDateStart}
</if>
<if test="saleDateEnd != null">
    and DATE_FORMAT(info.sale_date,'%Y-%m-%d') &lt;= #{saleDateStart}
</if>

问题就出在 #{时间字段} 这个地方，

虽然这个时候查询的时候传入的时间字段值 是 年月日格式 没有时分秒 但是mybatis在生成sql的时候 ，依旧将 年月日进行格式化 成了 年月日 时分秒 类型。

解决方法：

<if test="saleDateStart != null">
  and DATE_FORMAT(info.sale_date,'%Y-%m-%d') &gt;= DATE_FORMAT(#{saleDateStart},'%Y-%m-%d')
</if>
<if test="saleDateEnd != null">
    and DATE_FORMAT(info.sale_date,'%Y-%m-%d') &lt;= DATE_FORMAT(#{saleDateStart},'%Y-%m-%d')
</if>

这样就能保证 mybatis在处理sql的时候，按照年月日的格式去处理参数！！！

2. 查询 本秒 本分钟 本小时 本天 本月 本年

本秒查询:

SELECT
    id,
    date_0
FROM
    worksheet_data_30 
WHERE
    DATE_FORMAT( date_0, '%Y-%m-%d %H:%i:%S' ) = DATE_FORMAT( '2019-06-05 09:35:06', '%Y-%m-%d %H:%i:%S' )

本分查询

SELECT
    id,
    date_0
FROM
    worksheet_data_30 
WHERE
    DATE_FORMAT( date_0, '%Y-%m-%d %H:%i' ) = DATE_FORMAT( '2019-06-05 09:35:06', '%Y-%m-%d %H:%i' )

本时查询

SELECT
    id,
    date_0
FROM
    worksheet_data_30 
WHERE
    DATE_FORMAT( date_0, '%Y-%m-%d %H' ) = DATE_FORMAT( '2019-06-05 09:35:06', '%Y-%m-%d %H' )

当天数据

select * from security_code_config  where DATE_FORMAT(create_date,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')

本月数据

select * from security_code_config  where DATE_FORMAT(create_date,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m')

本年数据

select * from security_code_config  where DATE_FORMAT(create_date,'%Y') = DATE_FORMAT(NOW(),'%Y')

3. 查询时间分组

查询最近7天 按天分组统计数据

SELECT
    create_date,
    IFNULL(sum(security_code_total), 0) createSCNum,
    IFNULL(sum(print_num), 0) printNum
FROM
    security_code_config
WHERE
    tid = 'ten_pisen'
AND 
    DATE_FORMAT(create_date,'%Y-%m-%d') <= DATE_FORMAT('2019-03-12','%Y-%m-%d')
AND
    DATE_FORMAT(create_date,'%Y-%m-%d') >= DATE_FORMAT('2019-03-01','%Y-%m-%d')
GROUP BY DATE_FORMAT(create_date,'%Y-%m-%d')

查询最近6个月 按月分组统计数据

SELECT
    create_date,
    IFNULL(sum(security_code_total), 0) createSCNum,
    IFNULL(sum(print_num), 0) printNum
FROM
    security_code_config
WHERE
    tid = 'ten_pisen'
AND 
    DATE_FORMAT(create_date,'%Y-%m-%d') <= DATE_FORMAT('2019-03-12','%Y-%m-%d')
AND
    DATE_FORMAT(create_date,'%Y-%m-%d') >= DATE_FORMAT('2018-10-01','%Y-%m-%d')
GROUP BY DATE_FORMAT(create_date,'%Y-%m')

>mysql日期和字符串相互转化

时间、字符串、时间戳之间的互相转换很常用，但是几乎每次使用时候都喜欢去搜索一下用法；本文整理一下三者之间的 转换（即：date转字符串、date转时间戳、字符串转date、字符串转时间戳、时间戳转date，时间戳转字符串）用法，方便日后学习和查阅；

>>涉及的函数

date_format(date, format) 函数，MySQL日期格式化函数date_format()

unix_timestamp() 函数

str_to_date(str, format) 函数

from_unixtime(unix_timestamp, format) 函数，MySQL时间戳格式化函数from_unixtime

时间转字符串

select date_format(now(), '%Y-%m-%d');  
  
#结果：2017-01-05  

时间转时间戳

select unix_timestamp(now());  
  
#结果：1452001082  

字符串转时间

select str_to_date('2016-01-02', '%Y-%m-%d %H');  
  
#结果：2017-01-02 00:00:00  

时间戳转时间

select from_unixtime(1451997924);  
  
#结果：2017-01-05 20:45:24 

时间戳转字符串

select from_unixtime(1451997924,'%Y-%d');  
  
//结果：2017-01-05 20:45:24  

附表

MySQL日期格式化（format）取值范围。

	值	含义
秒	%S、%s	两位数字形式的秒（ 00,01, ..., 59）
分	%I、%i	两位数字形式的分（ 00,01, ..., 59）
小时	%H	24小时制，两位数形式小时（00,01, ...,23）
	%h	12小时制，两位数形式小时（00,01, ...,12）
	%k	24小时制，数形式小时（0,1, ...,23）
	%l	12小时制，数形式小时（0,1, ...,12）
	%T	24小时制，时间形式（HH:mm:ss）
	%r	12小时制，时间形式（hh:mm:ss AM 或 PM）
	%p	AM上午或PM下午
周	%W	一周中每一天的名称（Sunday,Monday, ...,Saturday）
	%a	一周中每一天名称的缩写（Sun,Mon, ...,Sat）
	%w	以数字形式标识周（0=Sunday,1=Monday, ...,6=Saturday）
	%U	数字表示周数，星期天为周中第一天
	%u	数字表示周数，星期一为周中第一天
天	%d	两位数字表示月中天数（01,02, ...,31）
	%e	数字表示月中天数（1,2, ...,31）
	%D	英文后缀表示月中天数（1st,2nd,3rd ...）
	%j	以三位数字表示年中天数（001,002, ...,366）
月	%M	英文月名（January,February, ...,December）
	%b	英文缩写月名（Jan,Feb, ...,Dec）
	%m	两位数字表示月份（01,02, ...,12）
	%c	数字表示月份（1,2, ...,12）
年	%Y	四位数字表示的年份（2015,2016...）
	%y	两位数字表示的年份（15,16...）
文字输出	%文字	直接输出文字内容

原文链接:https://www.cnblogs.com/wangyongwen/p/6265126.html