mysql 取服务器时间格式_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() {returncreate_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型

returndateTime;

}/**

*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类型

returndateTime;

}

public staticvoidmain(String[] args){

Date da= newDate();//注意：这个地方da.getTime()得到的是一个long型的值

System.out.println(da.getTime());//由日期date转换为timestamp

//第一种方法：使用new Timestamp(long)

Timestamp t = new Timestamp(newDate().getTime());

System.out.println(t);//第二种方法：使用Timestamp(int year,int month,int date,int hour,int minute,int second,int nano)

Timestamp tt = newTimestamp(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官网也是不承认时间直接相加减的(如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(),interval1 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查询的时候依旧会变成这样？

and DATE_FORMAT(info.sale_date,'%Y-%m-%d') >=#{saleDateStart}

and DATE_FORMAT(info.sale_date,'%Y-%m-%d') <=#{saleDateStart}

原因：问题就出在   #{时间字段} 这个地方，虽然这个时候查询的时候传入的时间字段值 是  年月日格式  没有时分秒  但是mybatis在生成sql的时候 ，依旧将 年月日进行格式化 成了 年月日 时分秒类型。

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

and DATE_FORMAT(info.sale_date,'%Y-%m-%d') >= DATE_FORMAT(#{saleDateStart},'%Y-%m-%d')

and DATE_FORMAT(info.sale_date,'%Y-%m-%d') <= DATE_FORMAT(#{saleDateStart},'%Y-%m-%d')

其余类似比较SQL语句：

--特定时间秒查询

SELECTid,

date_0FROMworksheet_data_30WHEREDATE_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_0FROMworksheet_data_30WHEREDATE_FORMAT( date_0,'%Y-%m-%d %H:%i' ) = DATE_FORMAT( '2019-06-05 09:35:06', '%Y-%m-%d %H:%i')--特定小时查询

SELECTid,

date_0FROMworksheet_data_30WHEREDATE_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天 按天分组统计数据

SELECTcreate_date,

IFNULL(sum(security_code_total), 0) createSCNum,

IFNULL(sum(print_num), 0) printNumFROMsecurity_code_configWHEREtid= 'ten_pisen'

ANDDATE_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) printNumFROMsecurity_code_configWHEREtid= 'ten_pisen'

ANDDATE_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_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 位