Save DateTime mysql with nodejs

Question

Save DateTime mysql with nodejs

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

javascript
mysql
node.js

Answer 1

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.

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

Answer 2

The date needs to be enclosed in " or ' so mysql knows it is dealing with a string and can convert it into a datetime.

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'

Answer 3

With the help of @tadman, it's works

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) {

Answer 4

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,