From data picker node to UTC

[NODE777]

Hi

I would need to convert to ms UTC from the selected data with the date selector node in order to make a sqlite query using the SELECT clause to my db.
The db only includes the UTC time.
I'm using the data picker node hooked up to moment node and storing that date on global variable so I'm going to be able to retrieve that value to do a db query.
The goal is to select a time range from the db for display on a chart or table.
I don't have enough background on JS Date object, I google this subject but I could get an answer. .
Any help with that?

Thanks

[zenofmud]

Did you look at the options for the Moment node's Output?

You might want to look at that link...

[TotallyInformation]

@NODE777

The db only includes the UTC time.

And that's how it should be

Always store as UTC and convert for display.

Just be aware, that most systems use SECONDS not MILLISECONDS as JavaScript does.

So if you are getting a JavaScript value such as from Date.now(), that is in ms but if you are getting a date as a number from other systems, you will likely need to multiply by 1000 to use with JavaScript..

[Colin]

If you have a js Date object you should just be able to convert that to a string using toString(). The database server should understand that.

[NODE777]

@zenofmud @TotallyInformation@Colin
Thank you guys for the tips I'm working on that ! ,

[NODE777]

Hi again

I'm facing an issue that maybe can be silly but it prevent me from move forward with the topic I asked help for on 1/30/20.

My db hvac1 has a column named as UTC NUMERIC.
I'm using a couple of data picker to select data between two days range.
Those data picker are hooked up to 2 Moment nodes.
The moment nodes are set up so the output message are global.stday
and global.enday.

Those variables, stday and enday contain the corresponding utc timestamp as numeric value.

I'm trying to implement a SELECT * FROM hvac1 WHERE UTC BETWEEN stday AND enday using a function node.
Above I tried writing as 'stday' and stday, the same for enday but it doesn't work.

I wrote this code inside the function node and connected to sqlite node and it does work using numeric timestamp s as you can see below.

var stday='1580748250808';
var enday='1580753650821';
var b= "SELECT * FROM hvac1 WHERE UTC BETWEEN '1580748250808' AND '1580753650821'      " ;
msg.topic=b;

return msg;

The point is that I would need your help to write :
"SELECT * FROM hvac1 WHERE UTC BETWEEN '1580748250808' AND '1580753650821'" ;
but using the variables stday and enday once they has been retrieved from global storage,

Thank you very much for some help.

[TotallyInformation]

To help, we need to know the database type and the node you are using to write to it.

[Colin]

Also add a debug node showing the query you are sending to the dB so you can check it is correct. Plus you say it doesn't work but have not explained what you mean by that, is it wrong data returned or error message or what?

[fieldgoal]

hey node777,

you just need to put the right string escape sequences in your querry:

var stday='1580748250808';
var enday='1580753650821';
var b= "SELECT * FROM hvac1 WHERE UTC BETWEEN ' " + stday + " ' AND ' "+ enday +" '      " ;
msg.topic=b;

return msg;

i'm not sure why you put your unix timestamp in ' ' these single quotes tho.

And to check if your string is correctly formatted you can use a debug node, as colin suggested.

[Colin]

@fieldgoal is right, you should not have quotes round the times, they are numbers not strings or field names. I guess that it does work with the quotes as the db is converting to numbers for you, but that is a waste of time and electricity.

[NODE777]

@Colin
@TotallyInformation
@fieldgoal

Hi there !!!!
Thank so much for your help.

Here it is what is working
It was about using the right string escape as fieldgoal suggested me, plus all your questions that helped me to get a workaround on this

I was taken this SELECT clause for implement this function:

SELECT * FROM hvac WHERE UTC BETWEEN 'stday' AND 'enday'
I took this from some sqlite tutorial.

These are the nodes that work to select from stday at Local time and enday
at local time.

Thanks so much to all of you.