mysql - timestamp范围 - 数据库时间戳




我应该在MySQL中使用日期时间或时间戳数据类型吗? (20)

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

我在服务器端使用PHP。


2016 + :我建议将Mysql时区设置为UTC并使用DATETIME:

任何最新的前端框架(Angular 1/2,react,Vue,...)都可以轻松自动地将您的UTC日期时间转换为当地时间。

另外:

(除非您可能更改服务器的时区)

AngularJs的示例

// back-end: format for angular within the sql query
SELECT DATE_FORMAT(my_datetime, "%Y-%m-%dT%TZ")...

// font-end Output the localised time
{{item.my_datetime | date :'medium' }}

此处提供了所有本地化时间格式: https://docs.angularjs.org/api/ng/filter/datehttps://docs.angularjs.org/api/ng/filter/date


  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是常量。

MySQL中的时间戳通常用于跟踪记录的更改,并且通常在每次更改记录时更新。 如果要存储特定值,则应使用datetime字段。

如果您想要在使用UNIX时间戳或本机MySQL日期时间字段之间做出决定,请使用本机格式。 您可以在MySQL中以这种方式进行计算("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") ,当您查询记录时,可以很容易地将值的格式更改为UNIX时间戳("SELECT UNIX_TIMESTAMP(my_datetime)")如果你想用PHP操作它。


TIMESTAMP始终为UTC(即1970-01-01以来经过的秒数,以UTC为单位),并且您的MySQL服务器会自动将其转换为服务器时区的日期/时间。 从长远来看,TIMESTAMP是最佳选择,因为您知道您的时态数据将始终为UTC。 例如,如果您迁移到其他服务器或更改了服务器上的时区设置,则不会将日期搞砸。


参考本文:

主要区别:

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'


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

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

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


主要区别在于DATETIME是常量,而TIMESTAMP受time_zone设置的影响。

因此,只有当您拥有 - 或者将来可能 - 跨时区同步集群时,这才有意义。

换句话说: 如果我在澳大利亚有一个数据库,并且转储该数据库以同步/填充美国的数据库,则TIMESTAMP将更新以反映新时区中事件的实际时间,而DATETIME将仍然反映了au时区的事件发生时间

应该使用TIMESTAMP的DATETIME的一个很好的例子是在Facebook,他们的服务器永远不能确定跨时区发生的时间。 一旦我正在进行对话,其中时间说我在实际发送消息之前回复了消息。 (当然,这也可能是由于消息传递软件中的时区转换不正确而导致发布时间而不是同步。)


以下示例显示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:日期时间与时间戳数据类型


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

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


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

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



我发现TIMESTAMP能够在不使用不必要的触发器的情况下根据当前时间自动更新自身的能力无与伦比。 这只是我,虽然TIMESTAMP是UTC,就像它说的那样。

它可以跟踪不同的时区,因此如果您需要显示相对时间,则UTC时间就是您想要的。


我建议既不使用DATETIME也不使用TIMESTAMP字段。 如果你想要表示一个特定的日子(如生日),那么使用DATE类型,但如果你比这更具体,你可能有兴趣记录一个实际的时刻,而不是一个单位的时间(日,周,月,年)。 使用BIGINT而不是使用DATETIME或TIMESTAMP,只需存储自纪元以来的毫秒数(如果您使用的是Java,则为System.currentTimeMillis())。 这有几个好处:

  1. 您可以避免供应商锁定。 几乎每个数据库都以相对类似的方式支持整数。 假设您要移动到另一个数据库。 您是否想要担心MySQL的DATETIME值与Oracle如何定义它们之间的差异? 即使在不同版本的MySQL中,TIMESTAMPS也具有不同的精度级别。 直到最近,MySQL才支持时间戳中的毫秒数。
  2. 没有时区问题。 关于具有不同数据类型的时区会发生什么,这里有一些富有洞察力的评论。 但这是常识,你的同事是否都会花时间去学习它? 另一方面,将BigINT更改为java.util.Date非常困难。 使用BIGINT会导致许多时区问题陷入困境。
  3. 不用担心范围或精度。 您不必担心未来的日期范围会缩短什么(TIMESTAMP只会到2038年)。
  4. 第三方工具集成。 通过使用整数,第三方工具(例如EclipseLink)与数据库接口是微不足道的。 并非每个第三方工具都会像MySQL那样对“日期时间”有同样的理解。 想要尝试在Hibernate中弄清楚,如果您使用这些自定义数据类型,是否应该使用java.sql.TimeStamp或java.util.Date对象? 使用基本数据类型可以轻松使用第三方工具。

这个问题与如何在数据库中存储货币价值(即1.99美元)密切相关。 你应该使用Decimal,还是数据库的Money类型,或者最糟糕的Double? 由于上面列出的许多相同原因,所有这三个选项都很糟糕。 解决方案是使用BIGINT将货币价值存储在美分中,然后在向用户显示价值时将美分转换为美元。 数据库的工作是存储数据,而不是用于表示数据。 您在数据库(尤其是Oracle)中看到的所有这些奇特的数据类型几乎没有增加,并开始向供应商锁定。


我总是使用Unix时间戳,只是为了在处理大量日期时间信息时保持理智,特别是在执行时区调整,添加/减去日期等时。 在比较时间戳时,这排除了时区的复杂因素,并允许您在服务器端处理(无论是应用程序代码还是数据库查询)中节省资源,因为您使用轻量级算术而不是更重的日期时间加/减功能。

另一件值得考虑的事:

如果您正在构建应用程序,那么您永远不会知道如何在线下使用您的数据。 如果你不得不比较你的数据集中的一堆记录,比如来自第三方API的一堆项目,并说,按照时间顺序排列它们,你会很高兴有您的行的Unix时间戳。 即使您决定使用MySQL时间戳,也要将Unix时间戳存储为保险。


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

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

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


根据我的经验,如果您想要一个只插入一次的日期字段,并且您不希望对该特定字段进行任何更新或任何其他操作,请使用日期时间

例如,考虑具有REGISTRATION DATE字段的user表。 在该user表中,如果您想知道特定用户的上次登录时间,请使用时间戳类型字段以便更新该字段。

如果要从phpMyAdmin创建表,则默认设置将在发生行更新时更新时间戳字段。 如果您的时间戳字段未使用行更新进行更新,则可以使用以下查询使时间戳字段自动更新。

ALTER TABLE your_table
      MODIFY COLUMN ts_activity TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

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


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

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

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

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

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

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


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

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

DATETIME与被选择FROM_UNIXTIME( integer_timestamp_column )

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


我喜欢Unix时间戳,因为你可以转换为数字而只是担心数字。另外,您可以添加/减去并获得持续时间等。然后将结果转换为日期格式。此代码查找文档的时间戳与当前时间之间经过的时间(以分钟为单位)。

$date  = $item['pubdate']; (etc ...)
$unix_now = time();
$result = strtotime($date, $unix_now);
$unix_diff_min = (($unix_now  - $result) / 60);
$min = round($unix_diff_min);




sqldatatypes