Javascript 将 JS 日期时间转换为 MySQL 日期时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5129624/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert JS date time to MySQL datetime
提问by locoboy
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?
有谁知道如何将 JS 日期时间转换为 MySQL 日期时间？还有一种方法可以将特定的分钟数添加到 JS 日期时间，然后将其传递给 MySQL 日期时间吗？
采纳答案by kojiro
While JS does possess enough basic tools to do this, it's pretty clunky.
虽然 JS 确实拥有足够的基本工具来做到这一点，但它非常笨重。
/**
* 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());
};
回答by Gajus
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/:
对于更高级的用例，包括控制时区，请考虑使用http://momentjs.com/：
require('moment')().format('YYYY-MM-DD HH:mm:ss');
For a lightweight alternative to momentjs, consider https://github.com/taylorhakes/fecha
对于momentjs的轻量级替代品，请考虑https://github.com/taylorhakes/fecha
require('fecha').format('YYYY-MM-DD HH:mm:ss')
回答by Farside
I think the solution can be less clunky by using method toISOString(), it has a wide browser compatibility.
我认为使用 method 解决方案可以不那么笨拙toISOString()，它具有广泛的浏览器兼容性。
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"
“2017-06-29 17:54:04”
回答by M. Hamza Rajput
JS time value for MySQL
MySQL 的 JS 时间值
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.
你可以在 params 中发送它，它会起作用。
回答by Paul Totzke
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.
在 mysql 中使用时间戳时，此解决方案也适用于 Node.js。
@Gajus Kuizinas's first answer seems to modify mozilla's toISOString prototype
回答by Pointy
回答by Paulo Roberto Rosa
Full workaround (to mantain the timezone) using @Gajus answer concept:
使用@Gajus 回答概念的完整解决方法（以保持时区）：
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
回答by Nirbhay Kumar
new Date().toISOString().slice(0, 10)+" "+new Date().toLocaleTimeString('en-GB');
new Date().toISOString().slice(0, 10)+" "+new Date().toLocaleTimeString('en-GB');
100% working
100% 工作
回答by Thomas
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'
以“MySQL 格式”返回当前日期
for add time is
添加时间是
new Date().addTime('0:30:0')
which will add 30 minutes.... and so on
这将增加 30 分钟......等等
回答by Vlad Harbarchuk
The easiest correct way to convert JS Date to SQL datetime format that occur to me is this one. It correctly handles timezone offset.
我遇到的将 JS 日期转换为 SQL 日期时间格式的最简单的正确方法是这个。它正确处理时区偏移。
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 answerwill produce incorrect results at the turn on new day (i can't leave comments). For example:
请注意@Paulo Roberto 的回答会在新的一天开始时产生错误的结果（我无法发表评论）。例如：
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!
我们有 6 月 22 日而不是 23 日！
