使用 nodejs 保存 DateTime mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18022523/
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
Save DateTime mysql with nodejs
提问by Barno
I want save in my Datetime field the Date For insert i haven't problem... I have tried many attempts, this is the last but the result is the same
我想在我的日期时间字段中保存日期插入我没有问题...我尝试了很多次尝试，这是最后一次但结果是一样的
Insert:
插入：
var now = new Date();
var jsonDate = now.toJSON();
var then = new Date(jsonDate);
var o_voto_foto = {
profilo_id: profilo,
foto_id: data.id_foto_votata,
punteggio: data.voto,
proprietario_id: data.proprietario_foto,
created: then
};
connection.query('INSERT INTO prof_voto_foto SET ?', o_voto_foto, function(error, rows) {
if (error) {
var err = "Error on INSERT 'votoFotoFancybox': " + error;
console.error(err);
throw err;
}
Update table:
更新表：
var now = new Date();
var jsonDate = now.toJSON();
var then = new Date(jsonDate);
connection.query('UPDATE prof_voto_foto SET punteggio = ' + data.voto + ', created = ' + then +' WHERE profilo_id = ' + profilo + ' AND foto_id = ' + data.id_foto_votata, function(error, rows) {
if (error) {
var err = "Error on UPDATE 'votoFotoFancybox': " + error;
console.error(err);
throw err;
}
And i recive this error:
我收到这个错误：
Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Aug 02 2013 19:03:13 GMT+0200 (CEST) WHERE profilo_id = 103 AND foto_id = 5' at line 1
回答by Barno
With the help of @tadman, it's works
在@tadman 的帮助下，它起作用了
created = new Date();
connection.query('UPDATE prof_voto_foto SET punteggio = ' + connection.escape(data.voto) + ', created = ' + connection.escape(created) + ' WHERE profilo_id = ' + connection.escape(profilo) + ' AND foto_id = ' + connection.escape(data.id_foto_votata), function(error, rows) {
回答by Mike Brant
Since you are working with current timestamps, you may just consider using the NOW()MySQL function instead of populating date from javascript. Of course, this means you will standardize all timestamps on MySQL server time rather than client's time which may or may not be desirable.
由于您使用的是当前时间戳，因此您可以考虑使用NOW()MySQL 函数而不是从 javascript 填充日期。当然，这意味着您将标准化 MySQL 服务器时间上的所有时间戳，而不是客户端时间，这可能是可取的，也可能不是。
Usage would be like this:
用法如下：
'UPDATE prof_voto_foto SET punteggio = ' + data.voto + ', created = NOW() WHERE profilo_id = ' + profilo + ' AND foto_id = ' + data.id_foto_votata
回答by miah
The date needs to be enclosed in "or 'so mysql knows it is dealing with a string and can convert it into a datetime.
日期需要被封闭在"或'因此MySQL知道它在处理一个字符串，并可以将其转换成一个日期时间。
UPDATE prof_voto_foto SET punteggio = ' + data.voto + ', created = "' + then +'" WHERE profilo_id = ' + profilo + ' AND foto_id = ' + data.id_foto_votata
And it may need to be formatted to match what mysql is expecting, Aug 02 2013 19:03:13 GMT+0200 (CEST)needs to converted to 'YYYY-MM-DD HH:MM:SS'
它可能需要格式化以匹配 mysql 期望的内容，Aug 02 2013 19:03:13 GMT+0200 (CEST)需要转换为'YYYY-MM-DD HH:MM:SS'
回答by phatneglo
var o_voto_foto = {
profilo_id: profilo,
foto_id: data.id_foto_votata,
punteggio: data.voto,
proprietario_id: data.proprietario_foto,
// just delete the stuff you add here :) then add created = now() on the sql statement. that should do the trick
};
connection.query('INSERT INTO prof_voto_foto SET **created = now(),** ?', o_voto_foto, function(error, rows) {
if (error) {
var err = "Error on INSERT 'votoFotoFancybox': " + error;
console.error(err);
throw err;
}
do the same on the update,
对更新做同样的事情，
