Convert JS date to MySQL datetime

Question

Does anyone know how to convert JS dateTime to MySQL datetime? Also is there a way to add a certain number of minutes to JS datetime and then pass it to MySQL datetime?

Answer 1

While JS does have enough basic tools to do this, it's pretty awkward.

/** * You first need to create a formatting function to pad numbers to two digits… **/ function twoDigits(d) { if(0 <= d && d < 10) return "0" + d.toString(); if(-10 < d && d < 0) return "-0" + (-1*d).toString(); return d.toString(); } /** * …and then create the method to output the date string as desired. * Some people hate using prototypes this way, but if you are going * to apply this to more than one Date object, having it as a prototype * makes sense. **/ Date.prototype.toMysqlFormat = function() { return this.getUTCFullYear() + "-" + twoDigits(1 + this.getUTCMonth()) + "-" + twoDigits(this.getUTCDate()) + " " + twoDigits(this.getUTCHours()) + ":" + twoDigits(this.getUTCMinutes()) + ":" + twoDigits(this.getUTCSeconds()); }; 

Answer 2

 var date; date = new Date(); date = date.getUTCFullYear() + '-' + ('00' + (date.getUTCMonth()+1)).slice(-2) + '-' + ('00' + date.getUTCDate()).slice(-2) + ' ' + ('00' + date.getUTCHours()).slice(-2) + ':' + ('00' + date.getUTCMinutes()).slice(-2) + ':' + ('00' + date.getUTCSeconds()).slice(-2); console.log(date); 

or even shorter:

 new Date().toISOString().slice(0, 19).replace('T', ' '); 

Output:

 2012-06-22 05:40:06 

For more advanced use cases, including time zone management, consider using http://momentjs.com/ :

 require('moment')().format('YYYY-MM-DD HH:mm:ss'); 

For a lightweight alternative to momentjs , consider https://github.com/taylorhakes/fecha

 require('fecha').format('YYYY-MM-DD HH:mm:ss') 

Answer 3

I think the solution can be less awkward using the toISOString() method, it has wide browser compatibility.

So your expression will be single line:

 new Date().toISOString().slice(0, 19).replace('T', ' '); 

Generated Output:

"2017-06-29 17:54:04"

Answer 4

For an arbitrary date string,

 // Your default date object var starttime = new Date(); // Get the iso time (GMT 0 == UTC 0) var isotime = new Date((new Date(starttime)).toISOString() ); // getTime() is the unix time value, in milliseconds. // getTimezoneOffset() is UTC time and local time in minutes. // 60000 = 60*1000 converts getTimezoneOffset() from minutes to milliseconds. var fixedtime = new Date(isotime.getTime()-(starttime.getTimezoneOffset()*60000)); // toISOString() is always 24 characters long: YYYY-MM-DDTHH:mm:ss.sssZ. // .slice(0, 19) removes the last 5 chars, ".sssZ",which is (UTC offset). // .replace('T', ' ') removes the pad between the date and time. var formatedMysqlString = fixedtime.toISOString().slice(0, 19).replace('T', ' '); console.log( formatedMysqlString ); 

Or a single-line solution,

 var formatedMysqlString = (new Date ((new Date((new Date(new Date())).toISOString() )).getTime() - ((new Date()).getTimezoneOffset()*60000))).toISOString().slice(0, 19).replace('T', ' '); console.log( formatedMysqlString ); 

This solution also works for Node.js when using Timestamp in mysql.

@Gajus Kuizinas first answer seems to change Mozilla for the ISOString prototype

Answer 5

JS time value for MySQL

 var datetime = new Date().toLocaleString(); 

OR

 const DATE_FORMATER = require( 'dateformat' ); var datetime = DATE_FORMATER( new Date(), "yyyy-mm-dd HH:MM:ss" ); 

OR

 const MOMENT= require( 'moment' ); let datetime = MOMENT().format( 'YYYY-MM-DD HH:mm:ss.000' ); 

you can send this in the parameters, this will work.

Answer 6

There is a formatting program in the venerable DateJS library (it overrides ".toString ()"). You can also do this very easily, because the Date methods provide you with all the numbers you need.

Answer 7

Full workaround (to support time zone) using @Gajus answer concept:

 var d = new Date(), finalDate = d.toISOString().split('T')[0]+' '+d.toTimeString().split(' ')[0]; console.log(finalDate); //2018-09-28 16:19:34 --example output 

Answer 8

I gave simple examples of the date format in JavaScript, please check the code below

 var data = new Date($.now()); // without jquery remove this $.now() console.log(data)// Thu Jun 23 2016 15:48:24 GMT+0530 (IST) var d = new Date, dformat = [d.getFullYear() ,d.getMonth()+1, d.getDate() ].join('-')+' '+ [d.getHours(), d.getMinutes(), d.getSeconds()].join(':'); console.log(dformat) //2016-6-23 15:54:16 

Using momentjs

 var date = moment().format('YYYY-MM-DD H:mm:ss'); console.log(date) // 2016-06-23 15:59:08 

Example: https://jsfiddle.net/sjy3vjwm/2/

Answer 9

The easiest and most correct way to convert JS Date to SQL datetime format that comes to my mind is this one. It handles the time zone offset correctly.

 const toSqlDatetime = (inputDate) => { const date = new Date(inputDate) const dateWithOffest = new Date(date.getTime() - (date.getTimezoneOffset() * 60000)) return dateWithOffest .toISOString() .slice(0, 19) .replace('T', ' ') } toSqlDatetime(new Date()) // 2019-08-07 11:58:57 toSqlDatetime(new Date('2016-6-23 1:54:16')) // 2016-06-23 01:54:16 

Beware that @ Paulo Roberto's answer will give incorrect results when moving to a new day (I can not leave comments). For example :

 var d = new Date('2016-6-23 1:54:16'), finalDate = d.toISOString().split('T')[0]+' '+d.toTimeString().split(' ')[0]; console.log(finalDate); // 2016-06-22 01:54:16 

We have June 22 instead of 23!

Answer 10

 var _t = new Date(); 

if you want UTC format just

 _t.toLocaleString('indian', { timeZone: 'UTC' }).replace(/(\w+)\/(\w+)\/(\w+), (\w+)/, '$3-$2-$1 $4'); 

or

 _t.toISOString().slice(0, 19).replace('T', ' '); 

and if you want in a specific time zone, then

 _t.toLocaleString('indian', { timeZone: 'asia/kolkata' }).replace(/(\w+)\/(\w+)\/(\w+), (\w+)/, '$3-$2-$1 $4');