Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert MySql DateTime stamp into JavaScript's Date format

Some of the answers given here are either overcomplicated or just will not work (at least, not in all browsers). If you take a step back, you can see that the MySQL timestamp has each component of time in the same order as the arguments required by the Date() constructor.

All that's needed is a very simple split on the string:

// Split timestamp into [ Y, M, D, h, m, s ]
var t = "2010-06-09 13:12:01".split(/[- :]/);

// Apply each element to the Date function
var d = new Date(Date.UTC(t[0], t[1]-1, t[2], t[3], t[4], t[5]));

console.log(d);
// -> Wed Jun 09 2010 14:12:01 GMT+0100 (BST)

Fair warning: this assumes that your MySQL server is outputting UTC dates (which is the default, and recommended if there is no timezone component of the string).


To add to the excellent Andy E answer a function of common usage could be:

Date.createFromMysql = function(mysql_string)
{ 
   var t, result = null;

   if( typeof mysql_string === 'string' )
   {
      t = mysql_string.split(/[- :]/);

      //when t[3], t[4] and t[5] are missing they defaults to zero
      result = new Date(t[0], t[1] - 1, t[2], t[3] || 0, t[4] || 0, t[5] || 0);          
   }

   return result;   
}

In this way given a MySQL date/time in the form "YYYY-MM-DD HH:MM:SS" or even the short form (only date) "YYYY-MM-DD" you can do:

var d1 = Date.createFromMysql("2011-02-20");
var d2 = Date.createFromMysql("2011-02-20 17:16:00");
alert("d1 year = " + d1.getFullYear());

I think I may have found a simpler way, that nobody mentioned.

A MySQL DATETIME column can be converted to a unix timestamp through:

SELECT unix_timestamp(my_datetime_column) as stamp ...

We can make a new JavaScript Date object by using the constructor that requires milliseconds since the epoch. The unix_timestamp function returns seconds since the epoch, so we need to multiply by 1000:

SELECT unix_timestamp(my_datetime_column) * 1000 as stamp ...

The resulting value can be used directly to instantiate a correct Javascript Date object:

var myDate = new Date(<?=$row['stamp']?>);

Hope this helps.


One liner for modern browsers (IE10+):

var d = new Date(Date.parse("2010-06-09 13:12:01"));
alert(d); // Wed Jun 09 2010 13:12:01 GMT+0100 (GMT Daylight Time)

And just for fun, here's a one-liner that will work across older browsers (now fixed):

new (Function.prototype.bind.apply(Date, [null].concat("2010-06-09 13:12:01".split(/[\s:-]/)).map(function(v,i){return i==2?--v:v}) ));
alert(d); // Wed Jun 09 2010 13:12:01 GMT+0100 (GMT Daylight Time)