mysql中生日可用类型：1、DATE类型，可表示的日期格式为“YYYY-MM-DD”，在存储时需要3个字节；2、DATETIME类型，可表示的日期格式为“YYYY-MM-DD HH：MM：SS”，在存储时需要8个字节；3、TIMESTAMP类型，可表示的日期格式为“YYYY-MM-DD HH：MM：SS”，在存储时需要4个字节。

MySQL中适合生日的日期与时间类型

DATE 类型

DATE 类型用于仅需要日期值时，没有时间部分，在存储时需要 3 个字节。日期格式为 'YYYY-MM-DD'，其中 YYYY 表示年，MM 表示月，DD 表示日。

在给 DATE 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATE 的日期格式即可。如下所示：

以 'YYYY-MM-DD' 或者 'YYYYMMDD' 字符中格式表示的日期，取值范围为 '1000-01-01'～'9999-12-3'。例如，输入 '2015-12-31' 或者 '20151231'，插入数据库的日期为2015-12-31。

以 'YY-MM-DD' 或者 'YYMMDD' 字符串格式表示日期，在这里YY表示两位的年值。MySQL 解释两位年值的规则：'00～69' 范围的年值转换为 '2000~2069'，'70~99' 范围的年值转换为 '1970～1999'。例如，输入 '15-12-31'，插入数据库的日期为 2015-12-31；输入 '991231'，插入数据库的日期为 1999-12-31。

以 YYMMDD 数字格式表示的日期，与前面相似，00~69 范围的年值转换为 2000～2069，80～99 范围的年值转换为 1980～1999。例如，输入 151231，插入数据库的日期为 2015-12-31，输入 991231，插入数据库的日期为 1999-12-31。

使用 CURRENT_DATE 或者 NOW()，插入当前系统日期。

提示：MySQL 允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符。例如，'98-11-31'、'98.11.31'、'98/11/31'和'98@11@31' 是等价的，这些值也可以正确地插入数据库。

DATETIME 类型

DATETIME 类型用于需要同时包含日期和时间信息的值，在存储时需要 8 个字节。日期格式为 'YYYY-MM-DD HH：MM：SS'，其中 YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒。

在给 DATETIME 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATETIME 的日期格式即可，如下所示：

以 'YYYY-MM-DD HH：MM：SS' 或者 'YYYYMMDDHHMMSS' 字符串格式表示的日期，取值范围为 '1000-01-01 00：00：00'～'9999-12-3 23：59：59'。例如，输入 '2014-12-31 05：05：05' 或者 '20141231050505’，插入数据库的 DATETIME 值都为 2014-12-31 05：05：05。

以 'YY-MM-DD HH：MM：SS' 或者 'YYMMDDHHMMSS' 字符串格式表示的日期，在这里 YY 表示两位的年值。与前面相同，'00～79' 范围的年值转换为 '2000～2079'，'80～99' 范围的年值转换为 '1980～1999'。例如，输入 '14-12-31 05：05：05'，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。

以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如，输入 20141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 140505050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。

提示：MySQL 允许“不严格”语法：任何标点符号都可用作日期部分或时间部分之间的间隔符。例如，'98-12-31 11：30：45'、'98.12.31 11+30+35'、'98/12/31 11*30*45' 和 '98@12@31 11^30^45' 是等价的，这些值都可以正确地插入数据库。

TIMESTAMP 类型

TIMESTAMP 的显示格式与 DATETIME 相同，显示宽度固定在 19 个字符，日期格式为 YYYY-MM-DD HH：MM：SS，在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，为 '1970-01-01 00：00：01'UTC～'2038-01-19 03：14：07'UTC。在插入数据时，要保证在合法的取值范围内。

提示：协调世界时（英：Coordinated Universal Time，法：Temps Universel Coordonné）又称为世界统一时间、世界标准时间、国际协调时间。英文（CUT）和法文（TUC）的缩写不同，作为妥协，简称 UTC。

TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外，还有一个最大的区别是：

DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；

而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。

提示：如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值，结果值的时间部分被设置为 '00：00：00'，因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值，结果值的时间部分被删除，因此DATE 值未包含时间信息。

时间与日期函数

系统时间函数 now(), current_timestamp, sysdate()

这三个函数都能返回系统时间，看看它们的区别，同时访问这三个函数得到的结果一样，但是让其等待几秒之后，结果就不同了。

上图发现sysdate()返回的时间与now(), current_timestamp不同，慢了2秒，原因如下：

current_timestamp 是now的同义词，两者实际上是一样的；

sysdate() 函数返回的是执行到当前函数时的时间，而now()返回的是执行sql语句时的时间；

时间加减函数

① date_add 与 date_sub

date_add(date, interval expr unit) 与 date_sub(date, interval expr unit) 分别表示增加与减少。

selectdate_add('2017-12-1822:51:00',interval1second),date_add('2017-12-1822:51:00',interval1minute),date_add('2017-12-1822:51:00',interval1hour),date_add('2017-12-1822:51:00',interval1day),date_add('2017-12-1822:51:00',interval1month),date_add('2017-12-1822:51:00',interval1year);

遇到闰月时，date_add怎么处理呢，MySQL如果是闰月返回的日期为2月29，如果不是闰月返回的是2月28。

selectdate_add('2016-02-2922:51:00',interval-1year),date_add('2016-02-2922:51:00',interval4year);

2015-02-2822:51:002020-02-2922:51:00

② datediff 与 timediff

datediff(date1,date2)：两个日期相减 date1 - date2，返回天数。select datediff('2016-02-29 22:51:00','2016-03-29 22:51:00');

timediff(time1,time2)：两个日期相减 time1 - time2，返回 time 差值(格式为：838:59:59)。select timediff('2016-02-29 22:51:00','2016-01-29 22:51:00');

注意：timediff(time1,time2) 函数的两个参数类型必须相同。

③ timestamp 增，减，转换函数

timestamp(date) -- date to timestamp

timestamp(date, time) -- date + time

timestampadd(unit,interval,datetime_expr)-- 类似date_add，对时间按单位进行增加，减少

selecttimestampadd(second,60,'2017-12-1800:00:00');selecttimestampadd(minute,60,'2017-12-1800:00:00');selecttimestampadd(hour,60,'2017-12-1800:00:00');

timestampdiff(unit,datetime_expr1,datetime_expr2) --计算两个时间的差，可以计算秒，分，时，天，月，年，周

selecttimestampdiff(second,'2017-12-1800:00:00','2017-12-1812:00:00');selecttimestampdiff(hour,'2017-11-1800:00:00','2017-12-1812:00:00');selecttimestampdiff(day,'2017-10-1700:00:00','2017-11-1823:00:00');

④ date_format 函数

date_format 函数主要用途是格式化日期

selectdate_format(now(),'%Y%m%d'),date_format(now(),'%Y-%m-%d'),date_format(now(),'%Y/%m/%d'),date_format(now(),'%m/%d/%Y');

注意：查询数据时如果对某个日期字段使用这个函数，则优化器不会使用字段的索引，也不能通过索引来查询数据，因此查询效率会很低，如下列查询语句，如果字段birth_date有索引，则该索引因为使用了date_format函数失效：

select*fromemployeeswheredate_format(birth_date,'%Y-%m-%d')='1961-08-03';

日期的经典编程问题

根据某个用户的出生日期与当前日期，计算他最近的生日(包括已经过去的和将来的)

--上一年，当年，下一年生日与当前时间差最小的年就是用户最近的生日selectemp_no,first_name,last_name,birth_date,today,if(last_diff<=cur_diffandlast_diff<=next_diff,last,if(cur_difffrom(--计算当前时间与上一年，当年，下一年生日的时间差selectemp_no,first_name,last_name,birth_date,datediff(today,last)aslast_diff,abs(datediff(today,cur))ascur_diff,abs(datediff(today,next))asnext_diff,last,cur,next,todayfrom(--用户最近的生日可能分布在前一年，当年，下一年。根据年度差year_diff计算上一年，当年，下一年用户的生日selectemp_no,first_name,last_name,birth_date,DATE_ADD(birth_date,INTERVALyear_diffyear)ascur,DATE_ADD(birth_date,INTERVALyear_diff+1year)asnext,DATE_ADD(birth_date,INTERVALyear_diff-1year)aslast,todayfrom(--获取当前事前与出生日期的年度差year_diffselectemp_no,first_name,last_name,birth_date,(year(NOW())-year(birth_date))asyear_diff,NOW()astodayfromemployees)a)b)c


上一篇：mysql执行计划指的是什么
下一篇：Ubuntu下MySQL如何安装
mysql