TIMESTAMP vs.DATETIME MySQL [英] TIMESTAMP vs. DATETIME MySQL

问题描述

我需要将数据库从SQL转换为MySQL，并且成功完成了转换，但是，这个问题现在更适合MySQL。

I needed to convert a database from SQL to MySQL and did so successfully, however, this question is more geared towards MySQL now.

我注意到我能够在 Timestamp 列类型中插入 DateTime 字符串，根据我对Timestamp的了解，这一切都很好接受DateTime或实际时间戳，但是，我现在对 ON UPDATE CURRENT_TIMESTAMP 感到好奇，因为我将它放在数据库的列中，因为它只能在时间戳类型上使用。当它基于 CURRENT_TIMESTAMP YYYY-MM-DD HH：MM：SS c $ c>？

I noticed that I'm able to insert DateTime strings into a Timestamp column type, and that's all fine and good, from what I hear about Timestamp accepting either DateTime or actual Timestamps, however, I'm curious about the "ON UPDATE CURRENT_TIMESTAMP" now since I have it on a column of the database, since it is only capable of being used on Timestamp types. Would this use a timestamp that is not in the format: YYYY-MM-DD HH:MM:SS when it updates based on CURRENT_TIMESTAMP?

这将如何影响我从此列中提取的数据？

How would this influence the data that I pull from this column exactly?

例如，当某些日期可能是数字时间戳记而另一些日期可能是DATETIME格式时，是否很难从TIMESTAMP列中获取当前日期/时间？我是否应该仅使用DATETIME作为此列的类型，而没有 ON_UPDATE 的功能来避免此处来自数据库的混合数据出现问题？

For Example, would it be difficult to get the current date/time from the TIMESTAMP column when some dates might be a numeric timestamp, while others might be in DATETIME format? Should I just use DATETIME as the type for this column without the ON_UPDATE ability to avoid a problem with mixed data coming from the database here?

推荐答案

内部所有 TIMESTAMP 数据都存储为4字节整数，表示自 Unix时代。您在软件中看到的内容取决于软件如何显示此数据。例如，如果您将时区更改为其他时区，则会对从 TIMESTAMP 列读取的数据产生影响（因为Unix时区为UTC）

Internally all TIMESTAMP data is stored as 4 byte integer denoting number of seconds since Unix epoch. What you see presented in your software is dependent on how your software presents this data. For example, if you change your timezone to different one, it will have an effect on data read from TIMESTAMP column (beacuse Unix epoch is in UTC)

不会混合任何数据。 MySQL将负责将您的 YYYY-MM-DD HH：MM：SS 转换为时间戳。

There will be no mixing of data. MySQL will take care of converting your YYYY-MM-DD HH:MM:SS into timestamps. Be wary of timezones though!

在查询 TIMESTAMP 列时，MySQL默认将其格式设置为易于理解的格式，所以不要期望从这些整数中获取整数。您将获得一个格式为 YYYY-MM-DD HH：MM：SS 的字符串，就像在 DATETIME 字段中一样。

When querying TIMESTAMP columns, MySQL will by default format them in human readable form, so don't expect to get integers from these. You will get a string formatted in YYYY-MM-DD HH:MM:SS just like from DATETIME fields.

这篇关于TIMESTAMP vs.DATETIME MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！