一.mysql数据库中的date
1.DATETIME和DATE:
DATETIME占用8个字节,日期范围为"1000-01-01 00:00:00"到"9999-12-31 23:59:59"
DATE占用3个字节,日期范围为"1000-01-01"到"9999-12-31"
mysql的CAST函数在强制转换到DATETIME时会保留到微秒数,不过在插入后同样会截断.
e.g.
select cast('2013-02-11 00:22:33' as datetime) D\G *************************** 1. row *************************** D: 2013-02-11 00:22:33 1 row in set (0.00 sec) mysql> insert into date -> select cast('2013-12-06 21:18:33' as datetime); Query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0 type_name(fsp) TIME.DATETIME.TIMESTAMP select CURTIME(4) AS TIME\G *************************** 1. row *************************** TIME: 22:02:38 1 row in set (0.01 sec)
2.TIMESTAMP
UTC:世界统一时间,由英文(COordinated Universal Time)和法文(Temps Universal Cordonne')而来
TIMESTAMP和DATETIME格式是一样的,均为"YYYY-MM-DD HH:MM:SS";
区别是:
1).TIMESTAMP占用４个字节,显示为"1970-01-01 00:00:00" UTC到"2038-01-19 03:14:07" UTC,而DATETIME占用8个字节,日期范围为"1000-01-01 00:00:00"到"9999-12-31 23:59:59"
2).在建表进可以为TIMESTAMP建一个默认值,而DATETIME不行.
3).在更新表时,可以设置TIMESTAMP类型的列自动更新时间为当前时间.
eg.1:
mysql> create table s_timestamp -> ( a INT, -> b timestamp default current_timestamp -> )engine=innodb -> ; Query OK, 0 rows affected (0.16 sec) mysql> desc s_timestamp; +-------+-----------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-------+ | a | int(11) | YES | | NULL | | | b | timestamp | NO | | CURRENT_TIMESTAMP | | +-------+-----------+------+-----+-------------------+-------+ 2 rows in set (0.00 sec) mysql> insert into s_timestamp (a) values (1); Query OK, 1 row affected (0.04 sec) mysql> select * from s_timestamp; +------+---------------------+ | a | b | +------+---------------------+ | 1 | 2013-12-07 22:23:35 | +------+---------------------+ 1 row in set (0.00 sec)
再来新建一个时间随着更改值时自动更新的:
create table s_timestamp2(a INT, b timestamp default on update current_timestamp) Engine=InnoDB; mysql> insert into s_timestamp2 (a) values (1);
Query OK, 1 row affected (0.05 sec)
mysql> select * from s_timestamp2; +------+---------------------+ | a | b | +------+---------------------+ | 1 | 2013-12-07 22:30:13 | +------+---------------------+ 1 row in set (0.00 sec) mysql> update s_timestamp2 set a=2; Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from s_timestamp2; +------+---------------------+ | a | b | +------+---------------------+ | 2 | 2013-12-07 22:31:39 | +------+---------------------+ 1 row in set (0.00 sec)
3.YEAR和TIME
1)YEAR类型战胜1个字节,并且在定义时可以指定显示的宽度为YEAR(4)或YEAR(2);
mysql> create table s_year( -> a year(2)) engine=innodb; mysql> desc s_year; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | a | year(2) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> select * from s_year; +------+ | a | +------+ | 13 | +------+ 1 row in set (0.00 sec)
对于year(4)日期范围为1970-2070,year(2)中00-69代表2000-2069年.
2)TIME占用3个字节,显示的范围为-838:59:59 ---- 838:59:59因为,TIME类型不仅可以用来保存一天中的时间,也可以用来保存时间间隔,所以time可以为负值和最大值大于23作出解释.TIME类型同样可以显示微秒时间,但是在插入时,数据库同样会进行截断.
eg:
mysql> create table s_time (a TIME)ENGINE=InnoDB; Query OK, 0 rows affected (0.12 sec) mysql> desc s_time; +-------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-----+---------+-------+ | a | time | YES | | NULL | | +-------+------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> insert into s_time select '22:49:33' mysql> select * from s_time; +----------+ | a | +----------+ | 22:49:33 | +----------+ 1 row in set (0.00 sec)
二.与日期和时间相关的函数
1.now(),current_timestamp(),sysdate(),表示当前时间的函数
mysql> select now(),current_timestamp(),sysdate() \G *************************** 1. row *************************** now(): 2013-12-07 22:53:21 current_timestamp(): 2013-12-07 22:53:21 sysdate(): 2013-12-07 22:53:21 1 row in set (0.00 sec) mysql> select now(), current_timestamp(), sysdate(), sleep(3), now(), current_timestamp(), sysdate() \G *************************** 1. row *************************** now(): 2013-12-07 23:02:33 current_timestamp(): 2013-12-07 23:02:33 sysdate(): 2013-12-07 23:02:33 sleep(3): 0 now(): 2013-12-07 23:02:33 current_timestamp(): 2013-12-07 23:02:33 sysdate(): 2013-12-07 23:02:36 1 row in set (3.00 sec)
由上述例子可以看出,now()和current_timestamp()函数是同义的,即作用是相同的,都是sql语句开始执行时的时间,而sysdate()函数则表示sql语句执行到当前函数的时间.
2.时间加减函数
DATE_ADD(date,INTERVAL expr unit),DATE_SUB(date,INTERVAL expr unit),其中expr的值可以为负值，所以可以用date_add()函数来执行时间相减。
mysql> select now() as now ,date_add(now(),interval 1 day) as tomrrow, date_sub(now(),interval 1 day ) as yseterday ; +---------------------+---------------------+---------------------+ | now | tomrrow | yseterday | +---------------------+---------------------+---------------------+ | 2013-12-07 23:09:52 | 2013-12-08 23:09:52 | 2013-12-06 23:09:52 | +---------------------+---------------------+---------------------+ 1 row in set (0.00 sec) mysql> select date_add(now(),interval -1 day) as yesterday \G *************************** 1. row *************************** yesterday: 2013-12-06 23:13:30 1 row in set (0.00 sec)
如果是闰月mysql会自动判别月份日期，闰月，那么就有2.29，否则就没有，看下面例子
mysql> select date_add('2000-2-29',interval 4 year) as 4_year \G *************************** 1. row *************************** 4_year: 2004-02-29 1 row in set (0.00 sec) mysql> select date_add('2000-2-29',interval 3 year) as 4_year \G *************************** 1. row *************************** 4_year: 2003-02-28 1 row in set (0.00 sec) INTERVAL expr unit也可以是hour,second,minute,week,month,microsecond等时间单位 mysql> select date_add(now(),interval 10 minute) as min \G *************************** 1. row *************************** min: 2013-12-07 23:32:56 1 row in set (0.00 sec)
3.Date_Format函数
按照用户的需求来打印日期
mysql> select date_format(now(),'%Y-%m%d') as datetime; +-----------+ | datetime | +-----------+ | 2013-1207 | +-----------+ 1 row in set (0.00 sec)
