datetime和timestamp的区别

时间日期数据类型总概况

MySQL中有多种表示时间日期的数据类型,主要有YEAR、TIME、DATE、DATETIME、TIMESTAMP等。每一种数据类型都有存储的时间日期格式、以及取值范围,因此在使用时间日期数据类型的时候需要选取最佳的数据类型。

 

 

 此处注意发现:Time类型HH的取值竟然是800+,而不是我们本以为的24以内的数字,具体原因,下面会有解释

各类型详细讲解

1、Year

见名之意,year用于存储年,存储时只需要一个字节,插入数据时可以使用各种格式指定YEAR值(非常节约内存,所以当你只需要年的时候,用此字段合适)。

支持的常见插入格式为:

  1. (推荐甚至强制要求必须)四位字符串或者数字,范围为“1901”~“2155”,写多少即为多少
  2. 两位字符串格式:范围为“00”“99”,"00""69"被转化为20**(例如:“16”转化为“2016”),“70”~“99”被转化为19**(下同)
  3. 两位数字格式,范围为199,169被转化为20**(例如:1转化为2001),70~99被转化为19**
2、Time

time用于存储时间信息,存储时需要三个字节。
虽然,小时的范围是0~~23,但是为了表示某种特殊需要的时间间隔,将Time类型的范围扩大了。而且还支持了负值。

支持的常见插入格式为:

  1. (推荐甚至强制要求必须)字符串格式:‘HH:MM:SS’,‘HH:MM’,‘D HH:MM’,‘D HH’,'SS’等形式。举个例子,输入‘30’,Time类型会自动转换为00:00:30。
  2. HHMMSS’格式的字符串或HHMMSS格式的数值表示,例如,输入‘123456’,Time类型会转换成12:34:56;输入123456,Time类型会转换成12:34:56。如果输入0或者‘0’,那么TIME类型会转换为0000:00:00。
  3. ‘D HH:MM:SS’格式的字符串表示。其中,D表示天数,取值范围是0~~34。保存时,小时的值等于(D*24+HH)。举个例子,输入‘2 11:30:50’,Time类型会转换为59:30:50。

 

  1. 使用current_time或者current_time()或者now()输入当前系统时间。(一般用于默认值)
3、Date

date用于存储日期,没有时间部分,存储时需要三个字节。

支持的常见插入格式为:

  1. (推荐甚至强制要求必须) ‘YYYY-MM-DD’或‘YYYYMMDD’格式的字符串表示,这种方式可以表达的范围是‘1000-01-01’~‘9999-12-31’。
  2. MySQL中还支持一些不严格的语法格式,任何标点都可以用来做间隔符。如’YYYY/MM/DD‘,’YYYY@MM@DD‘,’YYYY.MM.DD‘等分隔形式。举个例子,输入’2011.3.8‘,date类型将转换为2011-03-08。
  3. ’YY-MM-DD‘或者’YYMMDD‘格式的字符串表示,其中’YY‘的取值,’00‘’69‘转换为20002069,’70‘’99‘转换为19701999。与year类型类似。
  4. 使用current_date或now()来输入当前系统时间。
4、Datetime

datetime类型使用8个字节来表示日期和时间。

支持的常见插入格式为:

  1. (推荐甚至强制要求必须)‘YYYY-MM-DD HH:MM:SS’或‘YYYYMMDDHHMMSS’格式的字符串表示。这种方式可以表达的范围是‘1000-01-01 00:00:00’~~‘9999-12-31 23:59:59’。
  2. MySQL中还支持一些不严格的语法格式,任何的标点都可以用来做间隔符。情况与date类型相同,而且时间部分也可以使用任意的分隔符隔开,这与Time类型不同,Time类型只能用‘:’隔开呢。
  3. 使用now()来输入当前系统日期和时间。
5、timestamp

timestamp类型使用4个字节来表示日期和时间。

支持的常见插入格式为:

二者主要区别在于取值范围。

  1. timestamp存储需要四个字节,它的取值范围为“1970-01-01 00:00:01” UTC ~ “2038-01-19 03:14:07” (和时区有关)
  2. 而datetime取值范围为“1000-01-01 00:00:00” ~ “9999-12-31 23:59:59”(和时区无关,怎么存入怎么返回,对程序员友好)

2、timestamp类型还有一个很大的特殊点,就是时间是根据时区来显示的。
例如,在东八区插入的timestamp类型为2009-09-30 14:21:25,在东七区显示时,时间部门就变成了13:21:25,在东九区显示时,时间部门就变成了15:21:25。
3、需要显示日期与时间,timestamp类型需要根据不同地区的时区来转换时间,但是,timestamp类型的范围太小,其最大时间为2038-01-19 11:14:07。
如果插入时间的比这个大,将会数据库插入0000-00-00 00:00:00。所以需要的时间范围比较大,还是选择dateTime类型比较安全。

MySQL中如何表示当前时间?

其实,表达方式还是蛮多的,汇总如下:
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP()
NOW()
LOCALTIME
LOCALTIME()
LOCALTIMESTAMP
LOCALTIMESTAMP()

 

posted on 2019-09-03 12:32  情陌人灬已不在  阅读(14065)  评论(1编辑  收藏  举报

导航