MySQL日期数据类型MySQL时间类型使用总结.docx
《MySQL日期数据类型MySQL时间类型使用总结.docx》由会员分享，可在线阅读，更多相关《MySQL日期数据类型MySQL时间类型使用总结.docx（15页珍藏版）》请在冰点文库上搜索。
MySQL日期数据类型MySQL时间类型使用总结
MySQL日期数据类型、MySQL时间类型使用总结.txt如果你同时爱几个人，说明你年轻；如果你只爱一个人，那么，你已经老了；如果你谁也不爱，你已获得重生。
积极的人一定有一个坚持的习惯。
MySQL：
MySQL日期数据类型、MySQL时间类型使用总结
MySQL日期类型：
日期格式、所占存储空间、日期范围比较。
日期类型存储空间日期格式日期范围
-----------------------------------------------------------------------------------
datetime8bytesYYYY-MM-DDHH:
MM:
SS1000-01-0100:
00:
00~9999-12-3123:
59:
59
timestamp4bytesYYYY-MM-DDHH:
MM:
SS1970-01-0100:
00:
01~2038
date3bytesYYYY-MM-DD1000-01-01~9999-12-31
year1bytesYYYY1901~2155
在MySQL中创建表时，对照上面的表格，很容易就能选择到合适自己的数据类型。
不过到底是选择datetime还是timestamp，可能会有点犯难。
这两个日期时间类型各有优点：
datetime的日期范围比较大；timestamp所占存储空间比较小，只是datetime的一半。
另外，timestamp类型的列还有个特性：
默认情况下，在insert,update数据时，timestamp列会自动以当前时间（CURRENT_TIMESTAMP）填充/更新。
"自动"的意思就是，你不去管它，MySQL会替你去处理。
一般情况下，我倾向于使用datetime日期类型。
MySQL时间类型：
时间格式、所占存储空间、时间范围。
时间类型存储空间时间格式时间范围
-----------------------------------------------------------------------------------
time3bytesHH:
MM:
SS-838:
59:
59~838:
59:
59
time时间范围居然有这么大的范围，特别是time可以取负值，有点奇怪。
后来，看了MySQL手册才知道这是为了满足两个日期时间相减才这样设计的。
selecttimediff（'2000:
01:
3123:
59:
59','2000:
01:
0100:
00:
00'）;--743:
59:
59
selecttimediff（'2000:
01:
0100:
00:
00','2000:
01:
3123:
59:
59'）;---743:
59:
59
selecttimediff（'23:
59:
59','12:
00:
00'）;--11:
59:
59
注意，timediff的两个参数只能是datetime/timestamp,time类型的，并且这两个参数类型要相同。
即：
datetime/timestamp和datetime/timestamp比较；time和time相比较。
虽然MySQL中的日期时间类型比较丰富，但遗憾的是，目前（2008-08-08）这些日期时间类型只能支持到秒级别，不支持毫秒、微秒。
也没有产生毫秒的函数。
《MySQL：
MySQL日期数据类型、MySQL时间类型使用总结》适用于MySQL5.X及以上版本。
一、MySQL获得当前日期时间函数
1.1获得当前日期+时间（date+time）函数：
now（）
mysql>selectnow（）;
+---------------------+
|now（）|
+---------------------+
|2008-08-0822:
20:
46|
+---------------------+
除了now（）函数能获得当前的日期时间外，MySQL中还有下面的函数：
current_timestamp（）
current_timestamp
localtime（）
localtime
localtimestamp--（v4.0.6）
localtimestamp（）--（v4.0.6）
这些日期时间函数，都等同于now（）。
鉴于now（）函数简短易记，建议总是使用now（）来替代上面列出的函数。
1.2获得当前日期+时间（date+time）函数：
sysdate（）
sysdate（）日期时间函数跟now（）类似，不同之处在于：
now（）在执行开始时值就得到了，sysdate（）在函数执行时动态得到值。
看下面的例子就明白了：
mysql>selectnow（）,sleep（3）,now（）;
+---------------------+----------+---------------------+
|now（）|sleep（3）|now（）|
+---------------------+----------+---------------------+
|2008-08-0822:
28:
21|0|2008-08-0822:
28:
21|
+---------------------+----------+---------------------+
mysql>selectsysdate（）,sleep（3）,sysdate（）;
+---------------------+----------+---------------------+
|sysdate（）|sleep（3）|sysdate（）|
+---------------------+----------+---------------------+
|2008-08-0822:
28:
41|0|2008-08-0822:
28:
44|
+---------------------+----------+---------------------+
可以看到，虽然中途sleep3秒，但now（）函数两次的时间值是相同的；sysdate（）函数两次得到的时间值相差3秒。
MySQLManual中是这样描述sysdate（）的：
Returnthetimeatwhichthefunctionexecutes。
sysdate（）日期时间函数，一般情况下很少用到。
2.获得当前日期（date）函数：
curdate（）
mysql>selectcurdate（）;
+------------+
|curdate（）|
+------------+
|2008-08-08|
+------------+
其中，下面的两个日期函数等同于curdate（）：
current_date（）
current_date
3.获得当前时间（time）函数：
curtime（）
mysql>selectcurtime（）;
+-----------+
|curtime（）|
+-----------+
|22:
41:
30|
+-----------+
其中，下面的两个时间函数等同于curtime（）：
current_time（）
current_time
4.获得当前UTC日期时间函数：
utc_date（）,utc_time（）,utc_timestamp（）
mysql>selectutc_timestamp（）,utc_date（）,utc_time（）,now（）
+---------------------+------------+------------+---------------------+
|utc_timestamp（）|utc_date（）|utc_time（）|now（）|
+---------------------+------------+------------+---------------------+
|2008-08-0814:
47:
11|2008-08-08|14:
47:
11|2008-08-0822:
47:
11|
+---------------------+------------+------------+---------------------+
因为我国位于东八时区，所以本地时间=UTC时间+8小时。
UTC时间在业务涉及多个国家和地区的时候，非常有用。
二、MySQL日期时间Extract（选取）函数。
1.选取日期时间的各个部分：
日期、时间、年、季度、月、日、小时、分钟、秒、微秒
set@dt='2008-09-1007:
15:
30.123456';
selectdate（@dt）;--2008-09-10
selecttime（@dt）;--07:
15:
30.123456
selectyear（@dt）;--2008
selectquarter（@dt）;--3
selectmonth（@dt）;--9
selectweek（@dt）;--36
selectday（@dt）;--10
selecthour（@dt）;--7
selectminute（@dt）;--15
selectsecond（@dt）;--30
selectmicrosecond（@dt）;--123456
2.MySQLExtract（）函数，可以上面实现类似的功能：
set@dt='2008-09-1007:
15:
30.123456';
selectextract（yearfrom@dt）;--2008
selectextract（quarterfrom@dt）;--3
selectextract（monthfrom@dt）;--9
selectextract（weekfrom@dt）;--36
selectextract（dayfrom@dt）;--10
selectextract（hourfrom@dt）;--7
selectextract（minutefrom@dt）;--15
selectextract（secondfrom@dt）;--30
selectextract（microsecondfrom@dt）;--123456
selectextract（year_monthfrom@dt）;--200809
selectextract（day_hourfrom@dt）;--1007
selectextract（day_minutefrom@dt）;--100715
selectextract（day_secondfrom@dt）;--10071530
selectextract（day_microsecondfrom@dt）;--10071530123456
selectextract（hour_minutefrom@dt）;--715
selectextract（hour_secondfrom@dt）;--71530
selectextract（hour_microsecondfrom@dt）;--71530123456
selectextract（minute_secondfrom@dt）;--1530
selectextract（minute_microsecondfrom@dt）;--1530123456
selectextract（second_microsecondfrom@dt）;--30123456
MySQLExtract（）函数除了没有date（）,time（）的功能外，其他功能一应具全。
并且还具有选取'day_microsecond'等功能。
注意这里不是只选取day和microsecond，而是从日期的day部分一直选取到microsecond部分。
够强悍的吧！
MySQLExtract（）函数唯一不好的地方在于：
你需要多敲几次键盘。
3.MySQLdayof...函数：
dayofweek（）,dayofmonth（）,dayofyear（）
分别返回日期参数，在一周、一月、一年中的位置。
set@dt='2008-08-08';
selectdayofweek（@dt）;--6
selectdayofmonth（@dt）;--8
selectdayofyear（@dt）;--221
日期'2008-08-08'是一周中的第6天（1=Sunday,2=Monday,...,7=Saturday）；一月中的第8天；一年中的第221天。
4.MySQLweek...函数：
week（）,weekofyear（）,dayofweek（）,weekday（）,yearweek（）
set@dt='2008-08-08';
selectweek（@dt）;--31
selectweek（@dt,3）;--32
selectweekofyear（@dt）;--32
selectdayofweek（@dt）;--6
selectweekday（@dt）;--4
selectyearweek（@dt）;--200831
MySQLweek（）函数，可以有两个参数，具体可看手册。
weekofyear（）和week（）一样，都是计算"某天"是位于一年中的第几周。
weekofyear（@dt）等价于week（@dt,3）。
MySQLweekday（）函数和dayofweek（）类似，都是返回"某天"在一周中的位置。
不同点在于参考的标准，weekday：
（0=Monday,1=Tuesday,...,6=Sunday）；dayofweek：
（1=Sunday,2=Monday,...,7=Saturday）
MySQLyearweek（）函数，返回year（2008）+week位置（31）。
5.MySQL返回星期和月份名称函数：
dayname（）,monthname（）
set@dt='2008-08-08';
selectdayname（@dt）;--Friday
selectmonthname（@dt）;--August
思考，如何返回中文的名称呢？
6.MySQLlast_day（）函数：
返回月份中的最后一天。
selectlast_day（'2008-02-01'）;--2008-02-29
selectlast_day（'2008-08-08'）;--2008-08-31
MySQLlast_day（）函数非常有用，比如我想得到当前月份中有多少天，可以这样来计算：
mysql>selectnow（）,day（last_day（now（）））asdays;
+---------------------+------+
|now（）|days|
+---------------------+------+
|2008-08-0911:
45:
45|31|
+---------------------+------+
三、MySQL日期时间计算函数
1.MySQL为日期增加一个时间间隔：
date_add（）
set@dt=now（）;
selectdate_add（@dt,interval1day）;--add1day
selectdate_add（@dt,interval1hour）;--add1hour
selectdate_add（@dt,interval1minute）;--...
selectdate_add（@dt,interval1second）;
selectdate_add（@dt,interval1microsecond）;
selectdate_add（@dt,interval1week）;
selectdate_add（@dt,interval1month）;
selectdate_add（@dt,interval1quarter）;
selectdate_add（@dt,interval1year）;
selectdate_add（@dt,interval-1day）;--sub1day
MySQLadddate（）,addtime（）函数，可以用date_add（）来替代。
下面是date_add（）实现addtime（）功能示例：
mysql>set@dt='2008-08-0912:
12:
33';
mysql>
mysql>selectdate_add（@dt,interval'01:
15:
30'hour_second）;
+------------------------------------------------+
|date_add（@dt,interval'01:
15:
30'hour_second）|
+------------------------------------------------+
|2008-08-0913:
28:
03|
+------------------------------------------------+
mysql>selectdate_add（@dt,interval'101:
15:
30'day_second）;
+-------------------------------------------------+
|date_add（@dt,interval'101:
15:
30'day_second）|
+-------------------------------------------------+
|2008-08-1013:
28:
03|
+-------------------------------------------------+
date_add（）函数，分别为@dt增加了"1小时15分30秒"和"1天1小时15分30秒"。
建议：
总是使用date_add（）日期时间函数来替代adddate（）,addtime（）。
2.MySQL为日期减去一个时间间隔：
date_sub（）
mysql>selectdate_sub（'1998-01-0100:
00:
00',interval'11:
1:
1'day_second）;
+----------------------------------------------------------------+
|date_sub（'1998-01-0100:
00:
00',interval'11:
1:
1'day_second）|
+----------------------------------------------------------------+
|1997-12-3022:
58:
59|
+----------------------------------------------------------------+
MySQLdate_sub（）日期时间函数和date_add（）用法一致，不再赘述。
另外，MySQL中还有两个函数subdate（）,subtime（），建议，用date_sub（）来替代。
3.MySQL另类日期函数：
period_add（P,N）,period_diff（P1,P2）
函数参数"P"的格式为"YYYYMM"或者"YYMM"，第二个参数"N"表示增加或减去Nmonth（月）。
MySQLperiod_add（P,N）：
日期加/减去N月。
mysql>selectperiod_add（200808,2）,period_add（20080808,-2）
+----------------------+-------------------------+
|period_add（200808,2）|period_add（20080808,-2）|
+----------------------+-------------------------+
|200810|20080806|
+----------------------+-------------------------+
MySQLperiod_diff（P1,P2）：
日期P1-P2，返回N个月。
mysql>selectperiod_diff（200808,200801）;
+-----------------------------+
|period_diff（200808,200801）|
+-----------------------------+
|7|
+-----------------------------+
在MySQL中，这两个日期函数，一般情况下很少用到。
4.MySQL日期、时间相减函数：
datediff（date1,date2）,timediff（time1,time2）
MySQLdatediff（date1,date2）：
两个日期相减date1-date2，返回天数。
selectdatediff（'2008-08-08','2008-08-01'）;--7
selectdatediff（'2008-08-01','2008-08-08'）;---7
MySQLtimediff（time1,time2）：
两个日期相减time1-time2，返回time差值。
selecttimediff（'2008-08-0808:
08:
08','2008-08-0800:
00:
00'）;--08:
08:
08
升级会员 