mysql获取大于指定日期不准确 mysql获取当前日期格式时间

转载

落花流水人家 2024-08-08 22:12:49

文章标签 mysql获取大于指定日期不准确 mysql 取服务器时间格式 java sql 字符串 文章分类 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获取大于指定日期不准确 mysql获取当前日期格式时间_字符串

数据库日期大小比较操作

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 位

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。