Another BaseStation SQLite Question

[Pete]

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

[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

[Anmer]

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

Code Select Expand

SELECT * FROM Aircraft

WHERE Interested =1

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

Code Select Expand

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.

[Pete]

Hi Anmer

Thank you very much indeed.

Regards

Pete

[benipaz]

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

Code Select Expand


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


[Pete]

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

[Anmer]

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:

Code Select Expand

SELECT * FROM Aircraft

WHERE Interested =1


[Pete]

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

[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!

[Anmer]

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:

Code Select Expand

SELECT * FROM Aircraft

WHERE Interested = NULL

It might return records missing a checkbox?

[Pete]

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

[Anmer]

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

Code Select Expand

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

[Pete]

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.

[Faramir]

The combination:

Code Select Expand

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

[DaveReid]

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.