How to add timestamp to message payload ?

[Gaz]

Hi,

New to node-red, I am trying to add a timestamp to the message payload then output this to a twitter account.I have searched online for past hour and cannot find a solution.
Any help would be greatly appreciated

regards

Gaz

[Nicholas O'Leary]

Hi Gaz,

The easiest way to add a timestamp to a payload is to use a Function node with something like the following:

msg.payload = new Date().toString();
return msg;

That sets the payload to a human readable date. You'll have to have a read of the various functions available on the Date object within JavaScript if you want a more customised format.

Wire that function node to a twitter output node you've connected to your twitter account and it should just work.

Nick

--
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.

[Nicholas Humfrey]

You can use the Inject node to put a timestamp into a message payload.

Sent from my phone

--

[Gaz]

Hi Nicholas,

Thanks for that, but I did try that and although it does add the timestamp, I then lose the original message.

Any pointers to how I can keep both ?

I do realise I need to learn more javascript, but at the moment I am not sure what I am looking for ?

Regards

Gary

[Gaz]

Hi quick update.

Would this be the correct way of doing it ? obviously would need to change a few things but it looks like it would work ?
http://www.w3schools.com/jsref/jsref_concat_string.asp

Regards

Gary

[Dave C-J]

H

i

use + to concatenate strings

msg.payload = msg.payload + " - " 

+ 

new Date().toString();

regards
Dave Conway-Jones

[Gaz]

Hi thanks to you all for your help.

Dave that worked !

Regards

Gary

[Hemal Chevli]

How do I do the same thing with unix timestamp instead of human readable time?
I want to store this in mysql database which has a table with a column property of time-stamp?
Thanks

[Hemal Chevli]

I'm getting data from serial port, and want to add time stamps to it and store in db. new data from serial begins on new line.

[Dave C-J]

Date.now()  gives the unix timestamp

    https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/now

but... - most of our database nodes will take the payload object and put different properties into different columns (to make querying easier)  - so you may want rearrange the payload first rather than just adding to the string.

maybe a function like

     msg.payload = {value:msg.payload, timestamp:Date.now()};

This takes the "old" payload (string from serial port) - assigns it to a property called value, and adds a second property called timestamp with the current time in it.

When saved in the database that should (or could) then create two columns - one called value and one called timestamp.

[Lawrence Griffiths]

javascript is milliseconds and Unix is seconds from Epoch.

You can test your time stamps at http://www.epochconverter.com/

[Dave C-J]

Lawrence,  good catch !

     should be   Date.now()/1000 ;  

Thanks

[chris mobberley]

FYI There is also a timestamp node:

https://www.npmjs.org/package/thethingbox-node-timestamp 

[Dave C-J]

I also just found this how-to...

http://www.ibm.com/developerworks/cloud/library/cl-arduino-iot3-app/index.html

that includes both appending a timestamp - and also saving in a mongodb....

[Greg EVA]

I have been working with timestamps in Node-RED and just figured I'd share some of my findings.

First of all, I added a few values to the msg object so that I have the various types of timestamps when I need them.  Seconds are easy to work with, but won't cut it for MySql, and are not ISO for IBM and their IoT reference model.

var now = new Date();
msg.payload = now.valueOf();     // contains the millisecond value since epoch needing to be divided by 1000 to get to Unix epoch time
msg.timestamp_ISO = now.toISOString();
msg.timestamp_mysql = msg.timestamp_ISO.slice(0, 19).replace('T', ' ');

Cheers,

Greg

[Martin Jarvis]

I'd like see a standard timestamp property(s) added to the msg object. I think it will encourage consistency in how time stamps are handled across built-in nodes and 3rd party nodes. I'm not sure if 1,2 or 3 timestamps are appropriate; one set by the original source of the message (e.g external sensor), one for time that message first started processing in the current flow, and one for time msg arrived at current node.

[Julian Knight]

I take it that you've seen the timestamp contributed node? I often use it as a dummy node when joining many inputs to a single node.

[Phil Grant]

Hi,

I've added the timestamp node and I can inject the time but I've noticed that no matter what I do the time is always an hour behind, I've tried setting my RPi to different timezones with with no affect, what am I missing? and how can I correct it?

Cheers

[Nicholas O'Leary]

Hi Phil,

looking at the node's documentation (http://flows.nodered.org/node/thethingbox-node-timestamp) it does say the timestamp is in UTC - so will be agnostic of your Pi's local timezone.

One choice will be to wait until the autumn, then we'll no longer be in BST and the time will be correct... ;)

More seriously, I think you'll have to account for the fact its in UTC wherever you're handling the date.

Nick

--

[Julian Knight]

Urm, this is a general problem. All of my times come out in UTC on the Pi.

[Mark Setrem]

Julian, What do you mean by "All of my times come out in UTC" as I don't appear to have that problem

For example:

var currentdate = new Date();
var currenthour = currentdate.getHours();

in a Node-Red function on my Pi is definitely reflecting the timezone I've set in raspi-config.

[Greg EVA]

@Phil, as Nick suggested, just wait till the time is correct. I have the same problem, but as I'm in CET and not GMT... I don't think it'll ever be correct. ;-)

Joking aside... it is considered to be a good idea to use UTC timestamps as you know exactly what the time was at that moment regardless of how the machine is configured, where it is located in the world, what the users local time is, the servers local time, etc.

That said, you probably want to do stuff with the time, and perhaps be able to show the local time somewhere; even if you'll retain UTC in your processing and storage.  For this reason... I made my own Timestamp function node which adds a number of different forms of timestamps to the message.  I think I pasted the code above... but the idea is that the UTC ISO string may be standard, but I don't know of an easy way to work with it, yet there are all sorts of JavaScript methods which can easily work with a Date object.... for example turning a Date object into a local time string.  Check this reference out.

http://www.w3schools.com/jsref/jsref_obj_date.asp

This is why I used the JavaScript date format, as it is easy to convert, work with, find the difference between values, etc.  Then I convert that Date object into a specific string which suits my needs (like a MySql DATETIME).

Greg

                                                                           

Greg Eva

ge...@ge-volution.eu

www.gE-volution.eu

France : +33 4 81 68 09 64

Canada : +1 226 476 1192

iNum : +833 51 00 09 90 28 81

--
http://nodered.org
---
You received this message because you are subscribed to a topic in the Google Groups "Node-RED" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/node-red/ff5chUeXhJI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to node-red+u...@googlegroups.com.

[Julian Knight]

What I mean is that all time related output always comes out as UTC not as local time. It is confusing as I have to remember to mentally convert any time related settings (scheduler) to GMT.

[Phil Grant]

Thanks Greg,

I'll definitely look into the info because having a UTC timestamp sent to the end user of the app will be unacceptable, I can't expect users to remember to add an hour to the time even though it's likely that only I will use the app but you never know.

Cheers

[Julian Knight]

It is not unreasonable to keep all system times as UTC so that you don't get into problems when moving timezones or switching into/out of daylight saving time. Really, best practice says that you work in UTC and always convert at the UI.

Of course, there are always grey areas like the afore mentioned scheduling. Is that UI or system? A bit of both really. I can put up with it but there ought to be a choice.

[Phil Grant]

Julian,

Fair point but at the moment there is no UI as such just a received pushbullet note, I suppose I could rely on the timestamp from pushbullet but that doesn't indicate the actual age of the temp data it could be hours old.

I need to investigate a function node and do it in Javascript I suppose.

[Julian Knight]

Not disagreeing at all Phil. It is annoying. Perhaps a localtime node is needed?

[Martin Jarvis]

Working with dates and times is made much easier by moment.js and moment-timezone.js.

Regards

Martin

m: 07776 490601

[Phil Grant]

Thanks Gary,

Slowly getting there but as usual there is something that doesn't work as expected, I mean why would anyone write a function like getMonth() that returned a value of 0 to 11, that's just bloody stupid!!!!!

Unless someone is now going to make me look a prat by giving a perfectly good explanation as to why .......well :-)

Phil

On Tuesday, 18 November 2014 08:24:57 UTC, Gaz wrote:

Hi quick update.

Would this be the correct way of doing it ? obviously would need to change a few things but it looks like it would work ?
http://www.w3schools.com/jsref/jsref_concat_string.asp

Regards

Gary



On Tuesday, 18 November 2014 07:56:05 UTC, Gaz wrote:

Hi Nicholas,

Thanks for that, but I did try that and although it does add the timestamp, I then lose the original message.

Any pointers to how I can keep both ?

I do realise I need to learn more javascript, but at the moment I am not sure what I am looking for ?

Regards

Gary



On Monday, 17 November 2014 23:37:48 UTC, Nicholas O'Leary wrote:

Hi Gaz,

The easiest way to add a timestamp to a payload is to use a Function node with something like the following:

msg.payload = new Date().toString();
return msg;

That sets the payload to a human readable date. You'll have to have a read of the various functions available on the Date object within JavaScript if you want a more customised format.

Wire that function node to a twitter output node you've connected to your twitter account and it should just work.

Nick

On Mon, 17 Nov 2014 23:25 Gaz <online.a...@googlemail.com> wrote:

Hi,

New to node-red, I am trying to add a timestamp to the message payload then output this to a twitter account.I have searched online for past hour and cannot find a solution.
Any help would be greatly appreciated

regards

Gaz

--
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.

[Phil Grant]

Thanks Dave,

That worked for me too.

Phil

[Phil Grant]

Thanks for the help.

Finally got it working as I wanted, might see about getting rid of the GMT part though.

Also sent as a Pushbullet note to my phone, PC and tablet

[Greg EVA]

This certainly seems like a convoluded approach, but has been this way forever (in most languages). It is due to how arrays work, the first element being 0. Strings are the same thing, the first character is in position 0. Seems pretty dumb, but it probably comes from pointer offsets... Start of variable plus X... Where x would be zero for the first spot, then 1, etc.

Anyhow, its not a huge deal, just add 1 to it when you use it.  You can do .getMonth()+1 for example.

[Phil Grant]

That's more like it 

[Mike Arney]

How do you convert the timezone in the UI?

Thanks,

Mike

[Colin Law]

Do you mean that you have a time which is in UTC and you want to
display it in a particular timezone?

Colin

> --
> http://nodered.org
>
> Join us on Slack to continue the conversation: http://nodered.org/slack
> ---
> 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.
> To post to this group, send email to node...@googlegroups.com.
> Visit this group at https://groups.google.com/group/node-red.
> To view this discussion on the web, visit
> https://groups.google.com/d/msgid/node-red/504b067d-2277-473b-909a-b772b3cb36f0%40googlegroups.com.

[Mike Arney]

I tried to insert my time in UTC, but I get the following error:

I have only been successful inserting to SQL with this code:

var d = new Date();

    dformat = [d.getMonth()+1,

    d.getDate(),

    d.getFullYear()].join('/')+' '+

    [d.getHours(),

    d.getMinutes(),

    d.getSeconds()].join(':');

dtstmp = new Date().toString();

msg.topic = "STG/A/CycleStart";

tp = msg.topic;

pay = msg.payload.digitalSamples.DIO4;

pld =       "INSERT INTO [Dev].[dbo].[MQTTData] (Topic, Payload, Timestamp) "

pld = pld + "VALUES ('" + tp + "', '" + pay + "', '" + dformat + "') "

pld = pld;

msg.payload = pld

return msg;

I would gladly change to UTC, if that is what it takes to make my Dashboard Chart reflect my local time of EST.

I use a query to get the chart data. Here is the entire flow:

[{"id":"c84ec3ca.44a08","type":"xbee-rx","z":"a860ddde.52e5a","name":"XBee","xBee":"72923406.31a05c","x":80,"y":91,"wires":[["45164c96.175d54","28d19cb6.3174c4","5d9b1a9b.ba3854"]]},{"id":"ba2f2f4a.fd432","type":"debug","z":"a860ddde.52e5a","name":"","active":true,"console":"false","complete":"true","x":990,"y":93,"wires":[]},{"id":"18873f4b.0523c1","type":"MSSQL","z":"a860ddde.52e5a","mssqlCN":"3b35b5a4.fd817a","name":"MSSQL","query":"","outField":"payload","x":752,"y":92,"wires":[["ba2f2f4a.fd432"]]},{"id":"27725d2d.6b50e2","type":"rpi-gpio out","z":"a860ddde.52e5a","name":"","pin":"11","set":"","level":"0","freq":"","out":"out","x":694,"y":146,"wires":[]},{"id":"45164c96.175d54","type":"function","z":"a860ddde.52e5a","name":"","func":"if(msg.payload.digitalSamples.DIO4 === 0) {\nmsg.payload = 0;\n}\nelse {\nmsg.payload = 1;\n}\nreturn msg;","outputs":1,"noerr":0,"x":463,"y":145,"wires":[["27725d2d.6b50e2","e68fafac.f0855"]]},{"id":"e68fafac.f0855","type":"debug","z":"a860ddde.52e5a","name":"","active":false,"console":"false","complete":"false","x":705,"y":194,"wires":[]},{"id":"13ced987.c3dcc6","type":"function","z":"a860ddde.52e5a","name":"","func":"// name of the time column values:\nvar tcol = \"Timestamp\";\n// list of columns to be charted:\nvar cols = [ \"Payload\" ];\n\n// iterate over each column of the query results\nvar data = cols.map(function(col) {\n    // iterate over each row of the query results\n    var vals = msg.payload.map(function(row) {\n        // return a data point for each col\n        return [ +row[tcol], +row[col] ];\n    });\n\n    // return data object for each line on the chart\n    return { key: col, values: vals };\n});\n\n// now put the new chart data in the payload\nmsg.payload = data;\nreturn msg;\nreturn msg;","outputs":1,"noerr":0,"x":1029,"y":268,"wires":[["87c28657.1816a8"]]},{"id":"afa157e3.8046e8","type":"MSSQL","z":"a860ddde.52e5a","mssqlCN":"3b35b5a4.fd817a","name":"MSSQL","query":"","outField":"payload","x":829,"y":268,"wires":[["13ced987.c3dcc6"]]},{"id":"87c28657.1816a8","type":"ui_chart","z":"a860ddde.52e5a","name":"","group":"84aa1ca8.6426","order":0,"width":"6","height":"6","label":"On / Off State","chartType":"line","legend":"false","xformat":"HH:mm","interpolate":"step","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"86400","cutout":0,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"x":1213,"y":269,"wires":[[],[]]},{"id":"5d9b1a9b.ba3854","type":"function","z":"a860ddde.52e5a","name":"Function","func":"pld =       \"SELECT Payload, Timestamp \"\npld = pld + \"FROM [Dev].[dbo].[MQTTData] \"\npld = pld + \"ORDER BY Timestamp \" ;\n\nmsg.payload = pld\nreturn msg;\n\n\n\n\n","outputs":1,"noerr":0,"x":467,"y":266,"wires":[["7451747c.9c024c"]]},{"id":"7451747c.9c024c","type":"delay","z":"a860ddde.52e5a","name":"","pauseType":"delay","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":644,"y":267,"wires":[["afa157e3.8046e8"]]},{"id":"28d19cb6.3174c4","type":"rbe","z":"a860ddde.52e5a","name":"","func":"rbei","gap":"1","start":"","inout":"in","x":272,"y":91,"wires":[["52137bdb.4200a4"]]},{"id":"52137bdb.4200a4","type":"function","z":"a860ddde.52e5a","name":"Data Conversion","func":"var d = new Date();\n    dformat = [d.getMonth()+1,\n    d.getDate(),\n    d.getFullYear()].join('/')+' '+\n    [d.getHours(),\n    d.getMinutes(),\n    d.getSeconds()].join(':');\ndtstmp = new Date().toString();\nmsg.topic = \"STG/A/CycleStart\";\ntp = msg.topic;\npay = msg.payload.digitalSamples.DIO4;\npld =       \"INSERT INTO [Dev].[dbo].[MQTTData] (Topic, Payload, Timestamp) \"\npld = pld + \"VALUES ('\" + tp + \"', '\" + pay + \"', '\" + dformat + \"') \"\npld = pld;\n\nmsg.payload = pld\nreturn msg;","outputs":1,"noerr":0,"x":503,"y":91,"wires":[["18873f4b.0523c1"]]},{"id":"72923406.31a05c","type":"xbee-config","z":"","apiMode":"1","rawFrames":false,"convertAdc":true,"vrefAdc":"1200","serialPort":"/dev/ttyS0","lock":true,"baudRate":"9600","dataBits":"8","stopBits":"1","parity":"none","bufferSize":"65536","rtscts":false,"xon":false,"xoff":false,"xany":false,"vmin":"1","vtime":"0"},{"id":"3b35b5a4.fd817a","type":"MSSQL-CN","z":"a860ddde.52e5a","name":"Dev","server":"10.0.0.120","encyption":true,"database":"Dev"},{"id":"84aa1ca8.6426","type":"ui_group","z":"","name":"STG Dashboard","tab":"c5cdd240.287e2","disp":true,"width":"6"},{"id":"c5cdd240.287e2","type":"ui_tab","z":"","name":"Home","icon":"dashboard"}]

Here is what my table looks like:

Here are the data types:

[Colin Law]

Let's concentrate on getting it into the db first. Is it mysql or sqlite or what?

Colin

To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/b6f0619d-643f-4ead-ac39-a3d57626e8a4%40googlegroups.com.

[Mike Arney]

Microsoft SQL

[Colin Law]

On 11 October 2017 at 15:47, Mike Arney <mikespc...@gmail.com> wrote:
>
> Microsoft SQL

Oh, you have my sympathies.
I don't know the details of that, but I am sure that there will be a
datetime column type, though the name may be slightly different. If
you create the column with that type and in the insert query put
something like NOW() for the value for that column it will put the
current time in automatically. You will have to look up the docs for
MSSQL to find exactly what.

Having done that, then when you later fetch that record it should (I
think) automatically be picked up as a timestamp value.

Having said all that, are you sure you need to use MSSQL? If the
purpose of the db is to store time series data (ie values and
timestamps) then you might be better off using influxdb.

Colin

>
> On Wednesday, October 11, 2017 at 10:22:27 AM UTC-4, Colin Law wrote:
>>
>> Let's concentrate on getting it into the db first. Is it mysql or sqlite or what?
>>
>> Colin
>>
>> On 11 October 2017 at 15:09, Mike Arney <mikespc...@gmail.com> wrote:
>>>
>>> I tried to insert my time in UTC, but I get the following error:
>>>

>>> I have only been successful inserting to SQL with this code:
>>>
>>>
>>> var d = new Date();
>>>
>>> dformat = [d.getMonth()+1,
>>>
>>> d.getDate(),
>>>
>>> d.getFullYear()].join('/')+' '+
>>>
>>> [d.getHours(),
>>>
>>> d.getMinutes(),
>>>
>>> d.getSeconds()].join(':');
>>>
>>> dtstmp = new Date().toString();
>>>
>>> msg.topic = "STG/A/CycleStart";
>>>
>>> tp = msg.topic;
>>>
>>> pay = msg.payload.digitalSamples.DIO4;
>>>
>>> pld = "INSERT INTO [Dev].[dbo].[MQTTData] (Topic, Payload, Timestamp) "
>>>
>>> pld = pld + "VALUES ('" + tp + "', '" + pay + "', '" + dformat + "') "
>>>
>>> pld = pld;
>>>
>>>
>>> msg.payload = pld
>>>
>>> return msg;
>>>
>>>
>>> I would gladly change to UTC, if that is what it takes to make my Dashboard Chart reflect my local time of EST.
>>>
>>>
>>> I use a query to get the chart data. Here is the entire flow:
>>>
>>>
>>> [{"id":"c84ec3ca.44a08","type":"xbee-rx","z":"a860ddde.52e5a","name":"XBee","xBee":"72923406.31a05c","x":80,"y":91,"wires":[["45164c96.175d54","28d19cb6.3174c4","5d9b1a9b.ba3854"]]},{"id":"ba2f2f4a.fd432","type":"debug","z":"a860ddde.52e5a","name":"","active":true,"console":"false","complete":"true","x":990,"y":93,"wires":[]},{"id":"18873f4b.0523c1","type":"MSSQL","z":"a860ddde.52e5a","mssqlCN":"3b35b5a4.fd817a","name":"MSSQL","query":"","outField":"payload","x":752,"y":92,"wires":[["ba2f2f4a.fd432"]]},{"id":"27725d2d.6b50e2","type":"rpi-gpio out","z":"a860ddde.52e5a","name":"","pin":"11","set":"","level":"0","freq":"","out":"out","x":694,"y":146,"wires":[]},{"id":"45164c96.175d54","type":"function","z":"a860ddde.52e5a","name":"","func":"if(msg.payload.digitalSamples.DIO4 === 0) {\nmsg.payload = 0;\n}\nelse {\nmsg.payload = 1;\n}\nreturn msg;","outputs":1,"noerr":0,"x":463,"y":145,"wires":[["27725d2d.6b50e2","e68fafac.f0855"]]},{"id":"e68fafac.f0855","type":"debug","z":"a860ddde.52e5a","name":"","active":false,"console":"false","complete":"false","x":705,"y":194,"wires":[]},{"id":"13ced987.c3dcc6","type":"function","z":"a860ddde.52e5a","name":"","func":"// name of the time column values:\nvar tcol = \"Timestamp\";\n// list of columns to be charted:\nvar cols = [ \"Payload\" ];\n\n// iterate over each column of the query results\nvar data = cols.map(function(col) {\n // iterate over each row of the query results\n var vals = msg.payload.map(function(row) {\n // return a data point for each col\n return [ +row[tcol], +row[col] ];\n });\n\n // return data object for each line on the chart\n return { key: col, values: vals };\n});\n\n// now put the new chart data in the payload\nmsg.payload = data;\nreturn msg;\nreturn msg;","outputs":1,"noerr":0,"x":1029,"y":268,"wires":[["87c28657.1816a8"]]},{"id":"afa157e3.8046e8","type":"MSSQL","z":"a860ddde.52e5a","mssqlCN":"3b35b5a4.fd817a","name":"MSSQL","query":"","outField":"payload","x":829,"y":268,"wires":[["13ced987.c3dcc6"]]},{"id":"87c28657.1816a8","type":"ui_chart","z":"a860ddde.52e5a","name":"","group":"84aa1ca8.6426","order":0,"width":"6","height":"6","label":"On / Off State","chartType":"line","legend":"false","xformat":"HH:mm","interpolate":"step","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"86400","cutout":0,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"x":1213,"y":269,"wires":[[],[]]},{"id":"5d9b1a9b.ba3854","type":"function","z":"a860ddde.52e5a","name":"Function","func":"pld = \"SELECT Payload, Timestamp \"\npld = pld + \"FROM [Dev].[dbo].[MQTTData] \"\npld = pld + \"ORDER BY Timestamp \" ;\n\nmsg.payload = pld\nreturn msg;\n\n\n\n\n","outputs":1,"noerr":0,"x":467,"y":266,"wires":[["7451747c.9c024c"]]},{"id":"7451747c.9c024c","type":"delay","z":"a860ddde.52e5a","name":"","pauseType":"delay","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":644,"y":267,"wires":[["afa157e3.8046e8"]]},{"id":"28d19cb6.3174c4","type":"rbe","z":"a860ddde.52e5a","name":"","func":"rbei","gap":"1","start":"","inout":"in","x":272,"y":91,"wires":[["52137bdb.4200a4"]]},{"id":"52137bdb.4200a4","type":"function","z":"a860ddde.52e5a","name":"Data Conversion","func":"var d = new Date();\n dformat = [d.getMonth()+1,\n d.getDate(),\n d.getFullYear()].join('/')+' '+\n [d.getHours(),\n d.getMinutes(),\n d.getSeconds()].join(':');\ndtstmp = new Date().toString();\nmsg.topic = \"STG/A/CycleStart\";\ntp = msg.topic;\npay = msg.payload.digitalSamples.DIO4;\npld = \"INSERT INTO [Dev].[dbo].[MQTTData] (Topic, Payload, Timestamp) \"\npld = pld + \"VALUES ('\" + tp + \"', '\" + pay + \"', '\" + dformat + \"') \"\npld = pld;\n\nmsg.payload = pld\nreturn msg;","outputs":1,"noerr":0,"x":503,"y":91,"wires":[["18873f4b.0523c1"]]},{"id":"72923406.31a05c","type":"xbee-config","z":"","apiMode":"1","rawFrames":false,"convertAdc":true,"vrefAdc":"1200","serialPort":"/dev/ttyS0","lock":true,"baudRate":"9600","dataBits":"8","stopBits":"1","parity":"none","bufferSize":"65536","rtscts":false,"xon":false,"xoff":false,"xany":false,"vmin":"1","vtime":"0"},{"id":"3b35b5a4.fd817a","type":"MSSQL-CN","z":"a860ddde.52e5a","name":"Dev","server":"10.0.0.120","encyption":true,"database":"Dev"},{"id":"84aa1ca8.6426","type":"ui_group","z":"","name":"STG Dashboard","tab":"c5cdd240.287e2","disp":true,"width":"6"},{"id":"c5cdd240.287e2","type":"ui_tab","z":"","name":"Home","icon":"dashboard"}]
>>>
>>>
>>>
>>> Here is what my table looks like:
>>>
>>>

>>> Here are the data types:
>>>
>>>
>>>
>>>
>>>

> To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/28cd39e2-3efe-40cb-b74b-380bab046015%40googlegroups.com.

[Mike Arney]

Here are my data types:

Could you give me a mysql query example? Here is my MSSQL query:

tp = msg.topic;

pay = msg.payload.digitalSamples.DIO4;

d = ???  --- Now() didn't work, Date() didn't work. (Got "ReferenceError: now is not defined")

INSERT INTO [Dev].[dbo].[MQTTData] (Topic, Payload, Timestamp) VALUES ('" + tp + "', '" + pay + "', '" + d + "')

[Mike Arney]

I use MSSQL for a few reasons:

1.) Storing to a SQL Server

2.) Going to send data from all of our machines to the same server (about 400+ sensors).

3.) Other users, in our plant can pull data from our SQL server tables into their own MS Access applications.

Not saying MSSQL is the best choice but, for our users, this is the easiest to accomplish.

[Colin Law]

Have you looked in the mssql docs (or asked your db guy) what sql syntax you have to use to insert the current time?

For mysql you would need something like

INSERT INTO [Dev].[dbo].[MQTTData] (Topic, Payload, Timestamp) VALUES ('" + tp + "', '" + pay + "', NOW())

Note that the lack of quotes round NOW(), you just want that to appear in the query as text without quotes, the same as INSERT appears. So the actual query will be something like

INSERT INTO [Dev].[dbo].[MQTTData] (Topic, Payload, Timestamp) VALUES ('some/topic', '123.45', NOW())

Colin

To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/fe134ef5-2ee9-482b-8c33-68eae192d308%40googlegroups.com.

[Colin Law]

Those may well be good reasons.

Colin

> https://groups.google.com/d/msgid/node-red/f350f21b-f258-4a44-9241-516fd0256b41%40googlegroups.com.

[steve rickus]

In this case, since you are only using the current timestamp, I would let the database handle it during insert. There are a couple ways to set that up...

You can 1. use the mssql syntaxt to explicitly set the value in your list of values, OR (and this is probably better for your case),

2. set the default value in the table for that column to be the current timestamp.

Option #1 means that you don't need to get the current time in javascript. Just modify your insert statement to use the builtin system timestamp:

INSERT INTO [Dev].[dbo].[MQTTData] (Topic, Payload, Timestamp) VALUES ('STG/A/CycleStart', 1, CURRENT_TIMESTAMP);

BTW, you don't need quotes around the numeric value for the payload value...

Option #2 is nice because you can drop that column from your insert statement completely, like so:

INSERT INTO [Dev].[dbo].[MQTTData] (Topic, Payload) VALUES ('STG/A/CycleStart', 1);

And you are ensured of getting a valid value in each new row that is inserted. Of course, before you can do that, you will need to alter your table definition, and set the default value for that column to CURRENT_TIMESTAMP

Now when it's time to query the database and display the line chart, the query should return a UTC timestamp, and the chart should show the x-axis labels in your local timezone. The reason you were getting errors before is that the javascript toString() function creates a human-readable date string that mssql could not parse back into a valid date. It's always safest when dealing with date/time values in javascript to use the internal format (13 digit millis) or stick with the toISOstring() function if you really need to pass a string representing the date.

> Date.now()
1507738769623
> new Date().toString()
'Wed Oct 11 2017 12:19:42 GMT-0400 (Eastern Daylight Time)'
> new Date().toISOString()
'2017-10-11T16:20:01.282Z'

--

Steve

[Mike Arney]

I change the default value of my timestamp to CURRENT_TIMESTAMP. 

I did option 2:

msg.topic = "STG/A/CycleStart";

tp = msg.topic;

pay = msg.payload.digitalSamples.DIO4;

pld =       "INSERT INTO [Dev].[dbo].[MQTTData] (Topic, Payload) "

pld = pld + "VALUES ('" + tp + "', '" + pay + "') "

pld = pld;

msg.payload = pld

return msg;

Time in table shows :

2017-10-11 15:15:36.073 (Which is Local)

Chart still shows 4 hours behind. 

Raspberry Pi3 is set to local time.

I'm still confused, but I really like option 2!

Thank you

[steve rickus]

Indeed, this is (one of the many reasons) why we avoid mssql... *sigh*

Apparently your mssql database server is running in the EDT timezone, so CURRENT_TIMESTAMP will also be calculated in that timezone. You could try the GETUTCDATE() function instead, but I'm fairly sure you cannot use the output of a function as the default value inside the table definition. If you try it, then we will both know that answer.

In that case, you will have to revert to option #1, but put the GETUTCDATE() function (unquoted) inside your VALUES ( ... ) expression. Sorry to mislead you, but it's been a long time since I have used mssql, and this is different than most other dbs.

If you still find the value in the database is localtime, then we can revert to calculating the utc datetime value in javascript, formatting the string to be parseable by mssql, and force it into the table that way -- but that should not be necessary. 

--

Steve

[Mike Arney]

When we get it to UTC in the database, will it show as local time on my chart?

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---

You received this message because you are subscribed to a topic in the Google Groups "Node-RED" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/node-red/ff5chUeXhJI/unsubscribe.

To unsubscribe from this group and all its topics, send an email to node-red+unsubscribe@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/b30e24e9-8da3-4b4e-ae07-287a4eab645f%40googlegroups.com.

[Julian Knight]

Sorry, haven't had time to go through this thread so I can't answer directly. However, there is a node that may help. node-red-contrib-moment will happily convert timezones for you amongst other things. My node so if it doesn't do what you need, just raise an issue on github.

To unsubscribe from this group and all its topics, send an email to node-red+u...@googlegroups.com.

[Mike Arney]

Julian,

My database contains my local datetime format. I have just found that the chart shows the correct timezone until the first data point is populated, then it shows -0400 hours. I am using a query to pull the "Payload" as Y axis and "Timestamp" as X axis. I was not able to make your node work for this.

[steve rickus]

That works for me -- although I'm using mysql for the most part, the chart does not care where the data come from...

Have you been able to get the UTC time into the database table yet? Most of the answers on stack overflow keep repeating how important it is to only use UTC timestamps in your sql database, and do the timezone conversions on the client or presentation layer. And I agree that this is the first thing you need to do.

If you cannot configure your table default to be the current UTC timestamp (did the GETUTCDATE() function work?), then let's try building it in your function's js code, and passing it in your list of VALUES on the INSERT statement. Most databases can understand a date string formatted as YYYY-MM-DD hh:mm:ss.ddd, so let's revert back to your original INSERT format and see if that string inserts properly into mssql. Here is one way to get the current utc datetime string:

var now = new Date();  // the current datetime as a Date object
var iso = now.toISOString();  // converted to YYYY-MM-DDThh:mm:ss.dddZ format
var utc = iso.replace(/^(.*)T(.*)Z$/, "$1 $2");  // with the 'T' and 'Z' removed

If you don't want and/or need the fractional seconds, you can modify the regex in the replace function to be:

var utc = iso.replace(/^(.*)T(.*)\.\d+Z$/, "$1 $2");  // without fractional seconds

Once the new inserts are properly storing utc timestamps, you may need to either delete all the old records, or add 4 hours to the existing datetime values. You can modify the existing data with a sql statement something like this (untested):

update MQTTData
   set Timestamp = dateAdd(second, dateDiff(second, getDate(), getUtcDate()), Timestamp)
;

The idea is to get the "current" difference (in seconds) between the local time and the utc time, and add that value to all the existing Timestamp values. Please note that this will not work if some of your existing values were created under EST (-05:00) and others were created during EDT (-04:00), but I'm fairly sure that's not the case with your data.

--

Steve

[Mike Arney]

Changing my default value to (GETUTCDATE()) worked! Data is in UTC and Chart is in Local Time. Woo Hoo!

Thanks everyone!