mORMot Open Source

uian2000

Member

Registered: 2014-05-06

Posts: 69

How dose timezone effect in mORMot

I have a TModTime field to log the modify DateTime.
It's odd that TSQLTableToGrid always display RawTime than my local time. My own timezone is GMT + 8.

Here is the sample code:

unit uMain;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Grids, StdCtrls,
  SynCommons, mORMot, mORMotUI, mORMotSQLite3, SynSQLite3Static;

type
  TfrmTestTimezone = class(TForm)
    dgSample: TDrawGrid;
    btnInsert: TButton;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure btnInsertClick(Sender: TObject);
  private
    { Private declarations }
    fServer: TSQLRestServerDB;
    procedure RefreshGrid;
  public
    { Public declarations }
  end;

var
  frmTestTimezone: TfrmTestTimezone;

implementation

{$R *.dfm}

type
  TSQLSample = class(TSQLRecord)
  private
    fSomeThing: RawUTF8;
    fUpdateTime: TModTime;
  published
    property SomeThing: RawUTF8 read fSomeThing write fSomeThing;
    property UpdateTime: TModTime read fUpdateTime write fUpdateTime;
  end;

procedure TfrmTestTimezone.FormCreate(Sender: TObject);
var
  vModel: TSQLModel;
begin
  vModel := TSQLModel.Create([TSQLSample]);
  fServer := TSQLRestServerDB.Create(vModel, ChangeFileExt(paramstr(0),'.db3'));
  fServer.CreateMissingTables();
end;

procedure TfrmTestTimezone.FormDestroy(Sender: TObject);
begin
  fServer.Free;
end;

procedure TfrmTestTimezone.FormShow(Sender: TObject);
begin
  RefreshGrid;
end;

procedure TfrmTestTimezone.btnInsertClick(Sender: TObject);
var
  vSample: TSQLSample;
begin
  vSample := TSQLSample.Create;
  vSample.SomeThing := StringToUTF8(DateTimeToStr(Now));
  fServer.Add(vSample, True);
  RefreshGrid;
  vSample.Free;
end;

procedure TfrmTestTimezone.RefreshGrid;
begin
  with TSQLTableToGrid.Create(dgSample,
    fServer.MultiFieldValues(TSQLSample, ''), nil) do
  begin
    Table.SetFieldType('UpdateTime', sftTimeLog);
    SetFieldLengthMean('bxx', False);
    Resize(dgSample);
  end;
end;

end.

BTW, if I SetFieldType like this

procedure TfrmTestTimezone.RefreshGrid;
begin
  with TSQLTableToGrid.Create(dgSample,
    fServer.MultiFieldValues(TSQLSample, ''), nil) do
  begin
// Change Begin
    Table.SetFieldType('UpdateTime', sftModTime);
// Change End
    SetFieldLengthMean('bxx', False);
    Resize(dgSample);
  end;
end;

That field will display as INTEGER!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: How dose timezone effect in mORMot

The time stored is the one retrieved from the server.

The time should be displayed as ISO-8601 date and time (or any custom format) in the TSQLTable grid.
The TSQLTable.ExpandAsString() method handles sftTimeLog, sftModTime, sftCreateTime as date/time.

uian2000

Member

Registered: 2014-05-06

Posts: 69

Re: How dose timezone effect in mORMot

When I was tring some older version of mORMot, I found field of TCreateTime and TModTime was saved as local time instead of utc.
With tracking of code difference, KEY difference raised as follow:

function TSQLRest.GetServerTimeStamp: TTimeLog;
var Tix: cardinal;
begin
  Tix := GetTickCount shr 9; // resolution change 1 ms -> 512 ms
  if fServerTimeStampCacheTix=Tix then
    result := fServerTimeStampCacheValue.Value else begin
    fServerTimeStampCacheTix := Tix;
    // Older version differs here
    // fServerTimeStampCacheValue.From([b]Now[/b]+fServerTimeStampOffset);
    fServerTimeStampCacheValue.From(NowUTC+fServerTimeStampOffset);
    result := fServerTimeStampCacheValue.Value;
  end;
end;

My issue here is to save a local time. So this is my solution:

procedure TfrmTestTimezone.FormCreate(Sender: TObject);
var
  vModel: TSQLModel;
begin
  vModel := TSQLModel.Create([TSQLSample]);
  fServer := TSQLRestServerDB.Create(vModel, ChangeFileExt(paramstr(0),'.db3'));
  fServer.CreateMissingTables();
  // Line Adding
  fServer.ServerTimeStamp := TimeLogNow;
end;

It's perfect!:)

Last edited by uian2000 (2014-11-04 05:52:36)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: How dose timezone effect in mORMot

Thanks for the feedback.