mysql中的时间格式为_MySQL的日期格式

MySQL的五种时间和日期类型

YEAR表示年份：字节数为1，取值范围为“1901——2155”

DATE表示日期：字节数为4，取值范围为“1000-01-01——9999-12-31”

TIME表示时间：字节数为3，取值范围为“-838:59:59——838:59:59”

DATETIME和TIMESTAMP表示日期和时间

DATETIME：字节数为8，取值范围为“1000-01-01 00:00:00——9999-12-31 23:59:59”

TIMESTAMP，字节数为4，取值范围为“19700101080001——20380119111407”

给各种时间日期类型赋值

1.

给YEAR赋值有3种方式

方式一：

直接插入4位字符串("1998")或者4位数字(1998)

方式二：

插入两位字符串("04")

如果范围是"00"-"69",则相当于插入"2000"-"2069"

如果范围是"70"-"99",则相当于插入"1970"-"1999"

**注："0"和"00"的效果一样都表示2000年**

方式三：

插入两位数字(90)

插入数字和插入字符的区别在于，插入一位0表示的不是2000年，而是0000.

2.

给TIME赋值

TIME类型表示为“时：分：秒”，尽管小时范围一般是0~23，但是为了表示某些特殊时间间隔，MySQL将TIME的小时范围扩发了，而且支持负值。

标准格式是"HH：MM：SS",但是不一定是这种格式,可以是"D HH：MM：SS"、"HH：MM"、"SS"、"D HH"、"D HH：MM"

"D HH：MM：SS"相当于是"(D*24+HH)：MM：SS"

eg：插入的是"2 23:50:50"="71:50:50"

"HH：MM"

eg:"11:25"="11:25:00"

"SS"

eg:"30"="00:00:30"

3.

给DATE赋值

标准格式是"YYYY-MM-DD"、其他的还有"YYYY@MM@DD"、"YYYY.MM.DD"等

还可以用"YY-MM-DD"

4.

给DATETIME赋值

标准格式为"YYYY-MM-DD HH：MM：SS"

具体赋值方法与上面各种类型的方法相似。

5.

给TIMESTAMP赋值

TIMESTAMP的取值范围比较小，没有DATETIME的取值范围大，因此输入值时一定要保证在TIMESTAMP的范围之内。它的插入也与插入其他日期和时间数据类型类似。

**插入当前时间

第一，可以使用CURRENT_TIMESTAMP；

第二，输入NULL，系统自动输入当前的TIMESTAMP；

第三，无任何输入，系统自动输入当前的TIMESTAMP。

另外有很特殊的一点：TIMESTAMP的数值是与时区相关。**

6.

当 MySQL遇到一个日期或时间类型的超出范围或对于该类型不合法的值时，它将该值转换为该类的"零"值。

列类型:"零"值

DATETIME:'0000-00-00 00:00:00'

DATE:'0000-00-00'

TIMESTAMP:00000000000000

TIME:'00:00:00'

YEAR:0000

datetime和timestamp的区别

datetime

1. 占用8个字节

2. 允许为空值，可以自定义值，系统不会自动修改其值。

3. 实际格式储存

4. 与时区无关

5. 不可以设定默认值，所以在不允许为空值的情况下，必须手动指定datetime字段的值才可以成功插入数据。

6. 可以在指定datetime字段的值的时候使用now()变量来自动插入系统的当前时间。

结论：datetime类型适合用来记录数据的原始的创建时间，因为无论你怎么更改记录中其他字段的值，datetime字段的值都不会改变，除非你手动更改它。

timestamp

1. 占用4个字节

2. 允许为空值，但是不可以自定义值，所以为空值时没有任何意义。

3. TIMESTAMP值不能早于1970或晚于2037。这说明一个日期，例如'1968-01-01'，虽然对于DATETIME或DATE值是有效的，但对于TIMESTAMP值却无效，如果分配给这样一个对象将被转换为0。

4. 值以UTC格式保存

5. 时区转化 ，存储时对当前的时区进行转换，检索时再转换回当前的时区。

6. 默认值为CURRENT_TIMESTAMP()，其实也就是当前的系统时间。

7. 数据库会自动修改其值，所以在插入记录时不需要指定timestamp字段的名称和timestamp字段的值，你只需要在设计表的时候添加一个timestamp字段即可，插入后该字段的值会自动变为当前系统时间。

8. 若是设置了**ON UPDATE CURRENT_TIMESTAMP**以后任何时间修改表中的记录时，对应记录的timestamp值会自动被更新为当前的系统时间。

结论：timestamp类型适合用来记录数据的最后修改时间，因为只要你更改了记录中其他字段的值，timestamp字段的值都会被自动更新。

timestamp的默认值

自动UPDATE 和INSERT 到当前的时间

'CREATE_TIME' timestamp NOT NULL DEFAULT **CURRENT_TIMESTAMP** **ON UPDATE CURRENT_TIMESTAMP**

自动INSERT 到当前时间，不过不自动UPDATE

'CREATE_TIME' timestamp NOT NULL DEFAULT **CURRENT_TIMESTAMP**

**CURRENT_TIMESTAMP**

系统时间

**ON UPDATE CURRENT_TIMESTAMP**

修改现有记录的时候都对这个数据列刷新

[timestamp的默认值](http://www.cnblogs.com/huangzhen/archive/2011/08/21/2147938.html)

[timestamp的默认值2](http://www.jb51.net/article/31872.htm)

慧聘中的实际应用

拿慧聘项目职位表为例：

我们需要记录职位的创建时间和最新的更新时间。创建时间只在insert的时候新建一个时间戳，而更新时间则是，只要有对这条数据的更新操作，就更改时间戳。

timestamp的ON UPDATE CURRENT_TIMESTAMP属性，会让数据库来帮我们在update的时候自动更新时间。

注意：图形化界面来创建表的时候，默认值都是null，不是CURRENT_TIMESTAMP。就是说数据库不会自动在insert的时候，插入当前时间戳。


图形化界面的自动update，需要勾选上图中的 根据当前时间戳更新选项

Mysql查看表的创建sql

SHOW CREATE TABLE BASE_USER