Save DateTime mysql with nodejs

Question

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

Do not forget to call connection.escape on all of your values unless you're using parameterized queries. — tadman
– tadman, Commented Aug 2, 2013 at 17:33

Barno · Accepted Answer · 2013-08-02 18:16:12Z

17

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

edited Aug 2, 2013 at 18:16

answered Aug 2, 2013 at 17:57

Barno

3,3415 gold badges31 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

anshabhi Over a year ago

is connection.escape() necessary with Date() object?

thenetimp Over a year ago

I agree with @anshabhi you don't need to escape objects that aren't passed in from user input. the escape is unnecessary here for the date that was created a line above.

Mechlar · Accepted Answer · 2019-12-03 23:04:20Z

16

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

edited Dec 3, 2019 at 23:04

Mechlar

5,02414 gold badges61 silver badges83 bronze badges

answered Aug 2, 2013 at 18:01

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

1 Comment

tsukimi Over a year ago

I prefer this way instead of messing around with JSON dates

miah · Accepted Answer · 2013-08-02 17:48:30Z

5

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'

answered Aug 2, 2013 at 17:48

miah

10.5k3 gold badges23 silver badges34 bronze badges

Comments

Shift 'n Tab · Accepted Answer · 2017-11-08 00:50:01Z

0

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,

edited Nov 8, 2017 at 0:50

Shift 'n Tab

9,51414 gold badges80 silver badges125 bronze badges

answered Jul 22, 2015 at 15:35

phatneglo

343 bronze badges

Collectives™ on Stack Overflow

Save DateTime mysql with nodejs

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related