CODEWITHSUNDEEP
javascriptmysqldatetimezone
krunal shah
krunal shah

Reputation: 16339

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();
+---------------------+---------------------+
| NOW()               | UTC_TIMESTAMP()     |
+---------------------+---------------------+
| 2014-03-18 18:55:04 | 2014-03-18 18:55:04 |
+---------------------+---------------------+

mysql> select UNIX_TIMESTAMP('2014-03-18') ;
+------------------------------+
| UNIX_TIMESTAMP('2014-03-18') |
+------------------------------+
|                   1395118800 |

Upvotes: 0

Views: 267

Answers (1)

eggyal
eggyal

Reputation: 125855

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

Upvotes: 2

Related Questions