Geek Answers Handbook

Difference between UNIX_TIMESTAMP and NOW () in MySQL

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

I want to know that NOW () returns locatime of the end user or the localtime at my server . Is it better to use UNIX_TIMESTAMP than NOW() to calculate the local time that users posted a comment.

+6
source share
3 answers

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

However, it would be better to store time using UNIX_TIMESTAMP() , which is expressed in GMT. This simplifies formatting depending on the country of the visitor (for example, using JavaScript).

If you still want to use DATETIME columns, you can store time using UTC_TIMESTAMP() (it formats the date, for example NOW() , but expresses it in UTC); it should more or less work the same in all other aspects.

+3
source

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

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

The argument can be DATE, DATETIME, TIMESTAMP, or a number in YYYYMMDD or YYMMDD.

Note. Since UNIX_TIMESTAMP() works with the current time and time, your output may differ from the displayed one.

NOW() returns the current date and time.

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

See what the manual should say NOW () :

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

... and UNIX_TIMESTAMP () :

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

So, for starters, they return different things: the correct date and an integer.

You really need to get three functions:

  • Save all dates in the same format (UTC or server time zone)
  • Get user timezone
  • Display saved date in user time zone

The date and time functions in the chapter offer a summary of the available functions. If you want to store dates in UTC, you must go UTC_TIMESTAMP () . If you want to use the server time zone, you can use NOW () . And CONVERT_TZ () to do conversions.

MySQL, however, will not help you with point 2. You need to either ask the user or use JavaScript to read the user clock and send it to the server so you can guess (if you do not ask, you always need to guess, because usually there are several time zones that use the same time in this moment).

+1
source

More articles:


All Articles