#610 ZQuery on SQlite database: filter on calculated field issue

[john landmesser]

Ticket 600 only solved filtering values as '00:06:20'

Try attached sample app with value '00:06:21' that is not working. Very strange issue !

All system informations in ticket 600 keep being valid.

See sample app from my google drive:

https://drive.google.com/file/d/1Bvxr5A3XQkg2V3wdpLP20OY-FsbbOWWM/view?usp=sharing

Actual filter code does not take into account seconds ?

Tested with Zeoslib trunk Revision 8236.

[marsupilami79]

Hello John,

the problem is in the way you filter:

calc_Zeit_pro_km='00:06:20'

Effectively this compares calc_Zeit_pro_km to 00:06:20.000000. To get something that works, use a filter with a range, something like this:

calc_Zeit_pro_km>='00:06:19.5' and calc_Zeit_pro_km<'00:06:20.5'

Please let me know if this solves your problem.

Best regards,

Jan

[john landmesser]

Hello Jan,

that works if i use
"calc_Zeit_pro_km>='00:06:19.999' and calc_Zeit_pro_km<'00:06:20.111'"

I have to keep that in mind :-(

I got a better solution from sqlite forum that works :

Calculate a value that can be filtered without any tricks:

SELECT TIME((
unixepoch('00:17:35') -- convert your query to seconds

• unixepoch ('00:00:00') -- remove basis to give absolute value
  ) / 7.0, -- perform division, note decimal to force float (unneeded but probably what you are expecting)
  'unixepoch') -- ensure interpreted as seconds not Julian day
  ```

Do note that times must be rigidly in HH:MM or HH:MM:SS format with a leading zero where needed.

Your initial query therefore would be:

SELECT TIME((unixepoch('00:11:14')-unixepoch('00:00:00'))/1.7,'unixepoch');

In my database i have a trigger after update like this:

CREATE TRIGGER Zeit_pro_km_After_update
AFTER UPDATE
ON laufdaten
FOR EACH ROW
BEGIN
UPDATE laufdaten
SET Zeit_pro_km = TIME( (strftime('%s', Zeit_gelaufen) - strftime('%s', '00:00:00') ) / km_gelaufen, 'unixepoch');
END;

This way the calculated field can be easy filtered :-)

I will have a look now if lazarus Tsqlite3connection has also trouble on filter a calculated field.

Should we close this issue?

[marsupilami79]

Closing the ticket ;)

[dwb]

The unit test for this ticket fails; see https://sourceforge.net/p/zeoslib/testsuite/9/