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!

Answers


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)

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

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

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

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

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

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.


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


Why not do this:

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

Need Your Help

Android add Json object to google marker

java android json google-maps

I am trying to add data i read from an API to be the Lat and Long of my markers.