数据库上datetime和timestamp的区别

数据库上datetime和timestamp的区别

  • timestamp存储的时间与时区有关，变换时区数据会受影响；
  • datetime与时区无关；

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

datetime和timestamp mysql 底层存储 数据库datetime和timestamp区别_字符串


从上表可以看出，timestamp存储时间范围小，1970-01-01 00:00:00到2038-01-19 03:14:07；datetime没有这个限制。

所以工作中要尽量用datetime。

各类型详细讲解

1. Year

  1. year用于存储年
  2. 存储时只需要一个字节
  3. 插入数据时可以使用各种格式指定YEAR值
  4. 非常节约内存，所以当你只需要年的时候，用此字段合适

2. Time

  1. time用于存储时间信息
  2. 存储时需要三个字节。
  3. 小时的范围是0~~23，但是为了表示某种特殊需要的时间间隔，将Time类型的范围扩大了。而且还支持了负值。
  4. 常见插入格式为
  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。

使用current_time或者current_time()或者now()输入当前系统时间。（一般用于默认值）

3. Date

  1. date用于存储日期
  2. 没有时间部分
  3. 存储时需要三个字节
  4. 支持的常见插入格式为：
  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类型类似。

使用current_date或now()来输入当前系统时间。

4. Datetime

  1. 使用8个字节来表示日期和时间
  2. 支持的常见插入格式为：
  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类型只能用‘:’隔开呢。

使用now()来输入当前系统日期和时间。

5. timestamp

  1. 使用4个字节来表示日期和时间
  2. 二者主要区别在于取值范围。
  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”（和时区无关，怎么存入怎么返回，对程序员友好）
  1. timestamp类型还有一个很大的特殊点，就是时间是根据时区来显示的
  2. 范围太小，其最大时间为2038-01-19 11:14:07