To store ms TDateTime

Alek · 2014-09-24 07:12:47

How I can store milliseconds TDateTime in any kind datetime property?

ab · 2014-09-24 08:56:46

Why not use a TDateTime property?
It has sub-millisecond resolution.

TTimeLog, on the other side, has second resolution.

Alek · 2014-09-24 11:48:53

Code from SynCommons.pas

procedure TimeToIso8601PChar(P: PUTF8Char; Expanded: boolean; H,M,S: cardinal;
  FirstChar: AnsiChar = 'T'); overload;
// we use Thhmmss format //---------------------> but not THH:MM:SS.SSS
begin
  P^ := FirstChar;
  inc(P);
  pWord(P)^ := TwoDigitLookupW[h];
  inc(P,2);
  if Expanded then begin
    P^ := ':';
    inc(P);
  end;
  pWord(P)^ := TwoDigitLookupW[M];
  inc(P,2);
  if Expanded then begin
    P^ := ':';
    inc(P);
  end;
  pWord(P)^ := TwoDigitLookupW[s];
// -------------->here no code with milliseconds
end;


//-------------->here convert date - it's ok
procedure DateToIso8601PChar(Date: TDateTime; P: PUTF8Char; Expanded: boolean); overload;
// we use YYYYMMDD date format
var Y,M,D: word;
begin
  DecodeDate(Date,Y,M,D);
  DateToIso8601PChar(P,Expanded,Y,M,D);
end;

/// convert a date into 'YYYY-MM-DD' date format
function DateToIso8601Text(Date: TDateTime): RawUTF8;
begin
  SetLength(Result,10);
  DateToIso8601PChar(Date,pointer(Result),True);
end;

procedure TimeToIso8601PChar(Time: TDateTime; P: PUTF8Char; Expanded: boolean;
  FirstChar: AnsiChar = 'T'); overload;
// we use Thhmmss format
var H,M,S,MS: word;
begin
  DecodeTime(Time,H,M,S,MS); //-------------->here we have MS, but don't use it after 
  TimeToIso8601PChar(P,Expanded,H,M,S,FirstChar);
end;

function DateTimeToIso8601(D: TDateTime; Expanded: boolean;
  FirstChar: AnsiChar='T'): RawUTF8;
// we use YYYYMMDDThhmmss format
var tmp: array[0..31] of AnsiChar;
begin
  if Expanded then begin //-------------->here Expanded version TDatetime
    DateToIso8601PChar(D,tmp,true);
    TimeToIso8601PChar(D,@tmp[10],true,FirstChar);
    SetString(result,PAnsiChar(@tmp),19);
  end else begin
    DateToIso8601PChar(D,tmp,false);
    TimeToIso8601PChar(D,@tmp[8],false,FirstChar);
    SetString(result,PAnsiChar(@tmp),15);
  end;
end;

ab · 2014-09-24 12:20:33

So you want JSON storage?

The easiest in your case is to define a plain double property or parameter, which will be stored as floating point in JSON and in the DB.
Then you can use this double value as TDateTime.
And you will get the ms resolution.

edwinsn · 2014-09-28 10:26:49

@ab, since we are on this, may I take this chance to ask, do you have any info on the comparison of performance between storing the TDateTime value as Iso8601 string (by default in mORMot) vs storing as Double?

By performance I mean querying performance, currently I use TDateTime fields (thus stored as Iso8601 strings in the sqlite3 db by default), and my query uses 'ORDER BY myDateField DESC' or something like that. I have noticed something the querying is slow, I'm wondering how many performance I will gain if I change my date time fields from TDateTime(Iso8601) to Double...

PS, I do have an index on the date time fields currently,.

ab · 2014-09-28 18:49:58

Double will definitively be faster than string storage and indexing, and I suspect TTimeLog also slightly faster.

mORMot Open Source

#1 2014-09-24 07:12:47

To store ms TDateTime

#2 2014-09-24 08:56:46

Re: To store ms TDateTime

#3 2014-09-24 11:48:53

Re: To store ms TDateTime

#4 2014-09-24 12:20:33

Re: To store ms TDateTime

#5 2014-09-28 10:26:49

Re: To store ms TDateTime

#6 2014-09-28 18:49:58

Re: To store ms TDateTime

Board footer