SQLite search for earliest date

Databases
1

Trying to find the earliest entry in my SQlite database by searching for Min(DateTime) but can’t figure out the sql command. Any help would be awesome.
DateTime is formatted as Xojo.Core.Date

[code]// Clear the ListBox
ListBox2.DeleteAllRows

// Get earliest date from the table.
Dim sql As String

//STUCK HERE
sql = "SELECT MIN(DateTime) FROM Sensor_Readings "

Dim data As RecordSet
data = App.DB.SQLSelect(sql)

While Not data.EOF
ListBox2.AddRow(data.Field(“SN”).StringValue, _
data.Field(“DateTime”).StringValue, _
data.Field(“Temp”).StringValue, _
data.Field(“PSI”).StringValue, _
Wend
data.Close[/code]

2

??? Xojo.Core.Date is an object, not a value, so you clearly did not do that.

The important question - that you don’t provide - is how did you SAVE the date and time?

I would probably have saved the Date as TotalSeconds.

3 
SELECT * FROM Sensor_Readings ORDER BY DateTime ASC LIMIT 1
4

but ORDER BY Datetime will only work properly IF the data field is in a sortable format (totalseconds, or SQLDATE)
if you have it like “Jan 25,2018” and “Dec 2,2018” they will NOT be returned in proper order

5

Dave is right, of course :slight_smile:
In this case (unsortable format) it may be easier to find the oldest record by sorting on the RowID column.
A good read: http://www.sqlitetutorial.net/sqlite-autoincrement/

/!\ EDIT: Not correct, as pointed by Tim below.

6

You can’t safely assume RowID is any particular order unfortunately.

7

Thanks Tim, you made me learn something (more) today :slight_smile:
Indeed RowId is only garanteed to be “in increasing order” if a column in the table is declared as INTEGER PRIMARY KEY AUTOINCREMENT. And if this is the case you can directly use this column to sort your datas.

8

[quote=378756:@Renaud GOMBERT WEBPORTAGE]Thanks Tim, you made me learn something (more) today :slight_smile:
Indeed RowId is only garanteed to be “in increasing order” if a column in the table is declared as INTEGER PRIMARY KEY AUTOINCREMENT. And if this is the case you can directly use this column to sort your datas.[/quote]

ROWID doesn’t guarantee order. if ROWIDs are removed in the middle, those #s might be filled in when adding new rows. certain operations change the ROWIDs.

So if you want a UNIQUE ID that is in order of CREATION of the row, make your own column with that ID in it. More work on your side but that is the only way to guarantee it.

9

… and some users can input older dates that they “forgot” to enter at due time.

I hope Dan’s data can be easily sorted.

10

In “most” databases, RowID indicates the order the records were entered, but have no bearing on the content of the record.
But as Scott mentioned, there are operations that can/will alter that order, and in some cases they will be reused, and VACUUM will for sure alter them (unless they are marked as the PK as well). So RowID at best identifies a particular row at an Instant in time,

11

We might as well wait until the OP tells us how his data is SAVED to the db. His original post, as noted by Markus, doesn’t really tell us that. If it’s saved to the db as SQLDateTime (d.ToText in this case, using the New Framework as he indicates), then he can simply do what Renaud suggested:

SELECT * FROM Sensor_Readings ORDER BY DateTime ASC LIMIT 1

But until we hear how the data is saved, not much we can do here.

12

In my case the RowID would probably work for the search because the records are created as the time increments (data logging sensors) but I understand the pitfalls and won’t use that. Since my date format is stored as Year:Month:Day:Hour:Min:Sec then searching by date is the best search for me.
My problem is grasping the sql commands and understanding the sqlite.org website.

Thanks Renaud, and all.

13

Are you saying you date/time is spread across 6 fields???
if so, and this is messy then

SELECT * from sensor_readings ORDER BY (year*31536000)+(month*2678400)+(day*86400)+(hour*3600)+(min*60)+sec LIMIT 1

or better yet (if you can)
add a new field, and write a short Xojo script to create a SQLDate/Time from those 6 fields

14

I think that’s the worst place to learn sqlite syntax. Use one of the many online free tutorials for that.

15

As Ralph said… not a good place to “learn”… but a good reference once you know what things mean and how they work.

SQL is one of those “Ah Ha!” things… you will read about it, play with it, and then all of a sudden you go “Ah Ha!” and it only then starts to make sense… Lucky for me that moment came years ago :slight_smile:

16

It is stored in one field that looks like this “2018-03-21 12:34:00”

I will keep searching . . .

17

THAT is the SQLDATETIME format!

so

SELECT * FROM Sensor_Readings ORDER BY DateTime ASC LIMIT 1

will work

unless of course you have multiple reading occurring PER second

18

Yes you are correct Dave, that does work.

I wasn’t clear, I meant I will keep searching for a better place to learn sqlite.

BTW, this is the best forum ever. I have always been an avid “search and read” user of forums but not really contributed much. You guys are all so helpful, thanks again.

19

Do not concentrate on learning SQLite … Learn “SQL”… the concepts are nearly identical across all DB engines. but SQLite has its quirks (as in it doesn’t really have “datatypes” like other DB’s)… but if you learn SQL based on what SQLite does or doesn’t do, then you will have major issues in the future if you decide to change, or need to “scale up”…