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.
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_TIMESTAMP than NOW() to calculate the localtime at which users posted a comment.
The function NOW() generates a formatted date-time string, determined by the time zone of your MySQL server.
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).
If you still want to use DATETIME columns, 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.
date('j M Y', stored timestamp)
UNIX_TIMESTAMP() returns an integer. Are you suggesting dumping DATETIME columns in favour of INT?
new Date(<timestamp-from-db> * 1000)
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().
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.
Argument may be a DATE, DATETIME,TIMESTAMP or a number in YYYYMMDD or YYMMDD.
Note : Since UNIX_TIMESTAMP() works on current datetime, your output may vary from the output shown.
NOW() returns the current date and time.
SELECT NOW(), UNIX_TIMESTAMP(NOW());
+---------------------+-----------------------+
| NOW() | UNIX_TIMESTAMP(NOW()) |
+---------------------+-----------------------+
| 2011-10-03 10:22:37 | 1317666157 |
+---------------------+-----------------------+
Let's see what the manual has to say about NOW():
Returns the current date and time as 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.
... and 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.
So, to begin with, they return different things: a proper date versus an integer.
You actually need to get three features:
The Date and Time functions chapter 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.
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).
