mORMot Open Source

bduncanj

Member

Registered: 2015-09-27

Posts: 2

TDateTime column appears written to SQLite3 without UTC timezone

Hi,

I've an application that writes events (alarms) in into a SQLite alarms table, with one of the columns being the start time of said alarm.  In the Delphi Alarm record this is a TDateTime and I understand mORMot writes this to SQLite as an ISO-8601 compatible field however it appears to lack the 'Z' suffix denoting UTC in the database.

I'd like all my records to be stored in UTC time so when creating my Alarm record I convert the start time to UTC:

myRecord.startTime := TTimeZone.Local.ToUniversalTime(aLocalTime);

However... this appears in the column in my SQLite table as 2015-02-01T22:23:01 (for example).  I later manually serialize these to JSON using the ObjectToJSON() function from the SQLite3Commons.pas file and the same format is preserved (lacking the 'Z' suffix in the time field).

As a result... when the browser consumes the date string it relies on the native implementation, and Chrome (45 at time of writing) appears to be the only browser which interprets dates without suffixes as UTC while FireFox and IE11 assume dates are local time (which is closer to the ISO-8601 standard).

• Should I be doing something else to the Delphi TDateTime before asking mORMot to write it to the database so that it gets the 'Z' suffix?

• Is this a property of the mORMot table configuration I define in Delphi?

• Or should I instead be defining a custom Delphi -> JSON serializer and just add this suffix myself?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,663

Website

Re: TDateTime column appears written to SQLite3 without UTC timezone

There is not such flag nor code to add the 'Z' suffix, in the current state of the framework, to all TDateTime serialization features...
It would be possible, but a bit difficult to track all needed modifications.

But I've added the woDateTimeWithZSuffix option, to be supplied to ObjectToJson().
See http://synopse.info/fossil/info/dc41005627
It should happend the 'Z' suffix as you expect.

bduncanj

Member

Registered: 2015-09-27

Posts: 2

Re: TDateTime column appears written to SQLite3 without UTC timezone

Splendid, many thanks for the (very) quick reply.  I'm back in business!