Getting Different Timestamp In Mysql And Javascript For The Same Date?

In Javascript new Date() Tue Mar 18 2014 18:54:17 GMT+0000 (GMT) Date.UTC(2014,03,18) 1397779200000 In Mysql mysql> SELECT NOW(), UTC_TIMESTAMP(); +-----------

Solution 1:

There are three differences:

1. Date.UTC() interprets its arguments in UTC, whereas UNIX_TIMESTAMP() interprets its arguments in the database session's timezone. From the update to your question it appears that this may not have had any effect as the database session's local timezone might be in UTC.

2. Date.UTC() returns a value in milliseconds since the UNIX epoch, whereas UNIX_TIMESTAMP() returns a value in seconds since the UNIX epoch: so they will always differ by a factor of 1000.

3. The month argument to Date.UTC() is zero-indexed, so a value of 03 indicates April whereas the date literal given to UNIX_TIMESTAMP() indicates March.

References are cited below.

JavaScript

As documented under Date.UTC() (emphasis added):

The UTC function differs from the Date constructor in two ways: it returns a time value as a Number, rather than creating a Date object, and it interprets the arguments in UTC rather than as local time.

Also, as documented under TimeClip() (emphasis added):

The operator TimeClip calculates a number of milliseconds from its argument, which must be an ECMAScript Number value.

Also, as documented under Month Number:

Months are identified by an integer in the range 0 to 11, inclusive.

[ deletia ]

A month value of 0 specifies January; 1 specifies February; 2 specifies March; 3 specifies April; 4 specifies May; 5 specifies June; 6 specifies July; 7 specifies August; 8 specifies September; 9 specifies October; 10 specifies November; and 11 specifies December.

MySQL

As documented under UNIX_TIMESTAMP(date) (emphasis added):

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. The server interprets date as a value in the current time zone and converts it to an internal value in UTC. Clients can set their time zone as described in Section 10.6, “MySQL Server Time Zone Support”.