Need 'SELECT' advice to retrieve latest database data

I have a SQL database with the following three columns: id, temperature and humidity.

These values are fed to the db 24/7. I would like to feed this data to a Chart.js line chart. This line chart would be very similar to a stock chart that automatically refreshes itself with new data. I think that retrieving this data every 60 seconds would be fine.

My problem is this: how do I code the SELECT query to just return the new data?

My only assumption would be to use the id number because it is unique. If that is true then I would have to keep track of the id number to make a comparison with. I could be wrong about this, but nothing else makes sense to me. If this is true, then the query would have to include the id value as well. I do not know how to make this comparison.

SELECT id, temperature, humidity FROM myDatabase
         WHERE new_id > old_id

Any help with this matter will be greatly appreciated.

Thank you.

Are those the only three columns? Shouldn’t there be a column for date/time?

I suppose a query could be created if the id was an integer that increased by one each time - you would just look for the largest id but that doesn’t seem right to me.

Your query won’t work because your table doesn’t have new_id or old_id columns.

I was speaking in general terms, not trying to be overly specific. My focus was how to define the SELECT query method. However, you are correct - there is a time_stamp column in the db table.

My example SELECT query was just that - an example.

Thanks.

Then this should do what you want?

SELECT MAX(time_stamp),temperature,humidity 
FROM myDatabaseTable

Thanks for your response.

In truth, I do not know if that would work or not. However, I need to refresh the chart with all of the new data that has been sent to the db over some time frame, so it would appear to me that usng MAX would only select the newest data not all of the new data. I hope this makes sense.

Since my last post, I now think that the correct way to get the latest data is to use the following Chart.js function:

function addData(chart, label, data) {
    chart.data.labels.push(label);
    chart.data.datasets.forEach((dataset) => {
        dataset.data.push(data);
    });
    chart.update();
}

I will experiment with this and know more later.

Thanks again for your help.

1 Like

What happened when you tried it? ™

The long and the short is, if you want a pull that gets data since the last time you pulled data, you’re going to need to know when it was you pulled data.

That is one way to use an artificial key, yes. The presence of the artificial key is, perhaps, somewhat another debate to have.

Well, your first data pull will pull back all of the current data. So if we’re going with your idea then… find the highest ID in that list. That’s your ‘old id’. Your query then becomes to select every id thats greater than that id; when you get the data, update the stored id so that it reflects the new ‘old id’.

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