Table.Filter by date and time

Post by **seawolf** » 16.10.2008, 21:13

TZTable and TZQuery Filter property dos not works correctly with datetime fields. So first of all I wrote 2 tests:

\test\component\ZTestDataSetGeneric.pas

Add to published interface

procedure TestTimeFilterExpression;
procedure TestDateTimeFilterExpression;

---

{**
Runs a test for time filter expressions.
}
procedure TZGenericTestDbcResultSet.TestTimeFilterExpression;
var
Query: TZQuery;
begin
Query := TZQuery.Create(nil);
try
Query.Connection := Connection;
Query.SQL.Text := 'SELECT * FROM people';

Query.Filter := 'p_begin_work >= "'+TimeToStr(EncodeTime(8,30,0,50))+'"';
Query.Filtered := True;
Query.Open;
CheckEquals(4, Query.RecordCount);
Query.Last;
CheckEquals(EncodeTime(8,30,0,0), Query.FieldByName('p_begin_work').AsDateTime);
Query.Close;
Query.Filter := '(p_begin_work > "'+TimeToStr(EncodeTime(8,0,0,0))+ '") AND (p_end_work < "'+TimeToStr(EncodeTime(18,0,0,0))+'")';
Query.Open;
CheckEquals(2, Query.RecordCount);
Query.Last;
CheckEquals(EncodeTime(8,30,0,0), Query.FieldByName('p_begin_work').AsDateTime);
CheckEquals(EncodeTime(17,30,0,0), Query.FieldByName('p_end_work').AsDateTime);
Query.Close;
finally
Query.Free;
end;
end;

{**
Runs a test for Datetime filter expressions.
}
procedure TZGenericTestDbcResultSet.TestDateTimeFilterExpression;
var
Query: TZQuery;
Date_came,Date_out : TDateTime;
begin
Query := TZQuery.Create(nil);
try
Query.Connection := Connection;
Query.SQL.Text := 'SELECT * FROM cargo';
Date_came := EncodeDateTime(2002,12,19,18,30,0,0);
Query.Filter := 'c_date_came >= "'+DateTimeToStr(Date_came)+'"';
Query.Filtered := True;
Query.Open;
CheckEquals(3, Query.RecordCount);
Query.Last;
Date_came := EncodeDateTime(2002,12,21,10,20,0,0);
CheckEquals(Date_Came, Query.FieldByName('c_date_came').AsDateTime);
Query.Close;
Date_came := EncodeDateTime(2002,12,19,14,30,0,0);
Date_out := EncodeDateTime(2002,12,23,2,0,0,0);
Query.Filter := '(c_date_came > "'+DateTimeToStr(Date_came)+ '") AND (c_date_out < "'+DateTimeToStr(Date_out)+'")';
Query.Open;
CheckEquals(2, Query.RecordCount);
Query.First;
Date_came := EncodeDateTime(2002,12,20,2,0,0,0);
Date_out := EncodeDateTime(2002,12,20,2,0,0,0);
CheckEquals(Date_came, Query.FieldByName('c_date_came').AsDateTime);
CheckEquals(Date_out, Query.FieldByName('c_date_out').AsDateTime);
Query.Close;
finally
Query.Free;
end;
end;

On \src\core\ZTokenizer.pas

TZTokenType = (ttUnknown, ttEOF, ttFloat, ttInteger, ttHexDecimal,
ttNumber, ttSymbol, ttQuoted, ttQuotedIdentifier, ttWord, ttKeyword, ttWhitespace,
ttComment, ttSpecial,ttTime,ttDate,ttDateTime);

On \src\core\ZExprParser.pas , procedure TokenizeExpression

...
ttSymbol:
begin
Temp := Tokens[TokenIndex];
for I := Low(OperatorTokens) to High(OperatorTokens) do
begin
if Temp = OperatorTokens then
begin
TokenType := OperatorCodes;
Break;
end;
end;
end;
ttTime,ttDate,ttDateTime:
begin
TokenType := ttConstant;
DefVarManager.SetAsDateTime(TokenValue, StrToDateTime(Tokens[TokenIndex]));
end;
end;

if TokenType = ttUnknown then
raise TZParseError.Create(Format(SUnknownSymbol, [Tokens[TokenIndex]]));

...

On \src\parsesql\ZGenericSQLToken.pas

function TZGenericSQLQuoteState.NextToken(Stream: TStream;
FirstChar: Char; Tokenizer: TZTokenizer): TZToken;
var
ReadChar: Char;
LastChar: Char;
CountDoublePoint,CountSlash : integer;
isDateTime : TDateTime;
begin
Result.Value := FirstChar;
LastChar := #0;
CountDoublePoint := 0;
CountSlash := 0;
while Stream.Read(ReadChar, 1) > 0 do
begin
if (LastChar = FirstChar) and (ReadChar <> FirstChar) then
begin
Stream.Seek(-1, soFromCurrent);
Break;
end;
if ReadChar = TimeSeparator then
inc(CountDoublePoint);
if ReadChar = DateSeparator then
inc(CountSlash);
Result.Value := Result.Value + ReadChar;
if (LastChar = FirstChar) and (ReadChar = FirstChar) then
LastChar := #0
else LastChar := ReadChar;
end;

if FirstChar = '"' then
Result.TokenType := ttWord
else Result.TokenType := ttQuoted;

// Time constant
if (CountDoublePoint = 2) and (CountSlash = 0) then
begin
try
isDateTime := StrToTime(DecodeString(Result.Value,'"'));
Result.Value := DecodeString(Result.Value,'"');
Result.TokenType := ttTime;
except
end;
end;
// Date constant
if (CountDoublePoint = 0) and (CountSlash = 2) then
begin
try
isDateTime := StrToDate(DecodeString(Result.Value,'"'));
Result.Value := DecodeString(Result.Value,'"');
Result.TokenType := ttDate;
except
end;
end;

// DateTime constant
if (CountDoublePoint = 2) and (CountSlash = 2) then
begin
try
isDateTime := StrToDateTime(DecodeString(Result.Value,'"'));
Result.Value := DecodeString(Result.Value,'"');
Result.TokenType := ttDateTime;
except
end;
end;

end;

Moreover can you please add on \src\plain\ZPlainFirebirdInterbaseConstants.pas

isc_info_db_SQL_dialect = 62;
isc_info_db_read_only = 63;
isc_info_db_size_in_pages = 64;

isc_info_db_SQL_dialect = 62;
isc_info_db_read_only = 63;
isc_info_db_size_in_pages = 64;

frb_info_att_charset = 101;
isc_info_db_class = 102;
isc_info_firebird_version = 103;
isc_info_oldest_transaction = 104;
isc_info_oldest_active = 105;
isc_info_oldest_snapshot = 106;
isc_info_next_transaction = 107;
isc_info_db_provider = 108;
isc_info_active_transactions = 109;
isc_info_active_tran_count = 110;
isc_info_creation_date = 111;
isc_info_db_file_size = 112;

All other tests works correctly to me

Post by **seawolf** » 27.10.2008, 11:32

In order to test if locate method works with DateTime fields I wrote this 2 tests:

Added In ..\test\component\ZTestDataSetGeneric.pas

...
procedure TestTimeLocateExpression;
procedure TestDateTimeLocateExpression;
....

{**
Runs a test for time locate expressions.
}
procedure TZGenericTestDbcResultSet.TestTimeLocateExpression;
var
Query: TZQuery;
begin
Query := TZQuery.Create(nil);
try
Query.Connection := Connection;
Query.SQL.Text := 'SELECT * FROM people';
Query.Open;
CheckEquals(true, Query.Locate('p_begin_work',EncodeTime(8,30,0,0),[]));
CheckEquals(EncodeTime(8,30,0,0), Query.FieldByName('p_begin_work').AsDateTime);
CheckEquals(EncodeTime(17,30,0,0), Query.FieldByName('p_end_work').AsDateTime);
Query.Close;
Query.Open;
CheckEquals(false, Query.Locate('p_begin_work',EncodeTime(8,31,0,0),[]));
Query.Close;
finally
Query.Free;
end;
end;

{**
Runs a test for Datetime locate expressions.
}
procedure TZGenericTestDbcResultSet.TestDateTimeLocateExpression;
var
Query: TZQuery;
begin
Query := TZQuery.Create(nil);
try
Query.Connection := Connection;
Query.SQL.Text := 'SELECT * FROM cargo';
Query.Open;
CheckEquals(true, Query.Locate('c_date_came',EncodeDateTime(2002,12,19,14,0,0,0),[]));
CheckEquals(EncodeDateTime(2002,12,19,14,0,0,0), Query.FieldByName('c_date_came').AsDateTime);
CheckEquals(EncodeDateTime(2002,12,23,0,0,0,0), Query.FieldByName('c_date_out').AsDateTime);
Query.Close;
Query.Open;
CheckEquals(false, Query.Locate('c_date_came',EncodeDateTime(2002,12,19,0,0,0,0),[]));
Query.Close;
finally
Query.Free;
end;
end;

Post by **mdaems** » 31.10.2008, 13:59

Added both pieces of code in SVN rev. 505

Thanks,

Mark

Demek · Post by **Demek** » 19.05.2009, 14:25

Hello!
Has put 505 updating which adds filtration possibility on date and time.
But at program compilation the error jumps out: is not a valid date that it can be

aurallion · Post by **aurallion** » 16.07.2009, 09:13

Hello, i've used Zeos version 6.6.5. But lookslike i got the problem with filtering Datetime field... i have a table which has fields starttime and endtime. assumed this table has a row with values starttime = '7/16/2009 9:00:00' and endtime = '7/16/2009 10:00:00'.

when i set the TZTable filter to: starttime < '7/16/2009 9:30:00' and endtime > '7/16/209 9:30:00'. The result is EMPTY...

with the same filter or query, i try it in the Firebird Maestro, i got one row filtered...

should i change/modify the 6.6.5's code with what seawolf has written above?

i found this problem when i want to create an event scheduler system, which has a start time and end time fields, and i want to see what is the running event now..

or should i use another way?

Post by **seawolf** » 18.07.2009, 21:04

Tomorrow I rewrite a test in order to verify what you wrote

Post by **seawolf** » 22.07.2009, 22:14

Hi, I've added one more test as you suggested

procedure TZGenericTestDbcResultSet.TestDateTimeFilterExpression;
var
Query: TZQuery;
Date_came,Date_out : TDateTime;
begin
Query := TZQuery.Create(nil);
try
Query.Connection := Connection;
Query.SQL.Text := 'SELECT * FROM cargo';
Date_came := EncodeDateTime(2002,12,19,18,30,0,0);
Query.Filter := 'c_date_came >= "'+DateTimeToStr(Date_came)+'"';
Query.Filtered := True;
Query.Open;
CheckEquals(3, Query.RecordCount);
Query.Last;
Date_came := EncodeDateTime(2002,12,21,10,20,0,0);
CheckEquals(Date_Came, Query.FieldByName('c_date_came').AsDateTime);
Query.Close;
Date_came := EncodeDateTime(2002,12,19,14,30,0,0);
Date_out := EncodeDateTime(2002,12,23,2,0,0,0);
Query.Filter := '(c_date_came > "'+DateTimeToStr(Date_came)+ '") AND (c_date_out < "'+DateTimeToStr(Date_out)+'")';
Query.Open;
CheckEquals(2, Query.RecordCount);
Query.First;
Date_came := EncodeDateTime(2002,12,20,2,0,0,0);
Date_out := EncodeDateTime(2002,12,20,2,0,0,0);
CheckEquals(Date_came, Query.FieldByName('c_date_came').AsDateTime);
CheckEquals(Date_out, Query.FieldByName('c_date_out').AsDateTime);
Query.Close;
Date_came := EncodeDateTime(2002,12,21,14,30,0,0);
Date_out := EncodeDateTime(2002,12,25,2,0,0,0);
Query.Filter := '(c_date_came < "'+DateTimeToStr(Date_came)+ '") AND (c_date_out > "'+DateTimeToStr(Date_out)+'")';
Query.Open;
CheckEquals(1, Query.RecordCount);
Query.First;
Date_came := EncodeDateTime(2002,12,21,10,20,0,0);
Date_out := EncodeDateTime(2002,12,26,0,0,0,0);
CheckEquals(Date_came, Query.FieldByName('c_date_came').AsDateTime);
CheckEquals(Date_out, Query.FieldByName('c_date_out').AsDateTime);
Query.Close;
finally
Query.Free;
end;
end;

But I have not error .. so my conclusion is using EncodeDate function before comparing date and time. If you continue having problems after
using that function please post a more detailed sample or a sql dump

Post by **mdaems** » 25.07.2009, 00:00

SVN rev. 679

markus · Post by **markus** » 17.10.2011, 12:54

Hi,
I've been using ZEOS in v 6.6.6 under C++Builder 5.
Recently i've decided to switch to CB2010 and Zeos 7 (alpha).
My Db is running on postgresql 8.3/8.4

In my queries i use quite complex field names:
for example "item_id-unit_id" or "item_id-unit_id-index" - depending from tables i used in query.
I've encountered problem in the second example "item_id-unit_id-index".
Due to changes from rev 505 in NextToken function - this field name is treated as date constant and is stripped of " " - which causes error in query execution.

Changing SQLs is not an option for me - they are just too many of them.
Maybe it should be corrected to check if previous token was ',' or 'select' - it's only situations i can imagine where date/time const can occur. But this on the other hand will not guarantee that field name in DB is without date or time separators (in pgsql filed can have any name as long as it's enclosed in "").

Best regards,
Marek Wrzos

Post by **seawolf** » 17.10.2011, 18:10

At the moment solution is not so simple as you think, because it is parsed every single text and there is no way to know it is a field of a value

markus · Post by **markus** » 17.10.2011, 18:26

You are saying that both Query input (SQL text) and output (result) are parsed by this function?

Shouldn't SQL text be passed to DB engine "as is", except for params parsing?

Post by **mdaems** » 30.10.2011, 21:40

Can't you patch the parser so it only behaves like this when the characters between the '-' characters are (at most 4) numbers. This would make the parsing more correct I believe.
But I must admit, I can't understand how this parsing can change the query text sent to the server. As far as I knew it is only used to derive the data types of the fields resulting from a query.

Mark