9

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.

sanchitkhanna26
  • 2,143
  • 8
  • 28
  • 42

3 Answers3

9

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.

Ja͢ck
  • 170,779
  • 38
  • 263
  • 309
  • so now how do i calculate the country of user using javascript. I know to calculate the gmt but how the country @Jack – sanchitkhanna26 Feb 13 '13 at 09:17
  • would this print the date according to the user's country `date('j M Y', stored timestamp)` – sanchitkhanna26 Feb 13 '13 at 09:18
  • `UNIX_TIMESTAMP()` returns an integer. Are you suggesting dumping `DATETIME` columns in favour of `INT`? – Álvaro González Feb 13 '13 at 09:21
  • @RayZ JavaScript can do that easily by doing `new Date( * 1000)` – Ja͢ck Feb 13 '13 at 09:25
  • You can get the offset - see http://stackoverflow.com/questions/1194933/javascripts-date-gettimezoneoffset – Ed Heal Feb 13 '13 at 09:26
  • @ÁlvaroG.Vicario Alternatively you could store UTC dates as well, but OP didn't mention a server side language, so I didn't add that. – Ja͢ck Feb 13 '13 at 09:27
  • @EdHeal There's no need to determine the offset, JavaScript `Date()` can work with UTC timestamps. – Ja͢ck Feb 13 '13 at 09:27
  • You do for the client to convert the unix timestamp (UTC) by the client into local time. – Ed Heal Feb 13 '13 at 09:29
  • @Jack - I think you didn't get my point. When a DBMS offers a date column type and you prefer to store dates as integers or strings you lock yourself out of the specific date features. (Of course, you can always convert back to date, but you could store numbers as varchars and you don't normally do it.) – Álvaro González Feb 13 '13 at 09:43
  • note that DATETIME and TIMESTAMP columns don't account for daylight savings so if you need the values in the correct order use unix timestamp integers – Charon ME May 13 '20 at 10:54
7

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 |
+---------------------+-----------------------+
user2001117
  • 3,727
  • 1
  • 18
  • 18
3

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:

  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

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).

Álvaro González
  • 142,137
  • 41
  • 261
  • 360