Mysql: 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);

[dougwilson]

Maybe can you elaborate what you're expecting the timezone setting to do? What it does it just fix conversation, not change the timezone. You must set it to the timezone your mysql server is set to.

[dougwilson]

From the docs https://github.com/mysqljs/mysql/blob/master/Readme.md#connection-options

timezone: The timezone configured on the MySQL server. This is used to type cast server date/time values to JavaScript Date object and vice versa. This can be 'local', 'Z', or an offset in the form +HH:MM or -HH:MM. (Default: 'local')

From your description, you need to do timezone: '-08:00' since that is the timezone of your mysql server. Setting it to something different will just mess up all the dates as they are translated to and from javascript Date objects.

I hope this helps.

[ktechadmin]

Hi Dougwilson,

thanks for reply, im coming from php and in there in db config when we give the timezone parameter it passes that info into the DB so DB returns correct dataset.

leaving that just take mysql it supports below
"SET time_zone='+5:30';"

example of running with above and not without above.
SELECT NOW(); return 2018-11-24 15:28:24
but SET time_zone='+5:30';SELECT NOW() returns 2018-11-25 04:59:10.

basically when the correct timezone information is not passed to DB , select queries based on say mysql data type timestamp will give you wrong results.

SELECT sum(paid) as Totsales FROM orders_tbl WHERE paid=1 and date(DateCreated) = '2018-11-23' returns 65.

SET time_zone='+5:30';SELECT sum(paid) as Totsales FROM orders_tbl WHERE paid=1 and date(DateCreated) = '2018-11-23' returns 71

if your table uses timestamp type than your screwed, so the driver must pass the timezone data to mysql server

[dougwilson]

Gotcha. If you want to set the timezone on the server, then you don't need to pass in the timezone parameter at all. Just run that SET command as the first query on your connection.

[ktechadmin]

Gotcha. If you want to set the timezone on the server, then you don't need to pass in the timezone parameter at all. Just run that SET command as the first query on your connection.

actually thats what we are doing in php pdo .

but when we do the same in node.js its showing error

[dougwilson]

What is the error you're getting in node.js?

[ktechadmin]

What is the error you're getting in node.js?

   dbCon.query("SET time_zone='+5:30';select now()",
    function (err, result, fields) {
        if (err) {
            throw err;
        } else {
            console.log(result);
            // res.json(result);
        }

    });

for above function

        throw err; // Rethrow non-MySQL errors
        ^

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 'select now()' at line 1

in php pdo we fun as 2 queries in same connection , here dont know how to

[dougwilson]

You have to enable that feature. See the docs here: https://github.com/mysqljs/mysql#multiple-statement-queries

[ktechadmin]

thanks a lot above method can solve the issue but i dont understand the purpose of below if its not meant to pass to mysql

From the docs https://github.com/mysqljs/mysql/blob/master/Readme.md#connection-options

timezone: The timezone configured on the MySQL server. This is used to type cast server date/time values to JavaScript Date object and vice versa. This can be 'local', 'Z', or an offset in the form +HH:MM or -HH:MM. (Default: 'local')

[dougwilson]

The setting is used to type cast server date/time values to JavaScript Date object and vice versa. It is to be set to the timezone configured on the MySQL server. In your case, this would be the value '-08:00', but if you run the query SET time_zone='+5:30', you instead need to set that to '+5:30' to match. But since it is just used to type cast server date/time values to JavaScript Date objects, if you're not actually using Date objects, the setting would not be meaningful to your code.