Radarspotting

Hi All. 

A big thanks to everyone who helped me with my last SQLite problem.  I'm still tinkering with my BaseStation database and have hit another couple of stumbling blocks. 

Firstly, if an aircraft is 'interested' it is represented with a 'tick' in the database.  If I want to create a script that includes if an aircraft is interested, how is the tick represented?

Secondly, in the FirstCreated and LastModified fields the date time is (for instance) entered as 2023-03-18 12:02.296.  If I wanted to search for a certain date, how could the minutes and seconds be represented by a wildcard so as include all entries from that date?

Any help greatly appreciated.

Pete

Hi

Sorry, I got the date format slightly wrong.  It should be (eg) 2023-03-18 14:24:44.908.

I hope it didn't confuse anyone - it did me!

Cheers

Pete

This should find the records where the Interested checkbox has a value:

SELECT * FROM Aircraft

WHERE Interested =1

Not sure what date range you want to query but this works:

SELECT *   FROM      Aircraft
WHERE     FirstCreated BETWEEN
'2022-01-01 00:00:00' AND '2022-12-31 23:59:59';

Just flex the dates and times.

Hi Anmer

Thank you very much indeed.

Regards

Pete

Quote from: Pete on March 18, 2023, 02:05:44 PM
If I wanted to search for a certain date, how could the minutes and seconds be represented by a wildcard so as include all entries from that date?

you can use date part only in where condition. sql engine will add time 0 to the dates automatically. for 2023-03-17 it will be

SELECT * FROM Aircraft
WHERE FirstCreated BETWEEN '2023-03-17' AND '2023-03-18'


Hi and thank you benipaz for your reply.

I have only tried the first part of Anmer's reply so far but I've found a strange result in that it's only picking up 64 records as Interested.  The only part of the script I've had to change is to substitute an * for the = as it was coming up with a syntax error. 

So my query now reads:  SELECT * FROM Aircraft WHERE Interested =1.  Is there any reason why this is not picking all the 'Interested' entries?

Pete

What app are you using to run the SQL query?

I have a sqb file with over 68,000 aircraft records.

I set each one to have a check against Interested.

Running this sql returned every record:

SELECT * FROM Aircraft

WHERE Interested =1


I'm using SQLite Expert Personal.

I agree, there is no reason that I can see that this sql shouldn't work correctly.  The only problem I can think of is that there're corrupt areas in the Interested field of the database.  Having said that, I've run the sql again with the line:  WHERE Interested =0 and it picks up 41,000 Not Interested records as it should. 

Is there a formula that I could use showing that Interested is not equal to 0 rather than using 1?

Regards

Pete

I've just had a look on the internet and it seems <> 1 gives the result I'm after so I'll give it a go later and report back!

Quote from: Pete on March 19, 2023, 02:23:53 PM
Is there a formula that I could use showing that Interested is not equal to 0 rather than using 1?

Thanks.  I'm also using SQLite xpert personal.

The interested column is populated by a checkbox.  The checkbox should either have a "tick" or not.  "Interested=1" should return all those with a tick.  Maybe some of the records are missing the checkbox?

Try this:

SELECT * FROM Aircraft

WHERE Interested = NULL

It might return records missing a checkbox?

No, unfortunately it didn't return any results therefore I don't think I'm missing any checkboxes.

I have now found out that both of the following sql work in isolation:

SELECT * FROM Aircraft WHERE Interested <>0

and

SELECT * FROM Aircraft
WHERE FirstCreated BETWEEN '2009-01-15 00:00:00' AND '2009-01-15 23:59:59';

My final problem is combining them together to only return Interested aircraft on 15 Jan 09 but I get a syntax error.  Hopefully one last piece of help should finally solve the problem.  I think the second part should select from something other than Aircraft but I can't work out what that should be.

Pete

This works for me, no syntax error.  Your code has a semi-colon at the end?

SELECT * FROM Aircraft
WHERE FirstCreated BETWEEN '2009-01-15 00:00:00' AND '2009-01-15 23:59:59'

I think I've cracked it with a simple 'AND' and deleting the second 'SELECT * FROM Aircraft WHERE'.

Hopefully, that's it!

Thanks for your help. Anmer.

The combination:
SELECT * FROM Aircraft
WHERE STRFTIME('%Y-%m-%d', FirstCreated) LIKE '2009-01-15%'
AND Interested <>0

SQLite does not enforce strong typing, so it's possible that you have some records with a numerical value 1 in the Interested column and some with a string value "1".

Try this:

Select Interested, count(*) from AIRCRAFT
Group by Interested;

You should only get two rows in your result.

Is there a way of checking for this case and changing/forcing the "1" to be the correct format for the boolean (TBH - I am not sure if it should be a string or a number)

Hi

Interested is a boolean and is defined in Basestation as:

Interested boolean not null default 0,

So it should never have a value of null. As a boolean the test can be = true or = false. As Dave says SQLite does not enforce strong typing so depending on the app you are using you may find you get more consistent results using them. Please note I did say may!

Hi Dave

Apologies for the delayed reply as I've been away from my computer.  I've tried your suggested query and unfortunately, it returns a third row with 66 entries in it.

Is  there anyway of developing the sql script to produce a list of those 66 entries?

Regards

Pete

Probably.

What are the 3 values for Interested in your results table ?

Hi

The three rows have returned the following:

Rec No   Interested   count(*)
  1            (a Tick)     34439
  2            (blank)     41254
  3            (a Tick)     66

It seems (to me anyway) that the 66 entries in row 3 have had something other than a '1' entered into them.

I hope this helps.

Pete

SQLite Personal is telling us that there are two different non-zero values in the Interested column, both being interpreted as True - but it won't tell us what those values are.

Try the following (back up your SQB first):

Update Aircraft Set Interested = 1 where Interested <> 0

Then re-run the original query and report how many rows/values you get.

If you use DB Browser it shows the actual values in the Interested column whereas SQLite Expert doesn't.

Using DB Browser you can sort on the Interested column which should show the records containing values other than 0 and 1.

Thanks to you both.  The third row has now gone completely and rows one and two display numbers I would expect to see.  The database has changed since I gave you the original figures so that's to be expected.  Thanks again.

Pete