Zeoslib Portal

my developing environment: delphi 7 + zeoslib + firebird 2.1 db (dialect=3)

I saved a string '2009-01-01 00:23:32.200' to a timestamp field, it was ok, but when I used
FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz',FieldByName('datetime').AsDateTime)) to load datetime from field, the millisecond was lost, and I just got '2009-01-01 00:23:32.000' .

Is there any way can solve this problem? I really need the value of milliseconds.

Below is the saving clause:
insert into foo (datetime) values ('2009-01-01 00:23.32.200')

Hi, I almost found a solution, but are you executing

insert into foo (datetime) values ('2009-01-01 00:23.32.200')

Data are stored correctly? Did you checked using FlameRobin (o something else)?

Because it looks to me also the insert command does not save correctly data.

In order to read milliseconds you need to change:

src\plain\ZPlainFirebirdInterbaseConstants.pas

TISC_TIMESTAMP = record
timestamp_date: ISC_DATE;
timestamp_time: ISC_TIME;
end;

(previous declaration is not correct)

src\dbc\ZDbcInterbase6Utils.pas

function TZResultSQLDA.GetTimestamp(const Index: Integer): TDateTime;
var
TempDate: TCTimeStructure;
begin
CheckRange(Index);
{$R-}
with FXSQLDA.sqlvar[Index] do
begin
Result := 0;
if (sqlind <> nil) and (sqlind^ = -1) then
Exit;

case (sqltype and not(1)) of
SQL_TIMESTAMP : begin
FPlainDriver.isc_decode_timestamp(PISC_TIMESTAMP(sqldata), @TempDate);
Result := SysUtils.EncodeDate(TempDate.tm_year + 1900,
TempDate.tm_mon + 1, TempDate.tm_mday) + EncodeTime(TempDate.tm_hour,
TempDate.tm_min, TempDate.tm_sec, Word((PISC_TIMESTAMP(sqldata).timestamp_time mod 10000) div 10));
end;
SQL_TYPE_DATE : begin
FPlainDriver.isc_decode_sql_date(PISC_DATE(sqldata), @TempDate);
Result := SysUtils.EncodeDate(Word(TempDate.tm_year + 1900),
Word(TempDate.tm_mon + 1), Word(TempDate.tm_mday));
end;
SQL_TYPE_TIME : begin
FPlainDriver.isc_decode_sql_time(PISC_TIME(sqldata), @TempDate);
Result := SysUtils.EncodeTime(Word(TempDate.tm_hour), Word(TempDate.tm_min),
Word(TempDate.tm_sec), Word((PISC_TIME(sqldata)^ mod 10000) div 10));
end;
else
Result := Trunc(GetDouble(Index));
end;
end;
{$IFOPT D+}
{$R+}
{$ENDIF}
end;

Anyway, as I supposed, Zeoslib at the moment don't write millisec to the DB, but I need more time to fix that problem

In order to write msec it is necessary to change, on unit ZDbcInterbase6Utils.pas:

procedure TZParamsSQLDA.UpdateDateTime(const Index: Integer;
Value: TDateTime);
var
y, m, d: word;
hr, min, sec, msec: word;
SQLCode: SmallInt;
TmpDate: TCTimeStructure;
TmpTime: Cardinal;
begin
CheckRange(Index);
{$R-}
with FXSQLDA.sqlvar[Index] do
begin
DecodeDate(Value, y, m, d);
DecodeTime(Value, hr, min, sec, msec);
TmpDate.tm_year := y - 1900;
TmpDate.tm_mon := m - 1;
TmpDate.tm_mday := d;
TmpDate.tm_hour := hr;
TmpDate.tm_min := min;
TmpDate.tm_sec := sec;
TmpDate.tm_wday := 0;
TmpDate.tm_yday := 0;
TmpDate.tm_isdst := 0;
if (sqlind <> nil) and (sqlind^ = -1) then
Exit;
SQLCode := (sqltype and not(1));

case SQLCode of
SQL_TYPE_DATE : FPlainDriver.isc_encode_sql_date(@TmpDate, PISC_DATE(sqldata));
SQL_TYPE_TIME : begin
FPlainDriver.isc_encode_sql_time(@TmpDate, PISC_TIME(sqldata));
PISC_TIME(sqldata)^ := PISC_TIME(sqldata)^ + msec*10;
end;
SQL_TIMESTAMP :
begin
FPlainDriver.isc_encode_timestamp(@TmpDate,PISC_TIMESTAMP(sqldata));
PISC_TIMESTAMP(sqldata).timestamp_time :=PISC_TIMESTAMP(sqldata).timestamp_time + msec*10;
end;

else
raise EZIBConvertError.Create(SInvalidState);
end;
if (sqlind <> nil) then
sqlind^ := 0; // not null
end;
{$IFOPT D+}
{$R+}
{$ENDIF}
end;

Seawolf,

Current tests don't fail on this change. So I commit. (SVN rev 639)
Do you think it's reasonable to write a test case for that in the test suite? (Make sure it's fb only if you do.)

Mark

You're right and I've already done it in order to test that change.
Unfortunately I wrote this test on a different pc. Within 24 hour i'll post it.

I added this test to ZTestDbcInterbase.pas

(you have to add DateUtils,Math on the uses)

procedure TZTestDbcInterbaseCase.TestMsec;
var
Statement: IZStatement;
ResultSet: IZResultSet;
ThisTime : TDateTime;
oldTimeFormat: string;
begin
Statement := Connection.CreateStatement;
CheckNotNull(Statement);
Statement.SetResultSetType(rtScrollInsensitive);
Statement.SetResultSetConcurrency(rcUpdatable);
Statement.ExecuteUpdate('delete from DATE_VALUES where D_ID=4');
ResultSet := Statement.ExecuteQuery('select D_ID, D_DATE, D_TIME, D_DATETIME, D_TIMESTAMP from DATE_VALUES');
CheckNotNull(ResultSet);
OldTimeFormat := LongTimeFormat;
LongTimeFormat := 'hh:mm:ss.zzz';
ThisTime := Now;
ResultSet.MoveToInsertRow;
ResultSet.UpdateInt(1, 4);
ResultSet.UpdateDate(2,ThisTime);
ResultSet.UpdateTime(3,ThisTime);
ResultSet.UpdateTimestamp(4,ThisTime);
ResultSet.UpdateTimestamp(5,ThisTime);
ResultSet.InsertRow;
ResultSet.Last;
Check(ResultSet.GetInt(1) <> 0);
CheckEquals(Trunc(ThisTime), ResultSet.GetDate(2));
CheckEquals(RoundTo(Frac(ThisTime),-11), RoundTo(ResultSet.GetTime(3),-11));
CheckEquals(ThisTime, ResultSet.GetTimeStamp(4));
CheckEquals(ThisTime, ResultSet.GetTimeStamp(5));
ResultSet.DeleteRow;
LongTimeFormat := OldTimeFormat;
ResultSet.Close;
Statement.Close;
end;

Thanks all your reply, I have solved this problem.

I used the FB SQL grammar, here is the clause:
select cast(datetime as char(24)) as datetime from warninglog where ...

And then, after I opened the query, I fetched the datatime as a string with this sub-clause:
LeftStr(fdbquery.FieldByName('datetime').AsString,23)

Because the FB always return 4 char as millisecond, such as xxx.0230, and the last char is always a '0'.