Reading Data from a mySQL for a date range

General
1

I have been going round in circles for several hours now without any success, so I really hope that somebody can help. I have a mySQL db that contains readings for every 30 seconds and I am trying to write a Flow that will extract 5 mins of this so I can average them. I can’t get the SQL statement right, no matter what I try.

My flow first reads the last read date which is put into msg.payload from the db and I then try to determine the new end point (5 mins later).

var defaultStart = new Date('January 1, 2000 00:00:00');
var endTime = new Date('January 1, 2000 00:00:00');
var timePeriod = 5;

if (msg.payload.length < 1) {
    // There is no previous reading
    msg.starttime = defaultStart;
}
else {
    // Need to set starttime to the value received from the db
    msg.starttime = msg.payload;
}
endTime = msg.starttime.setMinutes(msg.starttime.getMinutes() + timePeriod);
msg.endtime = endTime;
return msg;

The resulting values are fed into the next node that creates the SQL statement

//create SQL command
var sql="Select * from cv_temperature where timestamp between " + msg.starttime +" and " + msg.endtime;

var msg1={}; //create new msg object
//The Sqlite node expects the command in the topic field
msg1.topic=sql;
return msg1;

But the resulting SQL statement looks like this

topic: "Select * from cv_temperature where timestamp between Sat Jan 01 2000 00:05:00 GMT+0000 (Greenwich Mean Time) and 946685100000”

Which is rejected by the database with the following error:

Error: SQLITE_ERROR: near "Jan": syntax error

I can see why it is failing, but I have tried a number of things to fix it without success.

I want to be able to read a time range of values, does anyone know or can point me at ways to do this please?

db table format for info:
create table cv_temperature(id integer primary key autoincrement, timestamp datetime, temperature real);

2

You need quotes around strings in a query

var sql="Select * from cv_temperature where timestamp between '" + msg.starttime +"' and '" + msg.endtime + "'";

That will fix the syntax error but I'm not certain the datetime strings you are using in the query will work as you expect.

3

The first thing you need to do is to work out what format you need the timestamp for mysql. So best to research that first. It may depend on the column type, I don't know. Once you know what you want then you probably want something like toISOString() to convert the Date object.

4

Thanks Steve, that bit had really helped - I no longer get an error, so definitely a step forward.

You’re right Colin, if I could figure it out, it would be easier. The field is of type datetime as it is a timestamp. I’ve tried toISOString and either y syntax is wrong or it is not valid. With the code below:-

endTime = msg.starttime.setMinutes(msg.starttime.getMinutes() + timePeriod);
msg.endtime = endTime.toISOString();

I get this error:

message: "TypeError: endTime.toISOString is not a function”

And I can’t deem to find a solution to it.

5

I can get results from the database with commands like

Select * from cv_temperature where timestamp between '2000-01-01 00:05:00' and '2021-05-11 00:00:00’;

From my research, I have been unable to figure out how to format the date object accordingly

6

Because you call setMinutes on msg.starttime

from the docs...

Return value

The number of milliseconds between 1 January 1970 00:00:00 UTC and the updated date.

Try this instead

var endTime = new Date(msg.starttime);
endTime.setMinutes(endTime.getMinutes() + timePeriod);
msg.endtime = endTime.toISOString();
7

Hi Steve,

You are fantastic, now that you made it clear and spotted the error in my code, it works, many many thanks.

The error message wasn’t clear that the problem was 2 fold, my syntax and the fact that I wasn’t setting endTime correctly.

8

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.