MySQL 中 UNIX_TIMESTAMP 和 NOW() 的区别

Difference between UNIX_TIMESTAMP and NOW() in MySQL

提问by sanchitkhanna26

I have a blog where users can comment. I insert the time at which they posted a comment using NOW()and then use date('j M Y', stored timestamp)to show the time at which they posted.

我有一个博客，用户可以在其中发表评论。我插入他们发表评论的时间，NOW()然后用date('j M Y', stored timestamp)来显示他们发表评论的时间。

I want to know does NOW() return the locatime of the end user or the localtime at my server. Is it better suited to use UNIX_TIMESTAMPthan NOW()to calculate the localtime at which users posted a comment.

我想知道 NOW() 是否返回locatime of the end user or the localtime at my server. 它是否UNIX_TIMESTAMP比NOW()计算用户发表评论的本地时间更适合使用。

采纳答案by Ja?ck

The function NOW()generates a formatted date-time string, determined by the time zone of your MySQL server.

该函数NOW()生成一个格式化的日期时间字符串，由您的 MySQL 服务器的时区决定。

However, it would be better to store times using UNIX_TIMESTAMP(), which is expressed in GMT. Doing so makes it easier to format it according to the country of a visitor (e.g. using JavaScript).

但是，最好使用UNIX_TIMESTAMP()以 GMT 表示的时间存储时间。这样做可以更容易地根据访问者的国家（例如使用 JavaScript）对其进行格式化。

If you still want to use DATETIMEcolumns, you can store times using UTC_TIMESTAMP()(it formats a date like NOW()but expresses it in UTC); it should more or less work the same in all other aspects.

如果你仍然想使用DATETIME列，你可以使用存储时间UTC_TIMESTAMP()（它格式化日期，NOW()但用UTC表示）；它在所有其他方面应该或多或少都一样。

回答by user2001117

MySQL UNIX_TIMESTAMP()returns a Unix timestamp in seconds since '1970-01-01 00:00:00' UTC as an unsigned integer if no arguments are passed with UNIT_TIMESTAMP().

UNIX_TIMESTAMP()如果没有传递参数，MySQL将自 '1970-01-01 00:00:00' UTC 起以秒为单位返回 Unix 时间戳作为无符号整数UNIT_TIMESTAMP().

When this function used with date argument, it returns the value of the argument as an unsigned integer in seconds since '1970-01-01 00:00:00' UTC.

当此函数与 date 参数一起使用时，它以自 '1970-01-01 00:00:00' UTC 以来的秒数作为无符号整数返回参数值。

Argument may be a DATE, DATETIME,TIMESTAMP or a number in YYYYMMDD or YYMMDD.

参数可以是 DATE、DATETIME、TIMESTAMP 或 YYYYMMDD 或 YYMMDD 中的数字。

Note : Since UNIX_TIMESTAMP()works on current datetime, your output may vary from the output shown.

注意：由于UNIX_TIMESTAMP()适用于当前日期时间，您的输出可能与显示的输出不同。

NOW()returns the current date and time.

NOW()返回当前日期和时间。

SELECT NOW(), UNIX_TIMESTAMP(NOW());
+---------------------+-----------------------+
| NOW()               | UNIX_TIMESTAMP(NOW()) |
+---------------------+-----------------------+
| 2011-10-03 10:22:37 |            1317666157 |
+---------------------+-----------------------+

回答by álvaro González

Let's see what the manual has to say about NOW():

让我们看看手册是怎么说NOW() 的：

Returns the current date and timeas a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.

以 'YYYY-MM-DD HH:MM:SS' 或 YYYYMMDDHHMMSS.uuuuuu 格式的值返回当前日期和时间，具体取决于函数是在字符串还是数字上下文中使用。该值以当前时区表示。

... and UNIX_TIMESTAMP():

...和UNIX_TIMESTAMP()：

If called with no argument, returns a Unix timestamp(seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD. The server interprets date as a value in the current time zone and converts it to an internal value in UTC.

如果不带参数调用，则返回 Unix 时间戳（自 '1970-01-01 00:00:00' UTC 以来的秒数）作为无符号整数。如果使用日期参数调用 UNIX_TIMESTAMP()，它会返回参数值作为自 '1970-01-01 00:00:00' UTC 以来的秒数。date 可以是 DATE 字符串、DATETIME 字符串、TIMESTAMP 或格式为 YYMMDD 或 YYYYMMDD 的数字。服务器将日期解释为当前时区中的值，并将其转换为 UTC 中的内部值。

So, to begin with, they return different things: a proper date versus an integer.

因此，首先，它们返回不同的东西：正确的日期与整数。

You actually need to get three features:

您实际上需要获得三个功能：

1. Store all dates in the same format (either UTC or the server's time zone)
2. Obtain user's time zone
3. Display stored date in user's time zone

1. 以相同的格式（UTC 或服务器的时区）存储所有日期
2. 获取用户的时区
3. 在用户的时区显示存储的日期

The Date and Time functionschapter offers a summary of available functions. If you want to store dates in UTC you'd go for UTC_TIMESTAMP(). If you want to use server's time zone you can use NOW(). And there's CONVERT_TZ()to make conversions.

该日期和时间功能的章节都提供了可用功能的摘要。如果您想以 UTC格式存储日期，您可以使用UTC_TIMESTAMP()。如果你想使用服务器的时区，你可以使用NOW()。还有CONVERT_TZ()进行转换。

MySQL, however, won't help you with point #2. You need to either ask the user or use JavaScript to read user's clock and send it to the server so you can guess (if you don't ask you'll always need to guess because there're normally several time zones that share the same time in a given instant).

但是，MySQL 不会帮助您解决第 2 点。您需要询问用户或使用 JavaScript 读取用户的时钟并将其发送到服务器以便您可以猜测（如果您不询问，您将始终需要猜测，因为通常有几个时区共享相同给定时刻的时间）。