Convert MySql DateTime stamp into JavaScript's Date format
JavascriptJqueryMysqlDatetimeJavascript Problem Overview
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!
Javascript Solutions
Solution 1 - Javascript
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).
Solution 2 - Javascript
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());
Solution 3 - Javascript
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.
Solution 4 - Javascript
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)
Solution 5 - Javascript
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);
Solution 6 - Javascript
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();
Solution 7 - Javascript
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)
Solution 8 - Javascript
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;
};
});
Solution 9 - Javascript
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]);
Solution 10 - Javascript
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');
Solution 11 - Javascript
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()}`;
},
Solution 12 - Javascript
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
Solution 13 - Javascript
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.
Solution 14 - Javascript
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]);
}
Solution 15 - Javascript
Why not do this:
var d = new Date.parseDate( "2000-09-10 00:00:00", 'Y-m-d H:i:s' );