mysql中DATETIME，DATE和TIMESTAMP的区别

Question

如题所述

Answer 1

一、TIMESTAMP

显示格式：YYYY-MM-DD HH:MM:SS

时间范围:[ '1970-01-01 00:00:00'到'2037-12-31 23:59:59']

TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 在创建新记录和修改现有记录的时候都对这个数据列刷新。

TIMESTAMP DEFAULT CURRENT_TIMESTAMP 在创建新记录的时候把这个字段设置为当前时间，但以后修改时，不再刷新它。

TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 在创建新记录的时候把这个字段设置为0，以后修改时刷新它。

TIMESTAMP DEFAULT ‘yyyy-mm-dd hh:mm:ss’ ON UPDATE CURRENT_TIMESTAMP 在创建新记录的时候把这个字段设置为给定值，以后修改时刷新它

1、TIMESTAMP列不为空时，默认值可以为“0000-00-00 00:00:00”，但不能为null。

2、一个表可以存在多个TIMESTAMP列，但一个表只有一个TIMESTAMP类型的字段可以在默认值或者UPDATE部分用CURRENT_TIMESTAMP，即设置为数据更新而改变为数据库系统当前值。

3、TIMESTAMP列的默认值是CURRENT_TIMESTAMP常量值。当纪录数据发生变化的时候，TIMESTAMP列会自动将其值设定为CURRENT_TIMESTAMP。

4、TIMESTAMP列创建后的格式是：

ALTER TABLE `course`

ADD COLUMN `birthday` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

ALTER TABLE `course`

ADD COLUMN `birthday` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP ;

ALTER TABLE `course`

ADD COLUMN `birthday` timestamp NULL AFTER `cname`;

二、DATETIME

显示格式：YYYY-MM-DD HH:MM:SS

时间范围:[ '1000-01-01 00:00:00'到'9999-12-31 23:59:59']

三、DATE

显示格式：YYYY-MM-DD

时间范围：['1000-01-01'到'9999-12-31']

四、日期格式转换

1、字符串转日期

select STR_TO_DATE('2013-01-29 13:49:18', '%Y-%m-%d %H:%i:%s')

2、日期转字符串

select DATE_FORMAT('2013-01-29 13:49:18', '%Y-%m-%d %H:%i:%s')

五、日期的中常用的年月日时分秒星期月份等获取方法

select TIMESTAMP('2013-01-29 13:50:27');

select DATE('2013-01-29 13:50:27');

select YEAR('2013-01-29 13:50:27');

select MONTH(('2013-01-29 13:50:27');

select WEEK('2013-01-29 13:50:27');

select DAY('2013-01-29 13:50:27');

select TIME('2013-01-29 13:50:27');

select CURTIME();

select CURDATE();

select CURRENT_DATE;

select CURRENT_TIME;

select CURRENT_TIMESTAMP;

select NOW()

六、日期的运算：

SELECT DATE_ADD('2013-01-29 13:50:27', INTERVAL 1 DAY);

-> '2013-01-30 13:50:27'

SELECT DATE_ADD('2013-01-29 13:50:27', INTERVAL 1 HOUR);

-> '2013-01-29 14:50:27'

SELECT DATE_ADD('2013-01-29 13:50:27', INTERVAL 1 MONTH);

-> '2013-02-28 13:50:27'

Answer 2

DATETIME 类型可用于需要同时包含日期和时间信息的值。MySQL 以 'YYYY-MM-DD HH:MM:SS' 格式检索与显示 DATETIME 类型。支持的范围是 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'。(“支持”的含义是，尽管更早的值可能工作，但不能保证他们均可以。)

DATE 类型可用于需要一个日期值而不需要时间部分时。MySQL 以 'YYYY-MM-DD' 格式检索与显示 DATE 值。支持的范围是 '1000-01-01' 到 '9999-12-31'。

TIMESTAMP 列类型提供了一种类型，通过它你可以以当前操作的日期和时间自动地标记 Insert 或Update 操作。如果一张表中有多个 TIMESTAMP 列，只有第一个被自动更新。

“完整”TIMESTAMP格式是14位，但TIMESTAMP列也可以用更短的显示尺寸创造
最常见的显示尺寸是6、8、12、和14。
你可以在创建表时指定一个任意的显示尺寸，但是定义列长为0或比14大均会被强制定义为列长14。
列长在从1～13范围的奇数值尺寸均被强制为下一个更大的偶数。