felixge/node-mysql — Issue #166: DATETIME and JavaScript Date object conversion
Author: yoorek
Created: 2012-02-06 11:09:09+00:00
State: open

I'm fighting with DATETIME field problem for same time now. Using JavaScript Date type when inserting to table and reading it i ancounter conversion form/to local and UTC time and unpredictable results. I am no expert in node.js, mysql or javascript so maybe I am missing something (but I'm sure I need to use DATETIME type - I can not use INT and Unix time although it would solve my problem and many more)

I managed to narrow the problem and modify sample code from node-mysql documentation to illustrate it.
The task is simple - I insert rows, I read first one and use it's created field value to read it again from database - I should read it yes? Here is a modified sample:

var mysql = require('mysql');
var TEST_DATABASE = 'nodejs_mysql_test';
var TEST_TABLE = 'test';
var client = mysql.createClient({
user:'root',
password:'root'
});

client.query('CREATE DATABASE ' + TEST_DATABASE, function (err) {
if (err && err.number != mysql.ERROR_DB_CREATE_EXISTS) {
throw err;
}
});

// If no callback is provided, any errors will be emitted as `'error'`
// events by the client
client.query('USE ' + TEST_DATABASE);

client.query(
'CREATE TABLE IF NOT EXISTS ' + TEST_TABLE +
'(id INT(11) AUTO_INCREMENT, ' +
'title VARCHAR(255), ' +
'text TEXT, ' +
'created DATETIME, ' +
'PRIMARY KEY (id))'
);

client.query(
'INSERT INTO ' + TEST_TABLE + ' ' +
'SET title = ?, text = ?, created = ?',
['super cool', 'this is a nice text', '2010-08-16 10:00:23']
);

var query = client.query(
'INSERT INTO ' + TEST_TABLE + ' ' +
'SET title = ?, text = ?, created = ?',
['another entry', 'because 2 entries make a better test', '2010-08-16 12:42:15']
);

// ***** HERE STARTS MODIFIED PART

// Get all rows
client.query('SELECT * FROM ' + TEST_TABLE,
function selectCb(err, results) {
if (err) {
throw err;
}

// read first row's created datetime
var date = results[0].created;
console.log("date:", date);

// use created datetime to query rows
client.query('SELECT * FROM ' + TEST_TABLE + ' WHERE created >= ?', [date],
function selectCb(err, results) {
if (err) {
throw err;
}
console.log(results); //// NO RESULTS
client.end();
}
);
}
);