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

文章目录

• >Mysql根据时间的月份进行分组查询
• • >>list的forEach用法
• >[mybatis]时间范围 处理时间格式问题 + 查询当天 本月 本年 + 按当天 当月 范围 查询 分组...
• >mysql日期和字符串相互转化
• • >>涉及的函数
  • • 时间转字符串
    • 时间转时间戳
    • 字符串转时间
    • 时间戳转时间
    • 时间戳转字符串
  • 附表

>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 numfrom storage_in_stockwhere 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 >= #{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') >= #{saleDateStart}
if>
<if test="saleDateEnd != null">and DATE_FORMAT(info.sale_date,'%Y-%m-%d') <= #{saleDateStart}
if>

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

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

解决方法：

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

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

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

本秒查询:

SELECTid,date_0
FROMworksheet_data_30 
WHEREDATE_FORMAT( date_0, '%Y-%m-%d %H:%i:%S' ) = DATE_FORMAT( '2019-06-05 09:35:06', '%Y-%m-%d %H:%i:%S' )

本分查询

SELECTid,date_0
FROMworksheet_data_30 
WHEREDATE_FORMAT( date_0, '%Y-%m-%d %H:%i' ) = DATE_FORMAT( '2019-06-05 09:35:06', '%Y-%m-%d %H:%i' )

本时查询

SELECTid,date_0
FROMworksheet_data_30 
WHEREDATE_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天 按天分组统计数据

SELECTcreate_date,IFNULL(sum(security_code_total), 0) createSCNum,IFNULL(sum(print_num), 0) printNum
FROMsecurity_code_config
WHEREtid = 'ten_pisen'
AND DATE_FORMAT(create_date,'%Y-%m-%d') <= DATE_FORMAT('2019-03-12','%Y-%m-%d')
ANDDATE_FORMAT(create_date,'%Y-%m-%d') >= DATE_FORMAT('2019-03-01','%Y-%m-%d')
GROUP BY DATE_FORMAT(create_date,'%Y-%m-%d')

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

SELECTcreate_date,IFNULL(sum(security_code_total), 0) createSCNum,IFNULL(sum(print_num), 0) printNum
FROMsecurity_code_config
WHEREtid = 'ten_pisen'
AND DATE_FORMAT(create_date,'%Y-%m-%d') <= DATE_FORMAT('2019-03-12','%Y-%m-%d')
ANDDATE_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）取值范围。

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

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！