TimestampStrToDateTime error fix

niek · Post by **niek** » 12.11.2008, 12:29

Some dates of form 2008-1-1 seem not to work good with
function TimestampStrToDateTime in ZSysUtils. Therefore i rewrote the function to accept a more date format's.

Good luck with it.
gr,
Niek

Code: Select all

{**
  Converts Timestamp String to TDateTime
  @param Value a timestamp string.
  @return a decoded TDateTime value.
}
function TimestampStrToDateTime(const Value: string): TDateTime;
var
  Year, Month, Day, Hour, Min, Sec: Integer;
  StrLength, StrPos, StrPosPrev: Integer;
  //
  function CharMatch( matchchars: string ): boolean;
  // probeer zoveel mogelijk char te matchen, p op 1 na laatste match
  begin
    StrPosPrev:= StrPos;
    Result:= false;
    while StrPos<=StrLength do
      if pos(Value[StrPos],matchchars)>0 then begin inc(StrPos); Result:= true; end
      else break;
  end;
begin
  Result := 0;
  StrPos:= 1;
  StrLength := Length(Value);

  if not CharMatch('1234567890') then exit;                         // year
  Year := StrToIntDef(Copy(Value, StrPosPrev, StrPos-StrPosPrev), 0);
  if not CharMatch('-/\') then exit;
  if not CharMatch('1234567890') then exit;                         // month
  Month:= StrToIntDef(Copy(Value, StrPosPrev, StrPos-StrPosPrev), 0);
  if not CharMatch('-/\') then exit;
  if not CharMatch('1234567890') then exit;                         // day
  Day:= StrToIntDef(Copy(Value, StrPosPrev, StrPos-StrPosPrev), 0);
  Result := EncodeDate(Year, Month, Day);
  //
  if not CharMatch(' ') then exit;
  if not CharMatch('1234567890') then exit;                         // hour
  Hour := StrToIntDef(Copy(Value, StrPosPrev, StrPos-StrPosPrev), 0);
  if not CharMatch('-/\') then exit;
  if not CharMatch('1234567890') then exit;                         // minute
  Min:= StrToIntDef(Copy(Value, StrPosPrev, StrPos-StrPosPrev), 0);
  if not CharMatch('-/\') then exit;
  if not CharMatch('1234567890') then exit;                         // second
  Sec:= StrToIntDef(Copy(Value, StrPosPrev, StrPos-StrPosPrev), 0);
  Result := REsult + EncodeTime(Hour, Min, Sec,0);

end;

Post by **mdaems** » 12.11.2008, 20:55

This patch doesn't affect the test suite (on mysql). And it looks easier to read. So I commit it to testing branch as a trial for 7.X. (SVN rev 518) Of course, if we encounter trouble we'll have to look at it again.

Mark

niek · Post by **niek** » 13.11.2008, 01:02

Hello Mark,

I rewrote the function, now 2digit year and the time portion works fine. This function can probably replace some functions in the library, like AnsiSQLDateToDateTime for instance.
It is more flexible and as faster as IsMatch('????-??-??*', Temp) & AnsiSQLDateToDateTime together!

Datetime format: [year[-month[-day]]] [hour:[:minute:[second]]]

gr,
Niek

unit ZSysUtils:

Code: Select all

function TimestampStrToDateTime(const Value: string): TDateTime;
var
  Year, Month, Day, Hour, Min, Sec, MSec, E: Integer;
  StrLength, StrPos, StrPosPrev: Integer;
  //
  function CharMatch1( matchchars: TCharSet ): boolean;
  begin
    StrPosPrev:= StrPos;
    while StrPos<=StrLength do
      if Value[StrPos] in matchchars then inc(StrPos)
      else break;
    Result:= StrPosPrev<>StrPos;
  end;
begin

  Result := 0;
  StrPos:= 1;
  StrLength := Length(Value);

  if not CharMatch1(['0','1','2','3','4','5','6','7','8','9']) then exit;                         // year
  Val(Copy(Value, StrPosPrev, StrPos-StrPosPrev), Year, E);
  if (StrPos>StrLength) or (Value[StrPos]<>':') then try                         // it is a date..
    if (StrPos-StrPosPrev)<=2 then if Year<50 then inc(Year,2000) else inc(Year,1900);
    if CharMatch1(['-','/','\']) and CharMatch1(['0','1','2','3','4','5','6','7','8','9']) then   // month
      Val(Copy(Value, StrPosPrev, StrPos-StrPosPrev), Month, E)
    else Month:= 1;
    if CharMatch1(['-','/','\']) and CharMatch1(['0','1','2','3','4','5','6','7','8','9']) then   // day
      Val(Copy(Value, StrPosPrev, StrPos-StrPosPrev), Day, E)
    else Day:= 1;
    // day
    Result := EncodeDate(Year, Month, Day);
  except
  end
  else
    Result:= 0;
  //
  if (StrPos<=StrLength) then try
    if (Value[StrPos]=':') then Hour:= Year
    else begin
      if not CharMatch1([' ','T']) then exit;
      if not CharMatch1(['0','1','2','3','4','5','6','7','8','9']) then exit;       // hour
      Hour := StrToIntDef(Copy(Value, StrPosPrev, StrPos-StrPosPrev), 0);
    end;
    if CharMatch1([':']) and CharMatch1(['0','1','2','3','4','5','6','7','8','9']) then // minute
      Val(Copy(Value, StrPosPrev, StrPos-StrPosPrev),Min,E)
    else Min:= 0;
    if CharMatch1([':']) and CharMatch1(['0','1','2','3','4','5','6','7','8','9']) then // second
      Val(Copy(Value, StrPosPrev, StrPos-StrPosPrev),Sec,E)
    else Sec:= 0;
    if CharMatch1([':','.']) and CharMatch1(['0','1','2','3','4','5','6','7','8','9']) then // milli secord support
      Val(Copy(Value, StrPosPrev, StrPos-StrPosPrev),MSec,E)
    else
      MSec:= 0;
    if Result >= 0 then
      Result := Result + EncodeTime(Hour, Min, Sec, MSec)
    else Result := Result - EncodeTime(Hour, Min, Sec, MSec)
  except
  end;

end;

I think you can replace the code in this unit aswell (also other db resultsets or abstract class), i don't know why we need the trunc and frac functions in the old situation.

Unit ZDbcSqLiteResultSet

Code: Select all

{**
  Gets the value of the designated column in the current row
  of this <code>ResultSet</code> object as
  a <code>java.sql.Date</code> object in the Java programming language.

  @param columnIndex the first column is 1, the second is 2, ...
  @return the column value; if the value is SQL <code>NULL</code>, the
    value returned is <code>null</code>
}
function TZSQLiteResultSet.GetDate(ColumnIndex: Integer): TDateTime;
var
  Value: string;
begin
{$IFNDEF DISABLE_CHECKING}
  CheckColumnConvertion(ColumnIndex, stDate);
{$ENDIF}
  Value := GetPChar(ColumnIndex);
  {if IsMatch('????-??-??*', Value) then
    Result := Trunc(AnsiSQLDateToDateTime(Value))
  else} Result := Trunc(TimestampStrToDateTime(Value));
  LastWasNull := Result = 0;
end;

{**
  Gets the value of the designated column in the current row
  of this <code>ResultSet</code> object as
  a <code>java.sql.Time</code> object in the Java programming language.

  @param columnIndex the first column is 1, the second is 2, ...
  @return the column value; if the value is SQL <code>NULL</code>, the
    value returned is <code>null</code>
}
function TZSQLiteResultSet.GetTime(ColumnIndex: Integer): TDateTime;
var
  Value: string;
begin
{$IFNDEF DISABLE_CHECKING}
  CheckColumnConvertion(ColumnIndex, stTime);
{$ENDIF}
  Value := GetPChar(ColumnIndex);
  {if IsMatch('*??:??:??*', Value) then
    Result := Frac(AnsiSQLDateToDateTime(Value))
  else} Result := Frac(TimestampStrToDateTime(Value));
end;

{**
  Gets the value of the designated column in the current row
  of this <code>ResultSet</code> object as
  a <code>java.sql.Timestamp</code> object in the Java programming language.

  @param columnIndex the first column is 1, the second is 2, ...
  @return the column value; if the value is SQL <code>NULL</code>, the
  value returned is <code>null</code>
  @exception SQLException if a database access error occurs
}
function TZSQLiteResultSet.GetTimestamp(ColumnIndex: Integer): TDateTime;
var
  Temp: string;
begin
{$IFNDEF DISABLE_CHECKING}
  CheckColumnConvertion(ColumnIndex, stTimestamp);
{$ENDIF}
  Temp := GetPChar(ColumnIndex);
  {if IsMatch('????-??-??*', Temp) then
    Result := AnsiSQLDateToDateTime(Temp)
  else} Result := TimestampStrToDateTime(Temp);
  LastWasNull := Result = 0;
end;