Does anyone know how to convert JS dateTime to MySQL datetime? Also is there a way to add a specific number of minutes to JS datetime and then pass it to MySQL datetime?
This question is related to
javascript
mysql
The venerable DateJS library has a formatting routine (it overrides ".toString()"). You could also do one yourself pretty easily because the "Date" methods give you all the numbers you need.
For 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's first answer seems to modify mozilla's toISOString prototype
I think the solution can be less clunky by using method toISOString()
, it has a wide browser compatibility.
So your expression will be a one-liner:
new Date().toISOString().slice(0, 19).replace('T', ' ');
The generated output:
"2017-06-29 17:54:04"
I have given simple JavaScript date format examples please check the bellow code
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 please check https://jsfiddle.net/sjy3vjwm/2/
A simple solution is send a timestamp to MySQL and let it do the conversion. Javascript uses timestamps in milliseconds whereas MySQL expects them to be in seconds - so a division by 1000 is needed:
// Current date / time as a timestamp:
let jsTimestamp = Date.now();
// **OR** a specific date / time as a timestamp:
jsTimestamp = new Date("2020-11-17 16:34:59").getTime();
// Adding 30 minutes (to answer the second part of the question):
jsTimestamp += 30 * 1000;
// Example query converting Javascript timestamp into a MySQL date
let sql = 'SELECT FROM_UNIXTIME(' + jsTimestamp + ' / 1000) AS mysql_date_time';
The easiest correct way to convert JS Date to SQL datetime format that occur to me is this one. It correctly handles timezone offset.
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 answer will produce incorrect results at the turn on new day (i can't 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've got 22 June instead of 23!
Using toJSON()
date function as below:
var sqlDatetime = new Date(new Date().getTime() - new Date().getTimezoneOffset() * 60 * 1000).toJSON().slice(0, 19).replace('T', ' ');
console.log(sqlDatetime);
_x000D_
I'm using this long time and it's very helpful for me, use as you like
Date.prototype.date=function() {
return this.getFullYear()+'-'+String(this.getMonth()+1).padStart(2, '0')+'-'+String(this.getDate()).padStart(2, '0')
}
Date.prototype.time=function() {
return String(this.getHours()).padStart(2, '0')+':'+String(this.getMinutes()).padStart(2, '0')+':'+String(this.getSeconds()).padStart(2, '0')
}
Date.prototype.dateTime=function() {
return this.getFullYear()+'-'+String(this.getMonth()+1).padStart(2, '0')+'-'+String(this.getDate()).padStart(2, '0')+' '+String(this.getHours()).padStart(2, '0')+':'+String(this.getMinutes()).padStart(2, '0')+':'+String(this.getSeconds()).padStart(2, '0')
}
Date.prototype.addTime=function(time) {
var time=time.split(":")
var rd=new Date(this.setHours(this.getHours()+parseInt(time[0])))
rd=new Date(rd.setMinutes(rd.getMinutes()+parseInt(time[1])))
return new Date(rd.setSeconds(rd.getSeconds()+parseInt(time[2])))
}
Date.prototype.addDate=function(time) {
var time=time.split("-")
var rd=new Date(this.setFullYear(this.getFullYear()+parseInt(time[0])))
rd=new Date(rd.setMonth(rd.getMonth()+parseInt(time[1])))
return new Date(rd.setDate(rd.getDate()+parseInt(time[2])))
}
Date.prototype.subDate=function(time) {
var time=time.split("-")
var rd=new Date(this.setFullYear(this.getFullYear()-parseInt(time[0])))
rd=new Date(rd.setMonth(rd.getMonth()-parseInt(time[1])))
return new Date(rd.setDate(rd.getDate()-parseInt(time[2])))
}
and then just:
new Date().date()
which returns current date in 'MySQL format'
for add time is
new Date().addTime('0:30:0')
which will add 30 minutes.... and so on
var _t = new Date();
if you want UTC format simply
_t.toLocaleString('indian', { timeZone: 'UTC' }).replace(/(\w+)\/(\w+)\/(\w+), (\w+)/, '$3-$2-$1 $4');
or
_t.toISOString().slice(0, 19).replace('T', ' ');
and if want in specific timezone then
_t.toLocaleString('indian', { timeZone: 'asia/kolkata' }).replace(/(\w+)\/(\w+)\/(\w+), (\w+)/, '$3-$2-$1 $4');
new Date().toISOString().slice(0, 10)+" "+new Date().toLocaleTimeString('en-GB');
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 params its will work.
Full workaround (to mantain the timezone) 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
Simple: just Replace the T. Format that I have from my <input class="form-control" type="datetime-local" is : "2021-02-10T18:18"
So just replace the T, and it would look like this: "2021-02-10 18:18" SQL will eat that.
Here is my function:
var CreatedTime = document.getElementById("example-datetime-local-input").value;
var newTime = CreatedTime.replace("T", " ");
https://www.tutorialrepublic.com/codelab.php?topic=faq&file=javascript-replace-character-in-a-string
Solution built on the basis of other answers, while maintaining the timezone and leading zeros:
var d = new Date;
var date = [
d.getFullYear(),
('00' + d.getMonth() + 1).slice(-2),
('00' + d.getDate() + 1).slice(-2)
].join('-');
var time = [
('00' + d.getHours()).slice(-2),
('00' + d.getMinutes()).slice(-2),
('00' + d.getSeconds()).slice(-2)
].join(':');
var dateTime = date + ' ' + time;
console.log(dateTime) // 2021-01-41 13:06:01
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 controlling the timezone, 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')
Source: Stackoverflow.com