How to do extended JSON $date right in Delphi, or is it a bug about timezones and UTC in System.JSON?

Question

I'm trying to parse extended JSON using TJSONObjectBuilder...AddPairs(). My JSON contains a $date (I need it in Utc for MongoDB). But somehow the timezone gets broken, no matter if my input is already Utc or not.

Input : {"Zulu":{"$date":"2019-01-01T00:00:00.000Z"},"Utc+1":{"$date":"2019-01-01T01:00:00.000+01:00"}}
Output: {"Zulu":{"$date":"2019-01-01T01:00:00.000Z"},"Utc+1":{"$date":"2019-01-01T01:00:00.000Z"}}
                                      ^                                            ^

Without TJsonDateTimeZoneHandling.Utc it is correct, but that doesn't help me, because I need the result in Utc:

Output: {"Zulu":{"$date":"2019-01-01T01:00:00.000+01:00"},"Utc+1":{"$date":"2019-01-01T01:00:00.000+01:00"}}

Here is my minimal code to show it:

program SystemJsonDateTest;
{$APPTYPE CONSOLE}
uses
  System.Classes, System.JSON.Types, System.JSON.Writers, System.JSON.Builders;
var
  StringWriter: TStringWriter;
  JsonWriter: TJsonTextWriter;
  Builder: TJSONObjectBuilder;
begin
  StringWriter:= TStringWriter.Create;

  JsonWriter:= TJsonTextWriter.Create(StringWriter);
  JsonWriter.ExtendedJsonMode:= TJsonExtendedJsonMode.StrictMode;
  JsonWriter.DateTimeZoneHandling:= TJsonDateTimeZoneHandling.Utc;

  TJSONObjectBuilder.Create(JsonWriter)
    .BeginObject
      .AddPairs('{"Zulu":{"$date":"2019-01-01T00:00:00.000Z"},'
      + '"Utc+1":{"$date":"2019-01-01T01:00:00.000+01:00"},'
      + '"Unix":{"$date":1546300800000}}')
    .EndObject
    .Free;

  JsonWriter.Free;
  WriteLn(StringWriter.ToString);
  StringWriter.Free;
  ReadLn;
end.

Background: I'm using TMongoDocument.AsJSON, found this behavior and tried to reproduce it with minimal code and without any reference to MongoDB components. If I'm doing something weird or the demo can be even more simplified, please comment...

In that MongoDocument, TBsonWriter is used instead, but it shows the same problem:

Stream:= TFileStream.Create('file.bson', fmCreate);
BsonWriter:= TBsonWriter.Create(Stream);
TJSONObjectBuilder.Create(BsonWriter).BeginObject.AddPairs(//see above

I know, this was a lot of text - in case you forgot the question, it is in the title ;)

Arnaud Bouchez · Answer 1 · 2019-12-05T09:18:58.353

MongoDB clients may support zones in the "$date" extended syntax for Date fields in the JSON input (even if the Delphi client seems to ignore it), but MongoDB server won't handle zones in its BSON storage.

In fact, the reference documentation states that Date values are stored as UTC - they are even called UTC Date in the BSON format, and stored as an Int64 number of Unix milliseconds:

BSON Date is a 64-bit integer that represents the number of milliseconds since the Unix epoch (Jan 1, 1970). This results in a representable date range of about 290 million years into the past and future.

As a result, both your "Utc+1" and "Zulu" fields would contain the very same exact UTC timestamp, even after proper conversion of the timezone by the Client library.

So you should better only send UTC dates to MongoDB, and make the conversion on the client side. Even with correct conversion, you will loose the Zone information in all cases, since it will be stored as UTC. And don't use ISO-8601 text for transmission, but just the UnixTime value, as integer:

function DateTimeToUnixMSTime(const AValue: TDateTime): Int64;
begin
  result := Round((AValue - UnixDateDelta) * MSecsPerDay);
end;

BTW it is a good idea to only use UTC dates in any kind of databases, then use on-the-fly conversion to the current user local on display/reporting, and store the local zone in a separated field, if really needed, either as text identifier, or as floating-point bias in days (perhaps more convenient - note that zone biases are not necessary integers, see e.g. for Afghanistan).

Thank you - using the UnixTime value as Integer is a valid workaround and would help me. However, you are mainly answering to my background information and not to my question. My example wasn't about MongoDB or BSON at all. Using UTC is what I know and wanted - but when I do, there is some wrong timezone conversion taking place, both in my minimal example and in my real case where I do `MongoDoc.AsJSON:= MyJson;` containing a $date in UTC. I debugged it and think it is a bug in `TJSONObjectBuilder`. The main question is, is that true? — maf-soft, Dec 05 '19 at 09:30
I already have a workaround which I prefer, because I want to be able to decipher my JSON easily. Before sending it to `AsJSON`, I replace the date like this: `Value:= DateToISO8601(ISO8601ToDate(Value, True), False);` - this converts it to a wrong value, so it becomes correct after the bug. — maf-soft, Dec 05 '19 at 10:06
For the sake of completeness, I tried the `DateUtils.DateTimeToUnix()*1000` workaround {"$date":1575550800000}, but it leads to exactly the same 1 hour difference, so I could add it to my demo code... — maf-soft, Dec 05 '19 at 13:02
So try to use another MongoDB library like our OpenSource https://github.com/synopse/mORMot/blob/master/SynMongoDB.pas, which has no problem with dates. — Arnaud Bouchez, Dec 05 '19 at 17:07

maf-soft · Accepted Answer · 2019-12-05T11:56:45.243

Yes, it is a bug, and it is fixed in 10.3.

In unit System.JSON.Builders, TJSONCollectionBuilder.TBaseCollection.WriteJSON() creates a TJsonTextReader with the default DateTimeZoneHandling=Local, which means, any DateTimes are converted to local.

But in System.JSON.Writers, TJsonTextWriter.WriteValue(Value: TDateTime), the DateTime value, which cannot contain the time zone, is expected and interpreted as Utc when DateTimeZoneHandling=Utc.

So, TJSONCollectionBuilder requires a Writer with DateTimeZoneHandling=Local, which makes it impossible to get the output in correct Utc.

...and just after debugging this, I knew what to google for: http://docwiki.embarcadero.com/RADStudio/Rio/en/New_features_and_customer_reported_issues_fixed_in_RAD_Studio_10.3

TMongoDocument and TJSONCollectionBuilder incorrectly parse ISO Dates Data, Data\FireDAC, RTL, RTL\Delphi RSP-17046
[FireDAC, MongoDB] [TJSONCollectionBuilder.TBaseCollection.WriteJSON] Dates always converted to local timezone if value updated in text mode Data, Data\FireDAC, RTL\Delphi RSP-20571

How to do extended JSON $date right in Delphi, or is it a bug about timezones and UTC in System.JSON?

2 Answers2