I have a MariaDb (MySql compatible) that was created using TMS Data Modeler which has a DateTime field in it.
The data was added using the delphi now function (from TMSWebCore) and looks like it was stored as UTC time and not my local time (UTC+8). When I try to retrieve the data it comes back as a floting point number. This is likely because I got the value from Aurelius as a TDateTime, then converted it to a number for sending as a JSON value. Should this be sent as a string or is there another way to transmit the value through JSON while retaining it's meaning (as a TDateTime).
The date format error is on the web browser. I'm using a Chrome compatible browser and set the locale to AU, but it displays as yyyy/mm/dd instead of dd/mm/yyyy.
Does anyone have any suggestions on how to fix any of this ?
Hi Andrews, I got a little bit confused by your statements because first you mentioned Web Core, then later you mentioned Aurelius, and for now, Aurelius is not supported in Web Core.
Could you please provide the exact code you used to save your data, then to retrieve your data, and clarify if this is an automatic CRUD endpoint or a service operation?
Aurelius is on the server side and is used like this
lLogList := lORM.FindRawLog.OrderBy(Linq[Dic.RawLog.DateAdded.PropName], false).Take(num).List;
for var i:=0 to lLogList.Count-1 do
begin
var j := TJsonObject.Create;
json.AddElement(j);
var e := lLogList[i];
j.AddPair('id', TJsonNumber.Create(e.RawLogId));
j.AddPair('correlationid', e.CorrelationId);
j.AddPair('dateadded', DateTimeToStr(e.DateAdded.Value)); // <- DateAdded
j.AddPair('direction', e.Direction);
j.AddPair('region', e.Region);
j.AddPair('data', e.Headers.AsString);
j.AddPair('data', e.Data.AsString);
end;
I originally sent the date as a TJsonNumber which was where I was having the problems. I changed to a string as a temporary solution. DateAdded is a DateTime type in Aurelius.
On the client side I have
procedure TfrmDebug.DoResponse(Sender: TObject; ARequest: TJSXMLHttpRequestRecord; AResponse: string);
var
js: TJson;
jo: TJsonObject;
ja: TJsonArray;
i,j: integer;
s: string;
r: real;
dt: TDateTime;
begin
if aRequest.req.Status = 200 then
begin
js := TJson.Create;
ja := js.Parse(aResponse) as TJsonArray;
for i:=0 to ja.Count-1 do
begin
jo := ja.Items[i] as TJsonObject;
DebugMemo.Lines.Add('id: ' + jo.GetJsonValue('id'));
DebugMemo.Lines.Add('correlationid: ' + jo.GetJsonValue('correlationid'));
s := jo.GetJsonValue('dateadded');
// dt := StrToFloat(s);
// s := DateTimeToStr(dt);
DebugMemo.Lines.Add('dateadded: ' + s);
DebugMemo.Lines.Add('direction: ' + jo.GetJsonValue('direction'));
DebugMemo.Lines.Add('region: ' + jo.GetJsonValue('region'));
DebugMemo.Lines.Add('data: ' + jo.GetJsonValue('data'));
end;
js.Free;
jo.Free;
end
else
begin
Showmessage('Error');
end;
end;
The commented lies were how I was trying to convert the TJsonNumber to a TDateTime.
So I suppose it's working now? That should work since you are controlling sending and receiving date at low level. But for better compatibility, you should format the date/time as a ISO 8601 format. You already have helper functions for in unit Bcl.Utils, methods TBclUtils.ISOToDateTime and TBclUtils.DateTimeFromISO.
I converted to ISO 8601 as you suggested (I think thats the better solution) but I seem to have an error on the web side when I try to convert back to a TDateTime using the TBclUtils.ISOToDateTime.
It raises this exception
ERROR
Value 2021-09-01T09:05:12+08:00 is not a valid datetime | fMessage::Value 2021-09-01T09:05:12+08:00 is not a valid datetime FJSError::Error: Value 2021-09-01T09:05:12+08:00 is not a valid datetime fHelpContext::0
at http://localhost:8000/UI/UI.js [259:48]
Which I dont understand as I folled through the BCLUtils code and it all looks like it should be valid. The string passed in to the ISOToDateTime function was "2021-09-01T09:05:12+08:00" and there didn't seem to be any extra characters in there that shouldn't have been there.
I did generate the ISO date string myself based on the wiki link you sent and it does match the code, but there must be a subtle error in there somewhere that I have not been able to spot.
By default, ISOToDateTime doesn't accept time zones. There is an overload method that you can pass the TTimeZoneMode, which you specify how time zone must be treated:
class function ISOToDateTime(const Value: string; TimeZoneMode: TTimeZoneMode): TDateTime; overload;
TTimeZoneMode = (Error, Ignore, AsUTC, AsLocal);
Default is Error, which means if the date has timezone information, a error will be raised. You can use any of the other options:
Ignore will simply strip out the time zone and ignore it. AsUTC will take time zone into account and return the date/time value for you in UTC time zone. AsLocal will take time zone into account and return the date/time value for you in your local time zone. So, for example:
Value := TBclUtils.ISOToDateTime('2021-09-01T09:05:12+08:00', TTimeZoneMode.AsLocal);
Alternatively, you can change the global TTimeZoneMode setting, which will affect all the ISOToDateTime operations globally:
// Affects all ISOToDateTime calls from now on
DefaultTimeZoneMode := TTimeZoneMode.AsLocal;
Value := TBclUtils.ISOToDateTime('2021-09-01T09:05:12+08:00');
I couldn't find an overloaded version of ISOToDateTime, but there is one for TryISOToDateTime that works.
My final problem is how do I format a string with the date time in AU format ?
I tried DateTimeToString(result, 'dd/mm/yyyy hh:nn:ss', TDateTimeVariable) but it doesn't seem to exist in the WebCore code. Is there an equivilent available ?
You are correct. I just updated the business products and the overloaded version is there.
Could I suggest that the TMS Subscription Manager include more products if possible. I usually catch the WebCore updates, but I had forgotten about the business products which aren't in the subscription manager.
Is there a replacement for the DateTimeToString function. It's in delphi, but doesnt appear to be in the WebCore code.
I just downloaded the current subscription manager and ran it.
If I use DateTimeToStr it comes back as "2021-09-02 21:04:51" but I would like it in "02/09/2021 21:04:51". The DateTimeToString allows me to specify the format. I have not been able to find where the web browser specifies the locale for date formats (my system locale is Australian).
I was sure I had checked it, but upon trying again, it worked, so I must have messed up something. Thanks for the assistance, and thanks Wagner for your help too.
