Support for milliseconds in TDateTime

Chaa · 2016-04-27 08:22:48

I need to support for store milliseconds in database for TDateTime column.

There are two ways of realization for this functionality:

1. Compiler directive, {$define USEMILLISECONDS}

2. Property TSQLPropInfoRTTIDateTime.Milliseconds that controls GetValue/SetValue behavior.
Also, we need to change UTF8CompareISO8601 implementation.

Which variant, from your perspective, is preferable?

P.S.

There is already a function DateTimeMSToString that supports milliseconds,
and I need to add supports for ss.sss to Iso8601ToDateTimePUTF8CharVar:

 procedure Iso8601ToDateTimePUTF8CharVar(P: PUTF8Char; L: integer; var result: TDateTime);
 var i: integer;
     B: cardinal;
-    Y,M,D, H,MI,SS: cardinal;
+    Y,M,D, H,MI,SS,SSS: cardinal;
-// expect 'YYYYMMDDThhmmss' format but handle also 'YYYY-MM-DDThh:mm:ss'
+// expect 'YYYYMMDDThhmmss[.sss]' format but handle also 'YYYY-MM-DDThh:mm:ss[.sss]'
 begin
 ...
   H := ord(P[9])*10+ord(P[10])-(48+480);
-  if P[11]=':' then inc(P); // allow hh:mm:ss
+  if P[11]=':' then begin inc(P); dec(L); end; // allow hh:mm:ss
   MI := ord(P[11])*10+ord(P[12])-(48+480);
-  if P[13]=':' then inc(P); // allow hh:mm:ss
+  if P[13]=':' then begin inc(P); dec(L); end; // allow hh:mm:ss
   SS := ord(P[13])*10+ord(P[14])-(48+480);
+  if (L>16) and (P[15]='.') then begin
+    // one or more digits representing a decimal fraction of a second
+    SSS := ord(P[16])*100-4800;
+    if L>17 then SSS := SSS+ord(P[17])*10-480;
+    if L>18 then SSS := SSS+ord(P[18])-48;
+  end else
+    SSS := 0;
-  if (H<24) and (MI<60) and (SS<60) then // inlined EncodeTime()
+  if (H<24) and (MI<60) and (SS<60) and (SSS<1000) then // inlined EncodeTime()
     result := result+(H*(MinsPerHour*SecsPerMin*MSecsPerSec)+
-              MI*(SecsPerMin*MSecsPerSec)+SS*MSecsPerSec)/MSecsPerDay;
+              MI*(SecsPerMin*MSecsPerSec)+SS*MSecsPerSec+SSS)/MSecsPerDay;
 end;

Last edited by Chaa (2016-04-27 08:23:18)

ab · 2016-04-27 10:29:07

Perhaps a new dedicated type, e.g. "THighResDateTime = type TDateTime" and a new ORM field type would be preferred, and less error-prone.

But it would not only affect mORMot.pas, but also SynDB.pas, since date/time fields are handled in a special way for external tables.

Chaa · 2016-04-27 11:39:43

ab wrote:

Perhaps a new dedicated type, e.g. "THighResDateTime = type TDateTime" and a new ORM field type would be preferred, and less error-prone.

I tried to use Int64 for date/time (as in Windows TFileTime), but this increase the code amount for converting to/from JSON - using custom JSON serializers, special ORM field type and etc.

And for Delphi developers TDateTime is more natural.

ab wrote:

But it would not only affect mORMot.pas, but also SynDB.pas, since date/time fields are handled in a special way for external tables.

In addition, there is some conversions from TDateTime to TTimeLog for perfomance reasons (for example, see UTF8CompareISO8601), with loss of milliseconds.

May be the simplest way is to change TimeToIso8601PChar and Iso8601ToDateTimePUTF8CharVar functions from SynCommons.pas, that uses in all other places of mORMot, and use IFDEF for compatibility with other code.

P.S.
There is a bug in UInt3DigitsToUTF8. So UInt3DigitsToUTF8(777) produces '07}'. Fix:

 function UInt3DigitsToUTF8(Value: Cardinal): RawUTF8;
 begin
   SetString(result,nil,3);
-  PWordArray(result)[0] := TwoDigitLookupW[Value div 100];
-  PByteArray(result)[2] := (Value mod 100)+48;
+  PByteArray(result)[0] := (Value div 100)+48;
+  PWordArray(result)[1] := TwoDigitLookupW[Value mod 100];
 end;

Last edited by Chaa (2016-04-27 11:56:15)

ab · 2016-04-27 18:01:10

See http://synopse.info/fossil/info/c95b6296b8 about the bug.

Chaa · 2016-06-01 11:58:38

I created the patch for milliseconds support for TDateTime.
This patch changes low-level functions Iso8601ToTime and TimeToIso8601.
So we can use TDateTime with millisecond precision in SQLite3 DB, ORM, JSON object and record serialization, TDocVariant custom variant type, Interface based services and other parts of mORMot.

mORMotMilliseconds-20160601.zip

Milliseconds supported for TDateTime only, and not supported for TTimeLog.
In theory, there is a place in TTimeLog for milliseconds (now used only 39 bits), but this is a major breaking change.

This patch is backward compatible with currently used DBs, and can read time stored without milliseconds.

To change behavior of Iso8601ToTime and TimeToIso8601 add in your Synopse.inc:

{$define USEMILLISECONDS}

ab · 2016-12-31 09:27:50

I still don't like much the use of conditional compilation to enable such a global feature.
In addition to milliseconds, appending "Z" as UTC time zone may make sense.

We may just switch to MS resolution everywhere for TDateTime.
Using TTimeLog for Seconds resolution.
And I would like to add native TUnixTime type to the framework, which is known by SQLite3 and a lot of libraries (including JavaScript).

Chaa · 2017-01-11 04:07:37

ab wrote:

In addition to milliseconds, appending "Z" as UTC time zone may make sense.

In ideal world date and time on server side and in DB must be in UTC.
But in real applications this is often not so.

ab wrote:

We may just switch to MS resolution everywhere for TDateTime.
Using TTimeLog for Seconds resolution.
And I would like to add native TUnixTime type to the framework, which is known by SQLite3 and a lot of libraries (including JavaScript).

It would be great.

You can start from my patches, just remove ifdefs.
Changes are backward compatible with existing data.

And I propose to add some consts:

const
    ISO8601DATE_LEN = 8;     // 20170101
    ISO8601DATEEXP_LEN = 10; // 2017-01-01
    ISO8601TIME_LEN = 10;    // 120000.000
    ISO8601TIMEEXP_LEN = 12; // 12:00:00.000

So the code like "if BEnd-B<=23 then FlushToStream;" become more readable.

ab · 2017-01-15 14:41:49

I've added WithMS: boolean parameter for ISO-8601 time conversion to allow optional appending of .sss milliseconds.
Also implemented .sss parsing into ms value.

See http://synopse.info/fossil/info/fc7fc84d70

Next step is to handle TDateTimeMS serialization to include .sss suffix.
For both JSON and ORM/ODM.
Plain TDateTime will be serialized without MS, for compatibility reasons.

ab · 2017-01-15 18:01:59

Framework now handles .sss suffix for TDateTimeMS record/array JSON serialization.
See http://synopse.info/fossil/info/e8ce812bd7

And both ORM and ODM should now handle TDateTimeMS as expected.
See http://synopse.info/fossil/info/708f43b6e7

antispam88 · 2017-01-15 19:21:26

Hi guys!

It doesn't realy fit the topic but because of actuality in our company I give it a try here:

Is in mormot a TDateTime like type which can handle leap seconds?

Best regards
antispam

ab · 2017-01-15 19:36:20

No, the idea is that you should use UTC timestamps in mORMot, so TDateTime is enough.

antispam88 · 2017-01-15 20:04:08

With UTC timestamps you can handle the daylight saving time problem, but they can't consider leap seconds :-(.

My approach would be something like counting ms from a specific date and translating this by an own algorithm to human readable form.
But I don't want to reinvent the wheel if it is already done.

Chaa · 2017-01-24 08:26:32

Hello, ab!
I continue to check milliseconds.

Some problems:

1. Missing length increment for case WithMS = True:

 procedure TTextWriter.AddDateTime(const Value: TDateTime; WithMS: boolean=false);
 begin
 ...
   if frac(Value)<>0 then begin
     TimeToIso8601PChar(Value,B,true,'T',WithMS);
     inc(B,9);
+    // These lines are missing:
+    if WithMS then
+      inc(B,4);
   end;
 end;

2. Lost of milliseconds in SynDBDataset.pas:

 procedure TSQLDBDatasetStatementAbstract.ColumnsToJSON(WR: TJSONWriter);
 begin
 ...
       SynCommons.ftDate: begin
         WR.Add('"');
-        WR.AddDateTime(TField(ColumnAttr).AsDateTime);
+        WR.AddDateTime(TField(ColumnAttr).AsDateTime,True);
         WR.Add('"');
       end;
 end;

3. Lost of milliseconds in SynSQLite3.pas:

 function SQLVarToSQlite3Context(const Res: TSQLVar; Context: TSQLite3FunctionContext): boolean;
 begin
 ...
     ftDate: begin
-      DateTimeToIso8601ExpandedPChar(Res.VDateTime,tmp);
+      DateTimeToIso8601ExpandedPChar(Res.VDateTime,tmp,'T',True);
       sqlite3.result_text(Context,tmp,-1,SQLITE_TRANSIENT_VIRTUALTABLE);
     end;
 end;

Chaa · 2017-01-30 07:52:33

Maybe create a Tickets for these issues?

ab · 2017-01-30 08:10:42

The first is a bug.
See https://synopse.info/fossil/info/769eb7da7f

But 2 and 3 will change the existing behavior, adding microseconds even if it is not needed.
We have to let the DB layer know if the MS are requested or not, not add them every time.
And adding ftDateMS to TSQLDBFieldType does not seem right to me...

Chaa · 2017-01-30 08:37:24

These code is used for support external databases via SQLite virtual table magic.
I think it will have a minimal impact on the existing applications.
Maybe only when working with virtual tables without ORM, but this is a rare use case.

ab · 2017-01-30 09:47:55

No, it will change the behavior of all ORM process, when using direct SQLite3 storage, I'm afraid.

I'm finishing some refactoring to properly handle it.
I do not want to have anything changed by default on JSON output, if TDateTimeMS is not used.
It may break a lot of regression tests of production code, which I want to avoid (not only for myself).

ab · 2017-01-30 10:38:45

I've committed the refactoring.

Chaa · 2017-02-02 08:00:29

ab wrote:

I've committed the refactoring.

Thanks!
Now milliseconds works!

Chaa · 2020-02-11 08:04:04

Milliseconds again.

I have a problem with floating point numbers rounding error.
There are assumtion, across a framework, that if two dates E and F differs in milliseconds, then abs(E-F) < 1/MSecsPerDay.
This is not true - thanks to floating point.
Test case:

procedure TTestLowLevelCommon.Iso8601DateAndTime;
  procedure Test(D: TDateTime; Expanded: boolean);
  begin
    ...
    s := DateTimeToIso8601(D,Expanded);
    E := Iso8601ToDateTime(s+'.001');
    F := Iso8601ToDateTime(s+'.002');
    Check(abs(E-F)<1/MSecsPerDay);           // <- failed
    Check(not SameValue(E,F,1/MSecsPerDay)); // <- failed
  end;
begin
  ...
end;

My real problem is in SynSQLite.Utf8SQLDateTime, that uses SameValue as in test case.

Some numbers:
1/MSecsPerDay = 1.15740740740741E-8
2020-01-01T00:00:00.002 - 2020-01-01T00:00:00.001 = 1.15687726065516E-8

The solution may be in defining the const for milliseconds precision.

const
  MSecsPrecision = 0.0000000115;

Last edited by Chaa (2020-02-11 11:37:54)

mpv · 2020-02-12 09:09:42

Ah, IMHO representing a TDateTime as float is a Delphi PITA. From our experience with time serias databases (we use InfluxDB in some of our project) perfect solution is to store hi-res time as Int64 - see for example how Influx store it
But for compatibility having custom millisecond precision as @Chaa propose is a good idea..

mORMot Open Source

#1 2016-04-27 08:22:48

Support for milliseconds in TDateTime

#2 2016-04-27 10:29:07

Re: Support for milliseconds in TDateTime

#3 2016-04-27 11:39:43

Re: Support for milliseconds in TDateTime

#4 2016-04-27 18:01:10

Re: Support for milliseconds in TDateTime

#5 2016-06-01 11:58:38

Re: Support for milliseconds in TDateTime

#6 2016-12-31 09:27:50

Re: Support for milliseconds in TDateTime

#7 2017-01-11 04:07:37

Re: Support for milliseconds in TDateTime

#8 2017-01-15 14:41:49

Re: Support for milliseconds in TDateTime

#9 2017-01-15 18:01:59

Re: Support for milliseconds in TDateTime

#10 2017-01-15 19:21:26

Re: Support for milliseconds in TDateTime

#11 2017-01-15 19:36:20

Re: Support for milliseconds in TDateTime

#12 2017-01-15 20:04:08

Re: Support for milliseconds in TDateTime

#13 2017-01-24 08:26:32

Re: Support for milliseconds in TDateTime

#14 2017-01-30 07:52:33

Re: Support for milliseconds in TDateTime

#15 2017-01-30 08:10:42

Re: Support for milliseconds in TDateTime

#16 2017-01-30 08:37:24

Re: Support for milliseconds in TDateTime

#17 2017-01-30 09:47:55

Re: Support for milliseconds in TDateTime

#18 2017-01-30 10:38:45

Re: Support for milliseconds in TDateTime

#19 2017-02-02 08:00:29

Re: Support for milliseconds in TDateTime

#20 2020-02-11 08:04:04

Re: Support for milliseconds in TDateTime

#21 2020-02-12 09:09:42

Re: Support for milliseconds in TDateTime

Board footer