Finding Previous Date in SQLite Database

Databases
1

Hello all!

I am coming from a background with very little database or SQL experience and have run across a problem that has me stumped. I’m hoping someone can set me on the right path.

I have a project that tracks readings from various machines over time. It makes use of a SQLite database to keep all the records it needs access to. In this SQLite database, there is a table that consists only of readings themselves. This table has columns for Unique ID, Date (stored in SQLite Date format), Machine Number (an Integer that refers to the unique ID in a separate “Machines” table) and the data of the reading itself (which is an Integer).

The application automates taking readings from each machine at various times of the day. For some machines, these readings can happen more than once a day, for others, it only happens once a day. It then adds a database record to the Readings table with the data from the reading. All readings from all machines are stored in that one table.

This all works great. I have also been able to display filtered lists based on various criteria (mostly date ranges input by the user) with no issues.

Here is the problem that has me stumped. One of the requirements for this application is that the user can select a single reading from a list of displayed readings. When a user does that, the application needs to display the difference between the selected reading and the most recent previous reading for the same machine. For example, let’s say the selected reading has the following data:

Unique ID: 542
Machine ID: 4
Date: 2015-03-11 16:57:23
Reading: 100236

When the user selects this entry in the application’s UI, the application needs to find the next previous entry for that machine:

Unique ID: 538
Machine ID: 4
Date: 2015-03-11 12:17:09
Reading: 100012

and find the difference between them: 100236-100012 = 224 and display that.

So, obviously, I need to run a query to get the data from the selected entry and a query to retrieve the previous entry. The problem is, I don’t know what time the previous reading was taken as that is not standardized. Therefore, I don’t know how to figure out what query I need to run to find that previous entry.

Does anyone have any pointers as to what I should be doing here? Can I assume that entries in the database will stay in order of when they were added? If that was the case, then I just query for all entries for that machine and filter for everything before the selected entry’s date and use RecordSet.MovePrevious. But, I don’t know that I can assume that the entries would stay in order.

I’d really appreciate any advise from those with more experience with SQL than I have.

Thank you!

2

The entries are retrieved in the order you specify. Don’t rely on the internal table order. Since you have the date, you ought to be able to use it:

SELECT * FROM Machines WHERE MachineID = 4 AND Date < '2015-03-11 16:57:23' ORDER BY Date DESC
3

And if you just want that specific pair of records, add “LIMIT 2” at end of the query. It makes it even faster and less resource hungry.

4 
SELECT * FROM Machines WHERE MachineID = 4 AND Date <= '2015-03-11 16:57:23' ORDER BY Date DESC LIMIT 2

It’s necessary “<=” instead of “<” to include THAT record and the prior.

5

Thank you Paul and Rick! That’s the nudge in the right direction I needed!