Node.js Updating MySql timestamp

Question

So i do a simple query like so:

connection.query("UPDATE workers SET timestamp='"+thedate+"' WHERE id = " + id,function(err,upres){
    connection.release();
    if(!err) {
      console.log('updated record')
      console.log(upres);
    }

})

console.log reveals the data format as: 2015-04-02 19:29:14

And if i debug the SQL statement, that turns out to be:

UPDATE workers SET timestamp='2015-04-02 21:31:16' WHERE id = 3;

However, when i list the data, the output is:

[{"id":3,"worker":"John Doe","timestamp":"2015-04-01T22:00:00.000Z","duration":30}]

This is way off compared to the time that is being reported?

What is causing this?

Answer 1

You do not know how MySQL is turning your VARCHAR into a date. There are a lot of configuration options. It would be better to use the STR_TO_DATE function to circumvent all of the assumptions. Here is a link to the docs for STR_TO_DATE().

As a side note, I would strongly recommend using prepared statements as a way to safeguard your application against errors and sql injection.

EDITS:

In regards to your questions, the column could be DATETIME, but your value you are assigning is a VARCHAR

'UPDATE workers SET timestamp = ? WHERE id = ?', ['4/2/2015 3:00:00 PM', 3'], [callBackFunction]

Based on what you said about the conversion not working, I am suspicious about the data type for the timestamp column.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE NAME = 'workers'

A statement like that should give you all of the information about that column. You could also find this in a GUI, if you have access. There are three different date types in MySQL date, datetime, or timestamp. This is most likely a DATE column, that will not be able to hold the time.