[javascript] Convert MySql DateTime stamp into JavaScript's Date format

Does anyone know how I can take a MySQL datetime data type value, such as YYYY-MM-DD HH:MM:SS and either parse it or convert it to work in JavaScript's Date() function, for example:- Date('YYYY, MM, DD, HH, MM, SS);

Thank you!

Recent versions of JavaScript will read an ISO8601 formatted date, so all you have to do is change the space to a 'T', doing something like one of the following:

#MySQL
select date_format(my_date_column,'%Y-%m-%dT%T') from my_table;

#PHP
$php_date_str = substr($mysql_date_str,0,10).'T'.substr($mysql_date_str,11,8);

//JavaScript
js_date_str = mysql_date_str.substr(0,10)+'T'+mysql_date_str.substr(11,8);

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.

From Andy's Answer, For AngularJS - Filter

angular
    .module('utils', [])
.filter('mysqlToJS', function () {
            return function (mysqlStr) {
                var t, result = null;

                if (typeof mysqlStr === 'string') {
                    t = mysqlStr.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;
            };
        });

Why not do this:

var d = new Date.parseDate( "2000-09-10 00:00:00", 'Y-m-d H:i:s' );

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)

To add even further to Marco's solution. I prototyped directly to the String object.

String.prototype.mysqlToDate = String.prototype.mysqlToDate || function() {
    var t = this.split(/[- :]/);
    return new Date(t[0], t[1]-1, t[2], t[3]||0, t[4]||0, t[5]||0);
};

This way you can go directly to:

var mySqlTimestamp = "2011-02-20 17:16:00";
var pickupDate = mySqlTimestamp.mysqlToDate();

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());

There is a simpler way, sql timestamp string:

2018-07-19 00:00:00

The closest format to timestamp for Date() to receive is the following, so replace blank space for "T":

var dateString = media.intervention.replace(/\s/g, "T");

"2011-10-10T14:48:00"

Then, create the date object:

var date = new Date(dateString);

result would be the date object:

Thu Jul 19 2018 00:00:00 GMT-0300 (Horário Padrão de Brasília)

You can use unix timestamp to direct:

SELECT UNIX_TIMESTAMP(date) AS epoch_time FROM table;

Then get the epoch_time into JavaScript, and it's a simple matter of:

var myDate = new Date(epoch_time * 1000);

The multiplying by 1000 is because JavaScript takes milliseconds, and UNIX_TIMESTAMP gives seconds.

First you can give JavaScript's Date object (class) the new method 'fromYMD()' for converting MySQL's YMD date format into JavaScript format by splitting YMD format into components and using these date components:

Date.prototype.fromYMD=function(ymd)
{
  var t=ymd.split(/[- :]/); //split into components
  return new Date(t[0],t[1]-1,t[2],t[3]||0,t[4]||0,t[5]||0);
};

Now you can define your own object (funcion in JavaScript world):

function DateFromYMD(ymd)
{
  return (new Date()).fromYMD(ymd);
}

and now you can simply create date from MySQL date format;

var d=new DateFromYMD('2016-07-24');

A quick search in google provided this:

 function mysqlTimeStampToDate(timestamp) {
    //function parses mysql datetime string and returns javascript Date object
    //input has to be in this format: 2007-06-05 15:26:02
    var regex=/^([0-9]{2,4})-([0-1][0-9])-([0-3][0-9]) (?:([0-2][0-9]):([0-5][0-9]):([0-5][0-9]))?$/;
    var parts=timestamp.replace(regex,"$1 $2 $3 $4 $5 $6").split(' ');
    return new Date(parts[0],parts[1]-1,parts[2],parts[3],parts[4],parts[5]);
  }

Source:http://snippets.dzone.com/posts/show/4132

var a=dateString.split(" ");
var b=a[0].split("-");
var c=a[1].split(":");
var date = new Date(b[0],(b[1]-1),b[2],b[0],c[1],c[2]);