createConnection timezone not working

[ktechadmin]

Hi guys,

in below code timezone setting does not seem to have any effort , i think its the issue with the driver could anyone help ?

my sql DB timezone : "-08:00"
application time : "+05:30"

yes this needs to be handled at app level. DB cant change.

var mysql = require('mysql');

//connect to db
var dbCon = mysql.createConnection({
host: "xxxx",
user: "xxxxx",
password: "xxxxx",
database:"xxxx",
timezone:"+05:30"
});

//db connection
dbCon.connect(function(err) {
if (err) throw err;
console.log("DB Connected!");
});

var dbCon = db.dbCon;
//query functions
function get_total_sales(startDate, endDate, callback) {
dbCon.query("SELECT sum(Paid) as total_sales, count(Paid) as item_soled FROM orders WHERE DateCreated >= " + mysql.escape(startDate) + " AND DateCreated <= " + mysql.escape(endDate) + " ",
function (err, result, fields) {
if (err) {
throw err;
} else {

            finalWriteDataObject["total_sales"] = numeral(result[0].total_sales).format();
            finalWriteDataObject["item_soled"] = numeral(result[0].total_saitem_soledles).format();
            callback();
        }

    });

}

get_total_sales(startDate, endDate, writeToFile);

[swimmadude66]

Chiming in to add that timezone definitely has no effect. In node-mysql, the data-parsing section for date objects handles timezones here: https://github.com/mysqljs/mysql/blob/ad014c82b2cbaf47acae1cc39e5533d3cb6eb882/lib/protocol/packets/RowDataPacket.js#L78

However in node-mysql2 the same functionality is accomplished here: https://github.com/sidorares/node-mysql2/blob/master/lib/packets/packet.js#L252

the latter does not read or check timezone from the connection config at all, and instead just formats from a parsed string to a Date object, which will default to local time. I have used a workaround for this issue by adding dateStrings: true, to my db connection config, which returns the date fields as they are shown in mysql workbench. From there, I cast to a UTC date like so:

        const dateString = row.CreateDate as string;
        const utcDate= new Date(dateString.replace(/\s/, 'T')+'Z');

Personally, I am going to switch back to using node-mysql at least until this is fixed, but I hope that workaround can help other people.

[rob5408]

This is addressed in the second part here: https://github.com/sidorares/node-mysql2/tree/master/documentation#known-incompatibilities-with-node-mysql and worked great for me. Add their typeCast function to the config object passed to createConnection.

[ithubg]

The workaround using typeCast doesn't work with prepared statement because it uses binary_parser.