Convert MySql DateTime stamp into JavaScript's Date format

Question

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!

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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)

Answer 5

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

Answer 6

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

Answer 7

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)

Answer 8

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

Answer 9

How to manipulate MySQL timestamp in Javascript

I had to deal with this topic right today

so I have mixed two functions that I'd like to share

function mysqlDatetoJs(mysqlTimeStamp){
    var t = mysqlTimeStamp.split(/[- :]/);
        return new Date(t[0], t[1]-1, t[2], t[3], t[4], t[5]);
    }

and then

function prettyDate(date) {
    var months =  ['Gen', 'Feb', 'Mar', 'Apr', 'Mag', 'Giu',
                    'Lug', 'Ago', 'Set', 'Ott', 'Nov', 'Dic'];

    return date.getUTCDate() + ' ' + months[date.getUTCMonth()] 
    + ' ' + date.getUTCFullYear();
    }

I have used it in building a dynamic table like this

td.innerHTML = prettyDate(mysqlDatetoJs(array[i].data));

and as result in the TD it is printed the date

4 Set 2021

given that array[i].data is the date record from the MySQL table and in particular for this reply it comes in the form

2021-09-04 11:25:20

hope it can be useful

Though I have found a clean date() explanation in this page https://www.digitalocean.com/community/tutorials/understanding-date-and-time-in-javascript

Answer 10

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

Answer 11

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

Answer 12

You can construct a layout like this by using the Js Date() methods before provide your string date to Date object:

 parseDate(date){
         const formated = new Date(date)
         
         return `${formated.getFullYear()}/${formated.getMonth()}/${formated.getDay()}`;
     },

Answer 13

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.

Answer 14

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

Answer 15

Why not do this:

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