MySQL时间类型及获取、展示处理

MySQL时间格式

　　mysql所支持的日期时间类型有：DATETIME、 TIMESTAMP、DATE、TIME、YEAR。

　　几种类型比较如下：

日期时间类型	占用空间	日期格式	最小值	最大值	零值表示
DATETIME	8 bytes	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59	0000-00-00 00:00:00
TIMESTAMP	4 bytes	YYYY-MM-DD HH:MM:SS	19700101080001	2038 年的某个时刻	00000000000000
DATE	4 bytes	YYYY-MM-DD	1000-01-01	9999-12-31	0000-00-00
TIME	3 bytes	HH:MM:SS	-838:59:59	838:59:59	00:00:00
YEAR	1 bytes	YYYY	1901	2155	0000

　　DATETIME

　　DATETIME 用于表示年月日时分秒，是 DATE 和 TIME 的组合，并且记录的年份（见上表）比较长久。如果实际应用中有这样的需求，就可以使用 DATETIME 类型。

　　TIMESTAMP

TIMESTAMP 用于表示年月日时分秒，但是记录的年份（见上表）比较短暂。
TIMESTAMP 和时区相关，更能反映当前时间。当插入日期时，会先转换为本地时区后再存放；当查询日期时，会将日期转换为本地时区后再显示。所以不同时区的人看到的同一时间是不一样的。
表中的第一个 TIMESTAMP 列自动设置为系统时间（CURRENT_TIMESTAMP）。当插入或更新一行，但没有明确给 TIMESTAMP 列赋值，也会自动设置为当前系统时间。如果表中有第二个 TIMESTAMP 列，则默认值设置为0000-00-00 00:00:00。
TIMESTAMP 的属性受 Mysql 版本和服务器 SQLMode 的影响较大。

　　如果记录的日期需要让不同时区的人使用，最好使用 TIMESTAMP。

　　DATE

　　DATE 用于表示年月日，如果实际应用值需要保存年月日就可以使用 DATE。

　　TIME

　　TIME 用于表示时分秒，如果实际应用值需要保存时分秒就可以使用 TIME。

　　YEAR

　　YEAR 用于表示年份，YEAR 有 2 位（最好使用4位）和 4 位格式的年。默认是4位。如果实际应用只保存年份，那么用 1 bytes 保存 YEAR 类型完全可以。不但能够节约存储空间，还能提高表的操作效率。

不严格语法的时间格式

　　每种日期时间类型都有一个有效值范围，如果超出这个范围，在默认的SQLMode下会报错，并以零值（见上表）存储。

　　插入或更新时，日期时间类型允许“不严格”语法，以DATETIME为例（其他日期时间类型雷同）：

YYYY-MM-DD HH:MM:SS 或 YY-MM-DD HH:MM:SS 格式的字符串。任何符号都可以用作日期部分或时间部分的间隔符。例如：“14-06-18 14:54:10”、“14*06*18 14.54.10”、“14+06+18 14=54=10”是等价的。对于包含日期时间的字符串值，如果月、日、时、分、秒的值小于10，不需要指定两位数。例如：“2014-2-3 2:3:6”、“2014-02-03 02:03:06”是等价的。

YYYYMMDDHHMMSS 或 YYMMDDHHMMSS 格式的字符串。如果字符串对于日期时间类型是合法的就可以解释为日期时间类型。例如：“20140618145410” 和 “140618145410”将被解释为 “2014-06-18 14:54:10” ，但是 “20140618145480” 是不合法的（秒数不合法），将被解释为 “0000-00-00 00:00:00”。

YYYYMMDDHHMMSS 或 YYMMDDHHMMSS 格式的数字。如果该数字对日期时间类型是合法的就可以解释为日期时间类型。例如：“20140618145410” 和 “140618145410” 将被解释为 “2014-06-18 14:54:10” 。数值的长度应为6、8、12、14。如果数值长度是 8 或 14 位长，则假定为 YYYYMMDD 或 YYYYMMDDHHMMSS 格式。如果数值为 6 或 12 位长，则假定为 YYMMDD 或 YYMMDDHHMMSS 格式。

TIMSTAMP返回值格式化：

　　通常日期格式都是以时间戳的形式存放在数据库里，当前端页面通过接口查询时，我们会将一个对象的某些属性查出来返回给页面。例如，某个类里面有个属性

　　Timestamp create_time

　　给这个对象实体类中的get方法加注解：@JsonFormat 来实现格式的自动转换：

　　依赖的jar包：jackson-annotations-2.8.5.jar

@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")

public Timestamp getCreate_time() {

        return create_time;

}

　　加注解前的返回值是一个时间戳，加了之后则按照注解的格式自动格式化，其余类型同理。

Timestamp类的使用

　　我们可以这样声明 Timestamp ts=new Timestamp(new Date().getTime());这样我们就可以得到时间比较具体的一个类型转换。在开发web应用中，针对不同的数据库日期类型，我们需要在我们的程序中对日期类型做各种不同的转换。若对应数据库数据是oracle的Date类型，即只需要年月日的，可以选择使用java.sql.Date类型，若对应的是MSsqlserver数据库的DateTime类型，即需要年月日时分秒的，选择java.sql.Timestamp类型;
　　你可以使用dateFormat定义时间日期的格式，然后进行转换：

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();

  }

}

}

　　注意：Mybatis返回值设置jdbctype是没有这个DATETIME类型的（如下图报错），如果数据库存储的是DATETIME的类型，并且准确显示到年月日时分秒，所以用timestamp。

MySQL时间类型及获取、展示处理-LMLPHP

数据库日期大小比较操作

　　mysql官网也是不承认时间直接相加减的（如CURDATE()+1），虽然会将当前时间转换为20160802，这时候就是比较这一串字符，mysql官网是不承认用这种方式比较时间大小的，因此正确的sql语句：

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

　　此时，就用到了DATE_SUB()这个函数，用法举例：

--今天是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

　　注意：SELECT NOW(),CURDATE(),CURTIME()

　　结果类似：

NOW()	CURDATE()	CURTIME()
2016-08-01 16:25:46	2016-08-01	16:25:46

SQL语句日期格式化

　　案例：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的时候，依旧将年月日进行格式化成了年月日时分秒类型。

　　解决办法：对传入到Mybatis的具体值进行格式化后再比较

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

　　其余类似比较SQL语句：

--特定时间秒查询

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

===============================附录，mysql格式化方法以及时间符号========================

　　date_format(date,'%Y-%m-%d %H:%i:%s') -------------->oracle中的to_char();

　　str_to_date(date,'%Y-%m-%d %H:%i:%s') -------------->oracle中的to_date();
　　format参数的格式有

%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天（0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位