# Reading Data from a mySQL for a date range

**URL:** <https://discourse.nodered.org/t/reading-data-from-a-mysql-for-a-date-range/45455>\
**Category:** General\
**Created:** [10 May 2021 21:25 UTC](https://discourse.nodered.org/t/reading-data-from-a-mysql-for-a-date-range/45455 "2021-05-10T21:25:45Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![mattb1969](https://avatars.discourse-cdn.com/v4/letter/m/e47c2d/32.png) [@mattb1969](https://discourse.nodered.org/u/mattb1969)\
**Post date:** [10 May 2021 21:25 UTC](https://discourse.nodered.org/t/reading-data-from-a-mysql-for-a-date-range/45455/1 "2021-05-10T21:25:46Z")

</div>

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

```auto
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

```auto
//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);

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [10 May 2021 22:17 UTC](https://discourse.nodered.org/t/reading-data-from-a-mysql-for-a-date-range/45455/2 "2021-05-10T22:17:30Z")

</div>

You need quotes around strings in a query

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [11 May 2021 09:00 UTC](https://discourse.nodered.org/t/reading-data-from-a-mysql-for-a-date-range/45455/3 "2021-05-11T09:00:33Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![mattb1969](https://avatars.discourse-cdn.com/v4/letter/m/e47c2d/32.png) [@mattb1969](https://discourse.nodered.org/u/mattb1969)\
**Post date:** [11 May 2021 18:44 UTC](https://discourse.nodered.org/t/reading-data-from-a-mysql-for-a-date-range/45455/4 "2021-05-11T18:44:21Z")

</div>

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

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

---

<div class="post-metadata">

**Author:** ![mattb1969](https://avatars.discourse-cdn.com/v4/letter/m/e47c2d/32.png) [@mattb1969](https://discourse.nodered.org/u/mattb1969)\
**Post date:** [11 May 2021 18:55 UTC](https://discourse.nodered.org/t/reading-data-from-a-mysql-for-a-date-range/45455/5 "2021-05-11T18:55:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [11 May 2021 19:05 UTC](https://discourse.nodered.org/t/reading-data-from-a-mysql-for-a-date-range/45455/6 "2021-05-11T19:05:10Z")

</div>

> [@mattb1969](#):
>
> ```auto
> endTime = msg.starttime.setMinutes(msg.starttime.getMinutes() + timePeriod);
> msg.endtime = endTime.toISOString();
> 
> ```
> 
> I get this error:
> 
> > message: "TypeError: endTime.toISOString is not a function”

Because you call setMinutes on `msg.starttime`

from the docs...

> [Return value](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/setMinutes#return_value)
> 
> The number of milliseconds between 1 January 1970 00:00:00 UTC and the updated date.

Try this instead

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

```

---

<div class="post-metadata">

**Author:** ![mattb1969](https://avatars.discourse-cdn.com/v4/letter/m/e47c2d/32.png) [@mattb1969](https://discourse.nodered.org/u/mattb1969)\
**Post date:** [11 May 2021 19:21 UTC](https://discourse.nodered.org/t/reading-data-from-a-mysql-for-a-date-range/45455/7 "2021-05-11T19:21:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [10 July 2021 19:21 UTC](https://discourse.nodered.org/t/reading-data-from-a-mysql-for-a-date-range/45455/8 "2021-07-10T19:21:38Z")

</div>

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