queries don't seem to support comparison operators ?

[darzok0914]

Hi, I'm trying to get my head around the fact that comparison operators don't seem to work on queries, I'm pretty sure that this is me being silly but I can't figure it out some here I am.

this works

entries:get({ where = { timeStamp = os.time() } })

this doesn't work

entries:get({ where = { timeStamp < os.time() } })

[kkharji]

🤔 the syntax is not yet supported I guess.

If I'm not mistaken you can try something like { where = { timeStamp = "<" .. os.time() } }

[darzok0914]

if I try

entries:get({ where = { timeStamp "<" os.time() } })

I have the same issue, which is lua complains that timeStamp cannot be found and I can't run the script.

however I tried this

entries:get({ where = { "timeStamp" < "os.time()" } })

which seems to be doing something but the result is not at all relevant so I guess we can't do this neither.

surely there is a way to have a query where I can use comparison operators, I'm trying to look to some code from other plugins that use sqlite.lua and it seems like some of theme are injecting strings with the full sqlite queries to get results but I'm yet to decipher all of it.

[kkharji]

Yes, injected string, that's why you need to set the value to os.time() value while at the same time preappending < so that the interface gets "< val".

Previously I tried to play around with sugar syntax unfortunately lua doesn't allow room to do that. The best we could do is something like that age = (strftime("%s", "now") - strftime("%s", "timestamp")) * 24 * 60, which all result in a string.

you may do something like where = { timeStamp = strftime("%s", "now) < strftime("%s", "timeStamp")} .

you need first to local strftime = sqlite.lib.strftime

[darzok0914]

I tried this and it always returns an empty table.

res = entries:get({ where = { timeStamp = strftime("%s", "timeStamp") < strftime("%s", "now")} })

I tried different things as well such as

res = entries:get({ where = { timeStamp = strftime("%s", "timeStamp") .. " < " .. strftime("%s", "now")} })

I also changed the type of timeStamp in my table, previously I was using "timestamp" as a type and I change to "date" but it didn't make any difference.

I also tried to change the comparison operator to see and it always returns an empty table.

how would the function strftime get access to the field timeStamp of my table? is it some magic under the hood?

[kkharji]

how would the function strftime get access to the field timeStamp of my table? is it some magic under the hood?

It doesn't.

All strftime fn is return a string. it is syntax magic you can say.