日期时间类型
占用空间
日期格式
最小值
最大值
零值表示
DATETIME
8字节
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字节
YYYY-mm-DD HH:MM:SS
1970-01-01 08-00-01
2038-01-19 03:14:07
00000000000000
DATE
4字节
YYYY-mm-DD
1000-01-01
9999-12-31
0000-00-00
TIME
3字节
HH:MM:SS
-838:59:59
838:59:59
00:00:00
YEAR
1字节
YYYY
1901
2155
0000
mysql保存日期格式使用YYYY-mm-DD HH:MM:SS的iso 8601标准。
其中，%y表示年，%m表示月，%d表示天，%h表示小时，%i表示分钟，%s表示秒。
注意timestamp类型，从1970-2038年，比如生日之类的超出范围的就不适合。
1、获取当前系统时间
方法
功能描述
now()
获取系统当前的时间日期
current_timestamp()
获取当前的时间日期
current_date()
获取当前的日期
current_time()
获取当前的时分秒
now()作用： 获取当前的日期时间。
select now();
current_timestamp()作用： 获取当前的日期时间。
select current_timestamp()
current_date()作用： 获取当前的日期。
select current_date()
current_time()作用： 获取当前的时分秒。
select current_time()
2、获取指定时间的年月日时分秒部分
方法
功能描述
year(timestamp|date)
获取指定日期的年
month(timestamp|date)
获取指定日期的月
day(timestamp|date)
获取指定日期的天
hour(timestamp|hour)
获取指定时间的小时
minute(timestamp|minute)
获取指定时间的分钟
second(timestamp|second)
获取指定时间的秒
#year(),
month(), day()
#hour(), minute(), second()
select year(now()),
month(now()), day(now()), hour(now()), minute(now()), second(now())
3、获取当前日期是本月的第几天，本周的星期几\
方法
功能描述
day(date|timestamp)
获取当前日期为本月的多少天
dayofweek(date|timestamp)
获取当前日期为本周的星期几， 1-7，其中1表示星期日
weekday(date|timestamp)
获取当前日期为本周的星期几， 0-6，其中0表示星期一
#day()
#dayofweek()
结果为1-7，其中1表示星期日；
#weekday()
结果为0-6，其中0表示星期一；
select day(now()),
dayofweek(now()), weekday(now());
4、时间，日期转换成字符串
方法
功能描述
date_format( timestamp
date ， format )
time_format( timestamp
date , format )
#mysql中分钟用 i%表示
select date_format( now(), '%Y-%m-%D %H:%i:%s' )
select date_format( now(), '%Y-%m-%D' )
select date_format( now(), '%H:%i:%d' )
select time_format( now(), '%H:%i:%d' )
5、时间日期查询
可以把mysql中当做字符串来进行逻辑操作。比如 >; <; =; between and;
select * from test where birth = '1990-01-01';
select * from test where birth > '1991';
select * from test where birth < '1992-02-01';
select * from test where birth in ('1990-01-01', '1991-03-02', '1990-03-03' );
select * from test where birth BETWEEN '1990-01-01' and '2000-01-01';
6、两个日期时间的差值计算
方法
功能描述
time_to_sec( time )
时分秒转成秒数;
sec_to_time(long)
秒数转成时分秒;
to_days(date)
日期转成天数;
from_days(long)
天数转成日期;
datediff( date1, date2 )
计算两个日期的天数差值，结果以天数显示。
timediff( time1, time2 )
计算两个时间的差值，结果以时分秒显示。
timestampdiff( type, datetime, datetime )
计算两个日期，或者时间日期的差值，可以秒数，分数，小时，天数等显示
时间转秒数 time_to_sec
mysql> select time_to_sec('12:13:14');
+-------------------------+
time_to_sec('12:13:14')
+-------------------------+
43994
+-------------------------+
秒数转时间 sec_to_time
mysql> select sec_to_time(43994);
mysql> select sec_to_time(43994);
+--------------------+
sec_to_time(43994)
+--------------------+
12:13:14
+--------------------+
日期转天数（相对于0000-00-00）
mysql> select to_days('0001-02-01');
+-----------------------+
to_days('0001-02-01')
+-----------------------+
397
+-----------------------+
mysql> select to_days('1991-02-01');
+-----------------------+
to_days('1991-02-01')
+-----------------------+
727229
+-----------------------+
天数转日期（相对于0000-00-00）
mysql> select from_days(726864);
+-------------------+
from_days(726864)
+-------------------+
1990-02-01
+-------------------+
比较两个日期的天数差值 （只能比较年月日）
mysql> select datediff('1990-01-02', '2020-04-02');
+--------------------------------------+
datediff('1990-01-02', '2020-04-02')
+--------------------------------------+
-11048
+--------------------------------------+
比较两个时间的差值 (只能比较时分秒)
mysql> select TIMEDIFF('19:19:19','19:19:18');
+---------------------------------+
TIMEDIFF('19:19:19','19:19:18')
+---------------------------------+
00:00:01
+---------------------------------+
比较两个日期，时间的天数，秒数的差值。timestampdiff( , , )，比较日期或者时间日期组合，不能单独比较时分秒。
mysql> select timestampdiff( day, '1990-01-02', '2020-04-02' );
+--------------------------------------------------+
timestampdiff( day, '1990-01-02', '2020-04-02' )
+--------------------------------------------------+
11048
+-----------------------------------------------------------------------+
mysql> select timestampdiff( second, '1991-01-01 12:12:12', '1991-01-01 12:12:11' );
+-----------------------------------------------------------------------+
timestampdiff( second, '1991-01-01 12:12:12', '1991-01-01 12:12:11' )
+-----------------------------------------------------------------------+
-1
+-----------------------------------------------------------------------+
7、时间，日期计算（迁移，后移）
方法
功能描述
addtime(timestamp, time)
当前时间推后指定时分秒。
timestamp( date )timestamp(date, time)
字符串，日期 转成时间戳; 以及后移指定时分秒
timestampadd( unit, interval, datetime_expr )
指定字符串或者datetime，后移指定的天，时分秒等
date_add()
指定日期前移后移指定天数
date_sub()
timestamp( date
datetime ) : 将时间日期转换成timestamp。timestamp( date
datetime, time ) : 将时间日期转换成timestamp，并且后移指定的时分秒。
#将日期或者日期字符串转换成时间戳。
mysql> select timestamp('1990-01-01');
+-------------------------+
timestamp('1990-01-01')
+-------------------------+
1990-01-01 00:00:00
+-------------------------+
#将日期或者日期字符串转换成时间戳，并且后移指定的时分秒。
mysql> select timestamp('1990-01-01', '11:11:11');
+-------------------------------------+
timestamp('1990-01-01', '11:11:11')
+-------------------------------------+
1990-01-01 11:11:11
+-------------------------------------+
addtime(timestamp, time) : 将指定的time，timestamp后移指定时分秒。
mysql> select addtime('1990-01-01 12:12:12', '12:12:12');
+--------------------------------------------+
addtime('1990-01-01 12:12:12', '12:12:12')
+--------------------------------------------+
1990-01-02 00:24:24
+--------------------------------------------+
mysql> select addtime('12:12:12', '12:12:12');
+---------------------------------+
addtime('12:12:12', '12:12:12')
+---------------------------------+
24:24:24
+---------------------------------+
date_add(date, interval expr type): 对指定日期前移，后移指定时间
#后移指定小时；
mysql> select date_add( '1991-01-01', interval 1
hour
);
+---------------------------------------------+
date_add( '1991-01-01', interval 1
hour
)
+---------------------------------------------+
1991-01-01 01:00:00
+---------------------------------------------+
#后移指定天数
mysql> select date_add( '1991-01-01', interval 1
day
);
+--------------------------------------------+
date_add( '1991-01-01', interval 1
day
)
+--------------------------------------------+
1991-01-02
+--------------------------------------------+
#后移指定天数和小时
mysql> select date_add('1991-01-02', interval '1 1' day_hour);
+-------------------------------------------------+
date_add('1991-01-02', interval '1 1' day_hour)
+-------------------------------------------------+
1991-01-03 01:00:00
+-------------------------------------------------+
1 row in set (0.00 sec)
#前移指定天数 (使用负数，可以将时间前移)
mysql> select date_add('1991-01-02', interval -1 hour);
+------------------------------------------+
date_add('1991-01-02', interval -1 hour)
+------------------------------------------+
1991-01-01 23:00:00
+------------------------------------------+
date_sub( date, interval expr type): 函数想日期添加指定的
mysql> select date_sub('1991-01-01', interval
'1' day);
+-------------------------------------------+
date_sub('1991-01-01', interval
'1' day)
+-------------------------------------------+
1990-12-31
+-------------------------------------------+
8、月末月初计算
1、计算本月的月末
mysql> select last_day('2020-03-20');
+------------------------+
last_day('2020-03-20')
+------------------------+
2020-03-31
+------------------------+
2、计算月初
#方式一
利用日期格式化，拼凑日期。
mysql> select DATE( DATE_FORMAT('2020-03-20','%y-%m-01') );
+----------------------------------------------+
DATE( DATE_FORMAT('2020-03-20','%y-%m-01') )
+----------------------------------------------+
2020-03-01
+----------------------------------------------+
#方式二
mysql> select date_sub('2020-03-20', INTERVAL day('2020-03-20')-1 day);
+----------------------------------------------------------+
date_sub('2020-03-20', INTERVAL day('2020-03-20')-1 day)
+----------------------------------------------------------+
2020-03-01
+----------------------------------------------------------+
3、计算三个月前的今天
mysql> select date_sub( '2020-02-20', interval 3 month );
+--------------------------------------------+
date_sub( '2020-02-20', interval 3 month )
+--------------------------------------------+
2019-11-20
4、计算三个月前的月初
mysql> select date_format( date_sub('2020-02-20', interval 3 month), '%Y-%m-01' );
+---------------------------------------------------------------------+
date_format( date_sub('2020-02-20', interval 3 month), '%Y-%m-01' )
+---------------------------------------------------------------------+
2019-11-01
+---------------------------------------------------------------------+
5、获取上个月的最后一天
#错误方式，先计算本月月末，再前移一个月
#得出的结果不是上个月的月末。
mysql> select date_sub( last_day('2020-02-20') , interval 1 month );
+-------------------------------------------------------+
date_sub( last_day('2020-02-20') , interval 1 month )
+-------------------------------------------------------+
2020-01-29
+-------------------------------------------------------+
#应该采用计算月初，再减一天的方式
mysql> select date_sub( '2020-02-20' , interval day('2020-02-20') day);
+----------------------------------------------------------+
date_sub( '2020-02-20' , interval day('2020-02-20') day)
+----------------------------------------------------------+
2020-01-31
+----------------------------------------------------------+
9、星期几计算
1、计算本周星期几的日期
#求上周星期日的日期（外国人以上周星期日作为一周的第一天）
mysql> select date_add('2020-02-20', interval 1-dayofweek('2020-02-20')
day );
+------------------------------------------------------------------+
date_add('2020-02-20', interval 1-dayofweek('2020-02-20')
day )
+------------------------------------------------------------------+
2020-02-16
#求本周星期六的日期
mysql> select date_add('2020-03-19', interval 7-dayofweek('2020-03-19')
day );
+------------------------------------------------------------------+
date_add('2020-03-19', interval 7-dayofweek('2020-03-19')
day )
+------------------------------------------------------------------+
2020-03-21
+------------------------------------------------------------------+
2、 计算3周之前的星期一
mysql> select date_add('2020-03-19', interval 7-dayofweek('2020-03-19')-21 day);
+-------------------------------------------------------------------+
date_add('2020-03-19', interval 7-dayofweek('2020-03-19')-21 day)
+-------------------------------------------------------------------+
2020-02-29
+-------------------------------------------------------------------+