MySQL时间类型及获取、展示处理详解数据库

@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")   
public Timestamp getCreate_time() {   
        return create_time;   
} 

import java.util.Date;  
import java.util.Calendar;  
import java.sql.Timestamp;  
import java.text.DateFormat;  
import java.text.SimpleDateFormat;  
import java.util.Locale;  
class Datetest{  
/**  
  *method 将字符串类型的日期转换为一个timestamp（时间戳记java.sql.Timestamp）  
   dateString 需要转换为timestamp的字符串  
   dataTime timestamp  
  */  
public final static java.sql.Timestamp string2Time(String dateString)  
  throws java.text.ParseException {  
   DateFormat dateFormat;  
  dateFormat = new SimpleDateFormat("yyyy-MM-dd kk:mm:ss.SSS", Locale.ENGLISH);//设定格式  
  //dateFormat = new SimpleDateFormat("yyyy-MM-dd kk:mm:ss", Locale.ENGLISH);  
  dateFormat.setLenient(false);  
  java.util.Date timeDate = dateFormat.parse(dateString);//util类型  
  java.sql.Timestamp dateTime = new java.sql.Timestamp(timeDate.getTime());//Timestamp类型,timeDate.getTime()返回一个long型  
  return dateTime;  
}  
/**  
  *method 将字符串类型的日期转换为一个Date（java.sql.Date）  
   dateString 需要转换为Date的字符串  
   dataTime Date  
  */  
public final static java.sql.Date string2Date(String dateString)  
  throws java.lang.Exception {  
  DateFormat dateFormat;  
  dateFormat = new SimpleDateFormat("yyyy-MM-dd", Locale.ENGLISH);  
  dateFormat.setLenient(false);  
  java.util.Date timeDate = dateFormat.parse(dateString);//util类型  
  java.sql.Date dateTime = new java.sql.Date(timeDate.getTime());//sql类型  
  return dateTime;  
}  
 
public static void main(String[] args){  
  Date da = new Date();  
  //注意：这个地方da.getTime()得到的是一个long型的值  
  System.out.println(da.getTime());  
 
  //由日期date转换为timestamp  
 
  //第一种方法：使用new Timestamp(long)  
  Timestamp t = new Timestamp(new Date().getTime());  
  System.out.println(t);  
 
  //第二种方法：使用Timestamp(int year,int month,int date,int hour,int minute,int second,int nano)  
  Timestamp tt = new Timestamp(Calendar.getInstance().get(  
      Calendar.YEAR) - 1900, Calendar.getInstance().get(  
      Calendar.MONTH), Calendar.getInstance().get(  
      Calendar.DATE), Calendar.getInstance().get(  
      Calendar.HOUR), Calendar.getInstance().get(  
      Calendar.MINUTE), Calendar.getInstance().get(  
      Calendar.SECOND), 0);  
  System.out.println(tt);  
 
  try {  
   　 String sToDate = "2005-8-18";//用于转换成java.sql.Date的字符串  
      String sToTimestamp = "2005-8-18 14:21:12.123";//用于转换成java.sql.Timestamp的字符串  
      Date date1 = string2Date(sToDate);  
      Timestamp date2 = string2Time(sToTimestamp);  
   System.out.println("Date:"+date1.toString());//结果显示  
   System.out.println("Timestamp:"+date2.toString());//结果显示  
  }catch(Exception e) {  
   e.printStackTrace();  
  }  
}  
}

SELECT COUNT(*) FROM USER WHERE registerDate >= CURDATE() AND registerDate < DATE_SUB(CURDATE(),INTERVAL -1 DAY);

--今天是2016年08月01日。 
date_sub('2016-08-01',interval 1 day) --表示 2016-07-31 
date_sub('2016-08-01',interval 0 day) --表示 2016-08-01 
date_sub('2016-08-01',interval -1 day) --表示 2016-08-02 
 
date_sub(curdate(),interval 1 day) --表示 2016-07-31 
date_sub(curdate(),interval -1 day) --表示2016-08-02 
date_sub(curdate(),interval 1 month) --表示 2016-07-01 
date_sub(curdate(),interval -1 month) --表示 2016-09-01 
date_sub(curdate(),interval 1 year) --表示 2015-08-01 
date_sub(curdate(),interval -1 year) --表示 2017-08-01

<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>

<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>

--特定时间秒查询 
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') 
--查询最近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')