timestamp范围 我应该在MySQL中使用日期时间或时间戳数据类型吗?




15 Answers

在MySQL 5及更高版本中, TIMESTAMP值从当前时区转换为UTC以进行存储,并从UTC转换回当前时区以进行检索。 (这仅适用于TIMESTAMP数据类型, 而不适用于其他类型,例如DATETIME。)

默认情况下,每个连接的当前时区是服务器的时间。 可以在每个连接的基础上设置时区,如MySQL服务器时区支持中所述

mysql timestamp格式

您会建议使用datetime datetimedatetime字段,以及为什么(使用MySQL)?

我在服务器端使用PHP。




以下示例显示TIMESTAMP日期类型如何在将time-zone to 'america/new_york'更改time-zone to 'america/new_york'DATETIME未更改)后更改值。

mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | Asia/Calcutta       |
+------------------+---------------------+

mysql> create table datedemo(
    -> mydatetime datetime,
    -> mytimestamp timestamp
    -> );

mysql> insert into datedemo values ((now()),(now()));

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+

mysql> set time_zone="america/new_york";

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+

我已将我的答案转换成文章,因此更多人可以找到这个有用的MySQL:日期时间与时间戳数据类型




我在语义基础上做出这个决定。

当我需要记录(或多或少)固定时间点时,我使用时间戳。 例如,当记录插入数据库或发生某些用户操作时。

当日期/时间可以任意设置和更改时,我使用日期时间字段。 例如,当用户可以保存以后更改约会时。




  1. 对于DATETIME,TIMESTAMP是四个字节对八个字节。

  2. 时间戳在数据库上也更轻,索引速度更快。

  3. 当您需要包含日期和时间信息的值时,将使用DATETIME类型。 MySQL以'YYYY-MM-DD HH:MM:SS'格式检索并显示DATETIME值。 支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。

TIMESTAMP数据类型的范围为'1970-01-01 00:00:01'UTC到'2038-01-09 03:14:07'UTC。 它具有不同的属性,具体取决于MySQL版本和运行服务器的SQL模式。

  1. 当TIMESTAMP受time_zone设置影响时,DATETIME是常量。



真的,取决于应用程序。

考虑将用户的时间戳设置为纽约的服务器,以便在Sanghai进行预约。 现在,当用户在Sanghai连接时,他从东京的镜像服务器访问相同的约会时间戳。 他将在东京时间看到这个任命,偏离原来的纽约时间。

因此,对于表示用户时间(如约会或计划)的值,日期时间更好。 无论服务器设置如何,它都允许用户控制所需的确切日期和时间。 设定时间是设定时间,不受服务器时区,用户时区或夏令时计算方式的变化的影响(是的,它确实发生了变化)。

另一方面,对于表示系统时间的值(如支付事务,表修改或日志记录),始终使用时间戳。 将服务器移动到另一个时区,或者在不同时区的服务器之间进行比较时,系统不会受到影响。

时间戳在数据库上也更轻,索引速度更快。




timestamp字段是datetime timestamp字段的特例。 您可以创建timestamp列以具有特殊属性; 它可以设置为在创建和/或更新时更新自身。

在“更大”的数据库术语中, timestamp上有几个特殊情况触发器。

正确的取决于你想做什么。




DATETIME,TIMESTAMP和DATE之间的比较

什么是[.fraction]?

  • DATETIME或TIMESTAMP值可以包括高达微秒(6位)精度的尾随小数秒部分。 特别是,插入DATETIME或TIMESTAMP列的值中的任何小数部分都将被存储而不是被丢弃。 这当然是可选的。

资料来源:




在使用MySQL和PHP时,我总是会使用Unix时间戳。 这是PHP中的默认date方法的主要原因是使用时间戳作为参数,因此不需要解析。

要在PHP中获取当前的Unix时间戳,只需执行time();
在MySQL中做SELECT UNIX_TIMESTAMP();




时间戳数据类型存储日期和时间,但是以UTC格式存储,而不是像datetime那样以当前时区格式存储。 当您获取数据时,时间戳再次将其转换为当前时区时间。

因此,假设您在美国并从具有美国时区的服务器获取数据。 然后,您将根据美国时区获取日期和时间。 时间戳数据类型列在其行更新时始终自动更新。 因此,跟踪上次更新特定行的时间非常有用。

有关详细信息,请阅读博客文章Timestamp Vs Datetime




在表上执行UPDATE语句时要小心时间戳更改。 如果您有一个包含列'Name'(varchar),'Age'(int)和'Date_Added'(timestamp)的表,并运行以下DML语句

UPDATE table
SET age = 30

然后,'Date_Added'列中的每个值都将更改为当前时间戳。




Timestamp和Datetime之间的另一个区别是在Timestamp中,您不能将默认值设置为NULL。




参考本文:

主要区别:

TIMESTAMP用于跟踪记录的更改,并在每次更改记录时更新。DATETIME用于存储特定值和静态值,不受记录中任何更改的影响。

TIMESTAMP也受到不同TIME ZONE相关设置的影响。DATETIME是不变的。

TIMESTAMP在内部将当前时区转换为UTC以进行存储,并在检索期间将其转换回当前时区。DATETIME无法做到这一点。

TIMESTAMP支持的范围:'1970-01-01 00:00:01'UTC到'2038-01-19 03:14:07'DESD DATETIME支持的范围:'1000-01-01 00:00:00'到'9999 -12-31 23:59:59'




就我而言,我每次都将UTC设置为所有内容的时区:系统,数据库服务器等。如果我的客户需要另一个时区,那么我在应用程序上配置它。

我几乎总是喜欢时间戳而不是日期时间字段,因为时间戳隐含地包含时区。因此,由于应用程序将从不同时区的用户访问,并且您希望他们在本地时区中查看日期和时间,因此该字段类型使得执行此操作非常容易,而不是将数据保存在日期时间字段中。

另外,在将数据库迁移到具有其他时区的系统的情况下,我会更自信地使用时间戳。不是说在计算两个时刻之间的差异时可能出现的问题,其中时间间隔发生变化,需要1小时或更短的精度。

总而言之,我重视时间戳的这些优点:

  • 准备在国际(多时区)应用程序上使用
  • 在时区之间轻松迁移
  • 很容易计算差异(只是减去两个时间戳)
  • 不用担心夏季进出日期

出于所有这些原因,我选择了有效的UTC和时间戳字段。我避免头痛;)




A TIMESTAMP需要4个字节,而a DATETIME需要8个字节。




我只是BIGINT在存储UTC时使用无符号...

然后仍然可以在PHP中调整到当地时间。

DATETIME与被选择FROM_UNIXTIME( integer_timestamp_column )

显然应该在该列上设置索引,否则就没有进展。




Related