On my server I have a DBISam table with a TimeStamp field. If I read the
table in my EWB appliation, I am having trouble with these fields? They give
me another date in EWB. How should I handle this? Must I split the fields in
my table in a Date field and a Time field?

Ronald

Ronald,

<< On my server I have a DBISam table with a TimeStamp field. If I read the
table in my EWB appliation, I am having trouble with these fields? They give
me another date in EWB. How should I handle this? Must I split the fields in
my table in a Date field and a Time field? >>

What "other date" are you seeing ?  Please detail what the source data looks
like, and what you're seeing in the EWB application.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

The contents of the field "starttijd" (datatype: TimeStamp) in the DBISam
table is: 13-2-2012 03:15:00.  As shown in the Database System Utility.

In EWB I open the table. A webbroker .exe sends EWB the contents in JSON of
the table.

The JSON that is sent is correct:

{   "rows": [
{ "no": "1", "ruimteno": "2", "omschrijving": null, "starttijd": "13-2-2012
03:15:00", "eindtijd": "13-2-2012 04:45:00", "eigenaar": null,
"verantwoordelijke": null },
{ "no": "2", "ruimteno": "1", "omschrijving": null, "starttijd": "13-2-2012
03:30:00", "eindtijd": "13-2-2012 05:00:00", "eigenaar": null,
"verantwoordelijke": null }
] }

In the TDataSet the Dataset Column of the field "starttijd" is dtDateTime.

Then I load the dataset.
If I do ShowMessage(tbRP.Columns['starttijd'].AsString) I see:1-1970 0:00

What am I doing wrong?

Greetings,
Ronald



"Tim Young [Elevate Software]"  schreef in bericht
news:39B11EF8-B54C-4884-8387-F358D9967872@news.elevatesoft.com...

Ronald,

<< On my server I have a DBISam table with a TimeStamp field. If I read the
table in my EWB appliation, I am having trouble with these fields? They give
me another date in EWB. How should I handle this? Must I split the fields in
my table in a Date field and a Time field? >>

What "other date" are you seeing ?  Please detail what the source data looks
like, and what you're seeing in the EWB application.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com

Ronald,

Actually JSON is not correct - EWB date/time types are integers
indicating milliseconds since Jan 1, 1970
(http://www.elevatesoft.com/manual?action=viewtopic&id=ewb1&topic=Types).

This JSON contains a string representation of the timestamp so you
should either modify the back-end to convert it to milliseconds or
modify the tdataset to store it as string (and then optionally convert
it yourself).

Raul



On 9/11/2013 2:44 PM, Ronald wrote:
> DBISam table is: 13-2-2012 03:15:00.  As shown in the Database System
> Utility.
>
> The JSON that is sent is correct:
>
> {   "rows": [
> { "no": "1", "ruimteno": "2", "omschrijving": null, "starttijd":
> "13-2-2012 03:15:00", "eindtijd": "13-2-2012 04:45:00", "eigenaar":
> null, "verantwoordelijke": null },
> { "no": "2", "ruimteno": "1", "omschrijving": null, "starttijd":
> "13-2-2012 03:30:00", "eindtijd": "13-2-2012 05:00:00", "eigenaar":
> null, "verantwoordelijke": null }
> ] }
>
> In the TDataSet the Dataset Column of the field "starttijd" is dtDateTime.
>

Did you create your own JSON file?

Dates and Date time are to be in number of milliseconds since 1970/01/01 - see documentation and are sent UTC.

http://www.elevatesoft.com/manual?action=viewtopic&id=ewb1&topic=JSON_Reference


Walter

I see. Strange, I use this to build the JSON:

else if (Fields[I].DataType in
[ftString,ftWideString,ftFixedChar,ftMemo,ftTime,ftDateTime]) then
                    TempString:=TempString+'"'+EscapeSpecialChars(Fields[I].AsString)+'"';

If I recall it right, I copied this code from a sample by Tim. I must be
mistaken, I presume.
Thanks, I will try to correct the "BuildRows" function.


"Walter Matte" schreef in bericht
news:DC8C28F2-87A0-4994-86A4-C983DA8ABF8A@news.elevatesoft.com...

Did you create your own JSON file?

Dates and Date time are to be in number of milliseconds since 1970/01/01 -
see documentation and are sent UTC.

http://www.elevatesoft.com/manual?action=viewtopic&id=ewb1&topic=JSON_Reference


Walter

Should I change the TimeStamp field into an Integer field in the DBISam
table?

"Walter Matte" schreef in bericht
news:DC8C28F2-87A0-4994-86A4-C983DA8ABF8A@news.elevatesoft.com...

Did you create your own JSON file?

Dates and Date time are to be in number of milliseconds since 1970/01/01 -
see documentation and are sent UTC.

http://www.elevatesoft.com/manual?action=viewtopic&id=ewb1&topic=JSON_Reference


Walter

On 9/11/2013 4:19 PM, Ronald wrote:
> I see. Strange, I use this to build the JSON:
>
> else if (Fields[I].DataType in
> [ftString,ftWideString,ftFixedChar,ftMemo,ftTime,ftDateTime]) then
>
> TempString:=TempString+'"'+EscapeSpecialChars(Fields[I].AsString)+'"';
>
> If I recall it right, I copied this code from a sample by Tim. I must be
> mistaken, I presume.
> Thanks, I will try to correct the "BuildRows" function.
>

It might have been though i think it was just Tim showing a sample on
how to get data to display.

This will work OK as long as your EWB dataset field is defined as string
- if you just need it for display purposes everything should work ok.

If you need an actual timestamp then you need to have your broker
convert the value to integer.

DateUtils has a DateTimeToUnix function that is likely easiest to use
(and since this in int value the json no longer needs quotes around the
actual value).

Raul

Here is a routine I wrote - DatasetToJSON and below the DateTeim to Unix with UTC conversion....


function TdmDBISAM.DatasetToJSON(dsData: TDataSet): string;
var
 i : integer;
 sl : TStringList;
 sRow, sField, sValue : string;
begin
 dsData.First;
 if dsData.Eof then
 begin
   result := '{ "rows": [  ] }';
   exit;
 end;
   
 sl := TStringList.Create;

 sl.Add('{ "rows": [');

 sRow := '';
 while not dsData.EOF do
 begin
   sRow := sRow + '{';
   
   for i := 0 to dsData.FieldCount - 1 do
   begin
     sField := dsData.Fields[i].FieldName;
     sValue := dsData.Fields[i].AsString;
     
     if (dsData.Fields[i].IsNull) then
     begin
       sRow := sRow + Format('"%s": %s',[sField, 'Null'])
     end
     else if (dsData.Fields[i].DataType = ftDateTime) or
        (dsData.Fields[i].DataType = ftTime) or
        (dsData.Fields[i].DataType = ftDate) or
        (dsData.Fields[i].DataType = ftTimeStamp) then
     begin   
       sValue := IntToStr(DateTimeToUnixTimeFAST(dsData.Fields[i].AsDateTime));
       sRow := sRow + Format('"%s": %s',[sField, sValue]);
     end
     else if (dsData.Fields[i].DataType = ftString) or    
             (dsData.Fields[i].DataType = ftMemo) then   
     begin
       sRow := sRow + Format('"%s": "%s"',[sField, sValue]);
     end
     else  
     begin
       sRow := sRow + Format('"%s": %s',[sField, sValue]);
     end;
         
     if (i <> (dsData.FieldCount - 1)) then
       sRow := sRow + ','
     else
       sRow := sRow + '}';
   end;
   sl.Add(sRow);
   sRow := ',';
   
   dsData.Next;
 end;  
            
 sl.Add('] }');

//  sl.SaveToFile('data' + '.json');
 
 result := sl.Text;
 sl.Free;

end;



function LocalToUTC(locDT : TDateTime) : TDateTime;
begin
 result := TTimeZone.Local.ToUniversalTime(locDT);
end;

function UTCToLocal(utcDT : TDateTime) : TDateTime;
begin
 result := TTimeZone.Local.ToLocalTime(utcDT);
end;


function UNIXTimeToDateTimeFAST(UnixTime: Int64): TDateTime;
const
 MsecPerDay : int64 = 86400000;
 DayDiff    : int64 = 25569;
var  
 DT         : TDateTime;
begin                  
 DT := (UnixTime / MsecPerDay) + DayDiff;       
 result := UTCToLocal(DT);
end;      

function DateTimeToUNIXTimeFAST(DelphiTime : TDateTime): Int64;
const
 MsecPerDay : int64 = 86400000;
 DayDiff    : int64 = 25569;
var  
 DT         : TDateTime;
 X          : extended;
begin                                        
 DT := LocalToUTC(DelphiTime);
 x := (DT - DayDiff) * MsecPerDay;
 result := round(X);
end;


Walter

Walter and Raul,

Thanks a lot for your answers. I understand it much better now.I will
replace my function with the code below and test it with my application.

Greetings,
Ronald

"Walter Matte" schreef in bericht
news:6817FADD-FCCD-4FB0-BC03-17FA261BA16D@news.elevatesoft.com...

Here is a routine I wrote - DatasetToJSON and below the DateTeim to Unix
with UTC conversion....


function TdmDBISAM.DatasetToJSON(dsData: TDataSet): string;
var
 i : integer;
 sl : TStringList;
 sRow, sField, sValue : string;
begin
 dsData.First;
 if dsData.Eof then
 begin
   result := '{ "rows": [  ] }';
   exit;
 end;

 sl := TStringList.Create;

 sl.Add('{ "rows": [');

 sRow := '';
 while not dsData.EOF do
 begin
   sRow := sRow + '{';

   for i := 0 to dsData.FieldCount - 1 do
   begin
     sField := dsData.Fields[i].FieldName;
     sValue := dsData.Fields[i].AsString;

     if (dsData.Fields[i].IsNull) then
     begin
       sRow := sRow + Format('"%s": %s',[sField, 'Null'])
     end
     else if (dsData.Fields[i].DataType = ftDateTime) or
        (dsData.Fields[i].DataType = ftTime) or
        (dsData.Fields[i].DataType = ftDate) or
        (dsData.Fields[i].DataType = ftTimeStamp) then
     begin
       sValue :=
IntToStr(DateTimeToUnixTimeFAST(dsData.Fields[i].AsDateTime));
       sRow := sRow + Format('"%s": %s',[sField, sValue]);
     end
     else if (dsData.Fields[i].DataType = ftString) or
             (dsData.Fields[i].DataType = ftMemo) then
     begin
       sRow := sRow + Format('"%s": "%s"',[sField, sValue]);
     end
     else
     begin
       sRow := sRow + Format('"%s": %s',[sField, sValue]);
     end;

     if (i <> (dsData.FieldCount - 1)) then
       sRow := sRow + ','
     else
       sRow := sRow + '}';
   end;
   sl.Add(sRow);
   sRow := ',';

   dsData.Next;
 end;

 sl.Add('] }');

//  sl.SaveToFile('data' + '.json');

 result := sl.Text;
 sl.Free;

end;



function LocalToUTC(locDT : TDateTime) : TDateTime;
begin
 result := TTimeZone.Local.ToUniversalTime(locDT);
end;

function UTCToLocal(utcDT : TDateTime) : TDateTime;
begin
 result := TTimeZone.Local.ToLocalTime(utcDT);
end;


function UNIXTimeToDateTimeFAST(UnixTime: Int64): TDateTime;
const
 MsecPerDay : int64 = 86400000;
 DayDiff    : int64 = 25569;
var
 DT         : TDateTime;
begin
 DT := (UnixTime / MsecPerDay) + DayDiff;
 result := UTCToLocal(DT);
end;

function DateTimeToUNIXTimeFAST(DelphiTime : TDateTime): Int64;
const
 MsecPerDay : int64 = 86400000;
 DayDiff    : int64 = 25569;
var
 DT         : TDateTime;
 X          : extended;
begin
 DT := LocalToUTC(DelphiTime);
 x := (DT - DayDiff) * MsecPerDay;
 result := round(X);
end;


Walter