MySQL node sql query time issue.

[Mark Setrem]

I have a flow that is scraping hire bike bike availability data and passing the number of bikes available into a table on a MariaDB instance using the mysql node.

The data input is working smoothly, as is the the data export apart from one issue...  Somewhere along the line in the an hour is being subtracted from the time.

The database is setup to use UTC as default, and the box that node-red is running on is in BST.

The data is being input with:

"INSERT INTO `hirebike`.`totalbikes` (`Time`, `Bikes` ,`Places` ) VALUES (UTC_TIMESTAMP(), '"+totalbike_variables here...

and this is working.  

Using a basic query : SELECT * FROM `hirebike`.`total bikes` 

I can query the data in the database using phpmyadmin and the data is correctly displayed in UTC.

ID    |      Time                     |BIkes| Places

211  | 2014-06-30 16:54:27|148    |317

I can query it in php and I get the following also in UTC:

211 2014-06-30 16:54:27 148 317

However querying it with the node-red mysql node I get:

 {
    "UID": 211,
    "Time": "2014-06-30T15:54:27.000Z",
    "Bikes": 148,
    "Places": 317,
  }

Any ideas? ideally I just want to get the data as stored back!

[Lawrence Griffiths]

I think the node doesn't take account on Day light saving 

I've noticed that the NR debug window time stamp is an hour out on BST.

If your PHP has date_default_timezone_set('Europe/London') set it might explain why it's interpreted correctly.

But others with deeper knowledge might explain why but with my imitated one I use this..

I use this util

// local time

function lt(){

var  lt = new Date();

var d = new Date(lt.getTime()-(lt.getTimezoneOffset()*60000));

return d;

}

var d = lt(); // correct local time

Lawrence

[Antoine Aflalo]

Well after checking the lib used for the Mysql node : https://github.com/felixge/node-mysql

There is a TimeZone option that can be set when creating the connection.

By default it use the "local" time. I don't know exactly what is the local time on Node-RED.

It shouldn't be difficult to provide an optional field in the node to set that timezone.

--
http://nodered.org
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Antoine Aflalo

[Dave C-J]

Good spot, can you please raise it as a Issue on github so we can track it ? thanks...

And likewise the debug window one... 

[Dave C-J]

Hi

have pushed a fix to the node-red-nodes repo

(and also added it to npm - so npm install node-red-node-mysql should work also... but may end up with a duplicate node if the other isn't removed first... etc etc

- early days with npm nodes so feedback welcome :-)