Retrieving Data using sqlite with conditions

Solar2D General Questions/Discussion
#1

Hi,
I have a financial management app I am working on and have been struggling to retrieve the data that i inserted, with conditions. I have two rows in my ‘income’ table labelled ‘PK’ and ‘timeStamp’. Every time a value is inputted, a timestamp is as well so then I can reference this when retrieving values. However, I can’t seem to figure out how to actually select these values according to their time and display them. This was my ‘test’ code which shows my idea of what I aimed to do, however it does not work.

for row in db:rows("SELECT * FROM income WHERE timeStamp BETWEEN os.date(""!%Y-%m-%d-7"") AND os.date(""!%Y-%m-%d"")") do

	local weekIncomeText = row.PK
	local t = display.newText( sceneGroup, weekIncomeText, 200, 2*row.PK, nil, 16)
	t:setFillColor( 0, 0, 0, )
	end

(I did have a similar forum post earlier, but I think it won’t be seen due to it being over a month old)
Thanks!

#2

os.date is not valid SQL (its lua). You need to construct a valid SQL query.

#3

Hi @MOULTC15A,

Try

local seven_days_ago = os.date( "!%Y-%m-%d", os.time() - 24*60*60*7 )
local now = os.date( "!%Y-%m-%d" ) 
local query = [[SELECT * FROM income WHERE timeStamp BETWEEN "]] .. seven_days_ago .. [[" AND "]] .. now .. [["]] 
--local your_query = "SELECT * FROM income WHERE timeStamp BETWEEN os.date(""!%Y-%m-%d-7"") AND os.date(""!%Y-%m-%d"")"
print( query )
-- print( your_query ) -- You get an error

Read more

Have a nice day:)
ldurniat

#4

Thanks for your input,

It seems to have ‘kind’ of worked. It does know ‘now’ and ‘seven days ago’, however, the console prints what query states. So it ends up printing SELECT * FROM income WHERE timeStamp BETWEEN “2020-09-10” AND “2020-09-17”.

It seems to skim/ignore the SELECT statement?

#5

Hi @MOULTC15A,

if you copy/pasted what @ldurniat wrote, the query will just print the statement. You have to execute the query like below:

db:exec(query)

#6

I have printed query statement to show you what you get.

Probably code below should work

local seven_days_ago = os.date( "!%Y-%m-%d", os.time() - 24*60*60*7 )
local now            = os.date( "!%Y-%m-%d" ) 
local query          = [[SELECT * FROM income WHERE timeStamp BETWEEN "]] .. seven_days_ago .. [[" AND "]] .. now .. [["]]     
for row in db:rows( query ) do
    	local weekIncomeText = row.PK
    	local t = display.newText( sceneGroup, weekIncomeText, 200, 2*row.PK, nil, 16)
    	t:setFillColor( 0, 0, 0, )
end
#7

Thanks for that,
This makes sense, however I was wondering how to add the values in the database together when displaying them? For example, currently a 20 will show on the screen (as 20 is a value in the database from between these time frames). However, when another value is added into database it will display both 20 and say… 50 on the screen. My aim is to try to get it to show 70 instead. I tried to use the SUM function such that the query line looks like this:
[[SELECT * SUM(PK) FROM income WHERE timeStamp BETWEEN "]] .. seven_days_ago .. [[" AND "]] .. now .. [["]].
This came back with an error though.

#8

You should use dates in SQL rather than the way you are doing it now.

 SELECT SUM(PK) FROM Income WHERE timeStamp > DATE_ADD(NOW(), INTERVAL -7 DAY)
#9

The simulator still seems to come back with an error (“near SUM: syntax error”). I don’t know if this is the console not understanding SUM or something else in the line of code?