如果您想决定是使用UNIX时间戳还是本机MySQL日期时间字段,请使用本机格式。您可以通过这种方式在MySQL中进行计算 ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") ("SELECT UNIX_TIMESTAMP(my_datetime)")

在MySQL 5及以上版本中, 值从当前时区转换为UTC进行存储,并从UTC转换回当前时区进行检索。(这仅适用于时间戳数据类型,以及 不

默认情况下,每个连接的当前时区都是服务器的时间。时区可以根据每个连接进行设置,如中所述 MySQL Server Time Zone Support .

除了行元数据(创建或修改的日期),我总是使用DATETIME字段。

mentioned 在MySQL文档中:

DATETIME类型在需要同时包含日期和时间信息的值时使用。MySQL以“YYYY-MM-DD HH:MM:SS”格式检索并显示日期时间值。支持的范围为“1000-01-01 00:00:00”到“9999-12-31 23:59:59”。

...

一般情况下,您很可能会达到时间戳的下限,例如存储生日。

下面的示例显示了 TIMESTAMP 日期类型在更改日期后更改了值 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: Datetime Versus Timestamp Data Types

主要区别在于DATETIME是常量,而时间戳受 time_zone 背景

因此,只有当你已经——或者将来可能已经——跨时区同步集群时,这才重要。

简而言之: 如果我在澳大利亚有一个数据库,并转储该数据库以同步/填充美国的数据库,则时间戳将更新以反映新时区中事件的实时性,而DATETIME仍将反映非盟时区中事件的时间 .

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

也不

1. 您可以避免供应商锁定。几乎每个数据库都以相对相似的方式支持整数。假设您要移动到另一个数据库。你想担心MySQL的DATETIME值和Oracle如何定义它们之间的差异吗?即使在不同版本的MySQL中,时间戳也有不同的精度。直到最近,MySQL才在时间戳中支持毫秒。
2. 没有时区问题。这里有一些关于不同数据类型的时区的深刻评论。但这是常识吗?你的同事会花时间去学习吗?另一方面,将BigINT更改为java.util.Date非常困难。使用BIGINT会导致很多时区问题被搁置一旁。
3. 无需担心范围或精度。你不必担心未来的日期范围会缩短什么(时间戳只会到2038年)。

这个问题与如何在数据库中存储货币价值(即$1.99)密切相关。您应该使用十进制,还是数据库的货币类型,或者最糟糕的是使用双精度?由于上述许多相同的原因,这三种选择都很糟糕。解决方案是使用BIGINT以美分为单位存储货币价值,然后在向用户显示价值时将美分转换为美元。数据库的任务是存储数据,而不是对这些数据进行大胆的报道。您在数据库(尤其是Oracle)中看到的所有这些奇特的数据类型几乎没有添加任何内容,并且会让您开始锁定供应商。

1. TIMESTAMP 是四个字节对八个字节 DATETIME

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

3. 这个 日期时间 日期时间 价值观 YYYY-MM-DD HH:MM:SS 总体安排支持的范围为 1000-01-01 00:00:00 到 9999-12-31 23:59:59 . 这个 时间戳 数据类型的范围为 1970-01-01 00:00:01 UTC 到 2038-01-09 03:14:07 UTC . 它有不同的属性,这取决于MySQL版本和服务器运行的SQL模式。

4. 日期时间 时间戳 受到 time_zone 背景

时间戳是4字节,而DATETIME是8字节。

http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

但正如斯克罗尼德所说,它的下限是1970年。不过,这对未来可能发生的任何事情都很好;)

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

因此,对于表示用户时间的值(如约会或日程安排),datetime更好。它允许用户控制所需的确切日期和时间,而不管服务器设置如何。设置时间是设置时间,不受服务器时区、用户时区或夏令时计算方式的更改影响(是的,它确实会更改)。

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

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

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

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

此外:

• DATETIME现在可以 自动地 设置为当前时间值 How do you set a default value for a MySQL Datetime column?
• 更快 http://gpshumano.blogs.dri.pt/2009/07/06/mysql-datetime-vs-timestamp-vs-int-performance-and-benchmarking-with-myisam/
• 时间戳仍然限于1970-2038年

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/date

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

注意:默认连接时区是服务器时区,但这可以(应该)在每个会话中更改(请参见 SET time_zone = ... ).

A. timestamp datetime 领域您可以创建 时间戳 列具有特殊属性;可以将其设置为在创建和/或更新时更新自身。

时间戳 上面有几个特殊情况的触发器。

也不这个 DATETIME 和 TIMESTAMP 对于泛型用例,类型从根本上被打破。MySQL将在将来改变它们。你应该使用 BIGINT 和UNIX时间戳,除非您有特定的理由使用其他东西。

以下是一些具体情况,在这些情况下,您的选择比较容易,您不需要本答案中的分析和一般建议。

• 仅日期 如果您只关心日期(如下一个农历新年的日期2022-02-01),并且您清楚地了解该日期适用的时区(或不关心,如农历新年),则使用 DATE 列类型。

• 记录插入时间 如果您正在为数据库中的行记录插入日期/时间,并且不关心应用程序 will break in the next 17 years ,然后继续使用 默认值为 CURRENT_TIMESTAMP() .

为什么 时间戳

时间戳

每次你 INSERT INTO SELECT FROM 时间戳

为什么 VARCHAR 破碎的

这个 瓦尔查尔 类型允许以ISO 8601格式明确存储非本地日期/时间/两者,并且它适用于2037年以后的日期。通常使用祖鲁时间,但ISO 8601允许对任何偏移量进行编码。这是不太有用的,因为MySQL日期和时间函数确实支持字符串作为预期的日期/时间/两者的输入,但如果输入使用时区偏移量,则结果是不正确的。

而且 瓦尔查尔 使用额外的存储字节。

日期时间 破碎的

A. 日期时间 储存 日期 和 TIME 在同一列中。除非时区被理解,否则这些东西都没有任何意义,并且时区不存储在任何地方。您应该将预期时区作为注释放在列中,因为时区与数据有着千丝万缕的联系。所以很少有人使用列注释,所以这是一个等待发生的错误。我从亚利桑那州继承了一台服务器,所以我总是需要将所有时间戳从亚利桑那州时间转换到另一个时间。

唯一的情况是 日期时间

你的2020年太阳新年正好从 DATETIME("2020-01-01 00:00:00") .

这对我来说没有其他的好处 日期时间

supports time zone offsets 在里面 日期时间 文字( thanks @Marko 日期时间

如何使用 比基特

定义:

CREATE TEMPORARY TABLE good_times (
    a_time BIGINT
)

插入特定值:

INSERT INTO good_times VALUES (
    UNIX_TIMESTAMP(CONVERT_TZ("2014-12-03 12:24:54", '+00:00', @@global.time_zone))
);

当然,这在您的应用程序中要好得多,例如:

$statement = $myDB->prepare('INSERT INTO good_times VALUES (?)');
$statement->execute([$someTime->getTimestamp()]);

选择:

SELECT a_time FROM good_times;

有一些过滤相对时间的技术(选择过去30天内的帖子,查找注册后10分钟内购买的用户)超出了这里的范围。

DATETIME、时间戳和日期之间的比较

• DATETIME或TIMESTAMP值可以包含尾随分数 秒部分精度高达微秒(6位)。在里面 特别是插入日期时间的值中的任何小数部分

资料来源:

• MySQL Date/Time data types reference
• MySQL Storage Requirements reference

值得注意的是,在MySQL中,您可以在创建表列时使用以下内容:

on update CURRENT_TIMESTAMP

这将在每次修改行时更新时间,有时对存储的上次编辑信息非常有用。这只适用于时间戳,而不适用于日期时间。

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

要获取PHP中的当前Unix时间戳,只需执行以下操作 time();
在MySQL中,你会做什么 SELECT UNIX_TIMESTAMP(); .

日期时间

例如,考虑一个 user 有一张桌子 注册日期 领域因为 表中,如果您想知道特定用户的上次登录时间,请使用 时间戳 键入以更新字段。

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

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

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

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

另一件值得考虑的事情是:

Reference taken from this Article:

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

时间戳还受不同时区相关设置的影响。

DATETIME不能这样做。

时间戳支持的范围: 1970-01-01 00:00:01 UTC至2038-01-19 03:14:07 UTC 1000-01-01 00:00:00至9999-12-31 23:59:59

我几乎总是喜欢时间戳而不是日期时间字段,因为时间戳隐式地包含时区。因此,由于从不同时区的用户访问应用程序的那一刻起,您就希望他们看到本地时区中的日期和时间,因此与将数据保存在datetime字段中相比,此字段类型的操作非常简单。

另外,在将数据库迁移到另一个时区的系统的情况下,使用时间戳会让我更加自信。更不用说在计算两个时刻之间的差异时可能出现的问题,这两个时刻之间的sumer时间变化需要1小时或更少的精度。

总之,我重视时间戳的这一优势:

• 时区间的轻松迁移
• 非常容易计算差异(只需减去两个时间戳)
• 不必担心夏天的日期

出于所有这些原因,我选择UTC&可能的时间戳字段。而且我避免头痛;)

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

UPDATE table
SET age = 30

+---------------------------------------------------------------------------------------+--------------------------------------------------------------------------+
|                                       TIMESTAMP                                       |                                 DATETIME                                 |
+---------------------------------------------------------------------------------------+--------------------------------------------------------------------------+
| TIMESTAMP requires 4 bytes.                                                           | DATETIME requires 8 bytes.                                               |
| Timestamp is the number of seconds that have elapsed since January 1, 1970 00:00 UTC. | DATETIME is a text displays 'YYYY-MM-DD HH:MM:SS' format.                |
| TIMESTAMP supported range: ‘1970-01-01 00:00:01′ UTC to ‘2038-01-19 03:14:07′ UTC.    | DATETIME supported range: ‘1000-01-01 00:00:00′ to ‘9999-12-31 23:59:59′ |
| TIMESTAMP during retrieval converted back to the current time zone.                   | DATETIME can not do this.                                                |
| TIMESTAMP is used mostly for metadata i.e. row created/modified and audit purpose.    | DATETIME is used mostly for user-data.                                   |
+---------------------------------------------------------------------------------------+--------------------------------------------------------------------------+

我发现时间戳能够根据当前时间自动更新自身,而无需使用不必要的触发器,这一点非常有用。这只是我的想法,虽然时间戳是UTC,就像人们说的那样。

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

用于跟踪记录更改的时间戳,并在记录每次更改时更新。

时间戳还受不同时区相关设置的影响。 日期时间是恒定的。

时间戳在内部将当前时区转换为UTC进行存储,并在检索期间将其转换回当前时区。

时间戳为4字节,日期时间为8字节。

1970-01-01 00:00:01 UTC至2038-01-19 03:14:07 UTC 支持的日期时间范围: 1000-01-01 00:00:00至9999-12-31 23:59:59

• 时间戳上的索引-有效
• Does not work

看看这个 post to see problems with Datetime indexing

我停止使用 datetime IMHO使用 timestamp 日期时间 .

当你问现在几点了?答案类似于“2019-02-05 21:18:30”,这不是完整的,没有定义的答案,因为它缺少另一部分,在哪个时区?华盛顿?莫斯科北京?

使用不带时区的datetimes意味着您的应用程序只处理1个时区,但是时间戳给您带来了 日期时间

以下是一些会让您后悔使用的案例 日期时间

1. (实际上,您应该始终在应用程序开始时定义时区,或者在PHP应用程序中定义请求处理)

   SET time_zone = '+2:00';
   

简言之

日期时间 =应用程序支持1个时区(用于插入和选择)

=应用程序支持任何时区(用于插入和选择)

这个答案只是为了强调时间戳的灵活性和易用性,当涉及时区时,它并没有涵盖任何其他的差异,比如 column size or range or fraction .

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

我更喜欢使用时间戳,以便将所有内容保持在一种常见的原始格式中,并在PHP代码或SQL查询中格式化数据。在某些情况下,在代码中,将所有内容都保存在几秒钟内非常方便。