MySQL的日期格式

摘要:
另外有很特殊的一点:TIMESTAMP的数值是与时区相关。**6.当MySQL遇到一个日期或时间类型的超出范围或对于该类型不合法的值时,它将该值转换为该类的"零"值。列类型:"零"值DATETIME:'0000-00-0000:00:00'DATE:'0000-00-00'TIMESTAMP:00000000000000TIME:'00:00:00'YEAR:0000datetime和timestamp的区别datetime1.占用8个字节2.允许为空值,可以自定义值,系统不会自动修改其值。

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的时候,插入当前时间戳。

![cmd-markdown-logo](http://7xrnr1.com1.z0.glb.clouddn.com/QQ%E6%88%AA%E5%9B%BE20160308185547.png)

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

Mysql查看表的创建sql
SHOW CREATE TABLE BASE_USER

免责声明:文章转载自《MySQL的日期格式》仅用于学习参考。如对内容有疑问,请及时联系本站处理。

上篇图书管理系统(毕业论文)IDA strings view 中文字符的显示下篇

宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=

相关文章

从零搭建 ES 搜索服务(二)基础搜索

一、前言 上篇介绍了 ES 的基本概念及环境搭建,本篇将结合实际需求介绍整个实现过程及核心代码。 二、安装 ES ik 分析器插件 2.1 ik 分析器简介 GitHub 地址:https://github.com/medcl/elasticsearch-analysis-ik 提供两种分词模式:「 ik_max_word 」及「 ik_smart 」...

CentOS7安装zabbix

二进制包安装    centos 7 添加阿里云镜像 wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo yum install epel-release 安装常用的开发组件 yum groups install "Develo...

[VC++]一些常用数据类型的使用

我们先定义一些常见类型变量借以说明 int i = 100; long l = 2001; float f=300.2; double d=12345.119; char username[]="bone"; char temp[200]; char *buf; CString str; _variant_t v1; _bstr_t v2; 一、其它数据类...

MySQL杂记select 权限范围变更史

前言: 在5.7版本中无意间发现,只有 select 权限的用户,是可以执行 select ... for update / select ... lock in share mode 命令的,一时想不出在什么场景下,会存在只有 select 权限的用户,需要对记录加锁操作, 复现: ---session1: mysql> create databa...

mysql 判断null 和 空字符串

mysql 判断null 和 空字符串 1.在mysql中null 不能使用任何运算符与其他字段或者变量(函数、存储过程)进行运算。若使用运算数据就可能会有问题。 2.对null 的判断: 创建一个user表:id 主健 name 可以为空 select * from user; insert into user values('33',null); #...

C++ MySQL编程

MySQL编程需要包含<mysql.h>头文件。该文件一般在MySQL安装目录下的include文件夹下。 包含头文件还不够,还需要包含“libmysql.lib”库,一般在lib文件夹下。 MySQL编程与Socket编程非常相似,唯一不同的是MySQL使用的是MYSQL结构体,而Socket使用的是SOCKET。因此需要先构建一个MYSQL...