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