TCreateTime

array81 · 2011-09-11 22:08:26

I have found TModTime published property. Can you add TCreateTime to automatically add the creation date of the record?

About TModTime: to have always the update date inside TModTime property I need only to publisher the property, for example:

  TSQLContacts = class(TSQLRecord)
  private
    ...
    fRecordModified: TModTime;
  published
    ...
    property RecordModified: TModTime read fRecordModified write fRecordModified;
  end;

After this the framework will update the RecordModified value every time I will change some value of some record, right?

Last edited by array81 (2011-09-11 22:18:31)

ab · 2011-09-13 15:12:28

That's it. I've added a new sftCreateTime / TCreateTime published field type in TSQLRecord, which will be set to the current server time stamp at record creation.

For instance, if you add such fields to your record:

...
    property LastChange: TModTime read fLastChange write fLastChange;
    property CreatedAt: TCreateTime read fCreatedAt write fCreatedAt;

The LastChange will be updated at each record update.
The CreatedAt property will be set at record creation.

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

As stated by the types definition:

  /// the available types for any SQL field property, as managed with the
  // database driver
  TSQLFieldType = (
  ....
    /// an INTEGER field containing the TModTime time of the record latest update
    // - TModTime=TCreateTime=TTimeLog=Int64 Delphi property which can be
    // typecasted to Iso8601
    // - the value of this field is automatically updated with the current
    // date and time each time a record is updated (with external DB, it will
    // use the Server time, as retrieved from SynDB) - see ComputeFieldsBeforeWrite
    // virtual method of TSQLRecord
    // - note only RESTful PUT/POST access will change this field value: manual
    // SQL statements (like 'UPDATE Table SET Column=0') won't change its content
    sftModTime,
    /// an INTEGER field containing the TCreateTime time of the record creation
    // - TModTime=TCreateTime=TTimeLog=Int64 Delphi property which can be
    // typecasted to Iso8601
    // - the value of this field is automatically updated with the current
    // date and time when the record is created (with external DB, it will
    // use the Server time, as retrieved from SynDB) - see ComputeFieldsBeforeWrite
    // virtual method of TSQLRecord
    // - note only RESTful PUT/POST access will set this field value: manual
    // SQL statements (like 'INSERT INTO Table ...') won't set its content
    sftCreateTime);

Note that only RESTful / ORM level commands will set those fields appropriately (via the new ComputeFieldsBeforeWrite virtual method).

Direct low-level UPDATE / INSERT SQL statements, or non-Delphi AJAX access won't set the value automatically.

array81 · 2011-09-13 16:39:12

With direct low-level UPDATE / INSERT SQL statements can I always use SQL to upgrade it with current date (for TModTime), right?

ab · 2011-09-13 18:00:26

array81 wrote:

With direct low-level UPDATE / INSERT SQL statements can I always use SQL to upgrade it with current date (for TModTime), right?

Yes - and you should better use the "Server Time", i.e. TSQLRest.ServerTimeStamp value (with or without external databases, it will return the server-side time, whatever client time is).

array81 · 2011-09-16 12:08:48

I have found this on documentation

For TTimeLog / TModTime, the proprietary fast Int64 date time format will map the Iso8601 
record  type,  as  defined  in  SynCommons:  first  0..5  bits  will  map  seconds,  next  6..11  will  map 
minutes, 12..16 hours, 17..21 days (minus one), 22..25 months (minus one) and 26..38 years. 
This format will be very fast for comparing dates or convert into/from text, and will be stored 
more efficiently than plain ISO 8601 TEXT as used for TDateTime fields.

However I have some question about TModTime and TCreateTime:
1) How can I convert TModTime and TCreateTime into TDateTime?
2) How can I use them with SQL? For example I want all record with TModTime between 2 date.

Thanks

ab · 2011-09-16 15:43:38

array81 wrote:

1) How can I convert TModTime and TCreateTime into TDateTime?

See the Iso8601 type and all associated functions.

For instance: Iso8601FromDateTime() and Iso8601Now.

aDateTime := Iso8601(aModTime).ToDateTime;

array81 wrote:

2) How can I use them with SQL? For example I want all record with TModTime between 2 date.

Use Iso8601 to convert from TDateTime into the corresponding Delphi Int64 / SQL INTEGER value.

TModTime or TCreateTime are SQL INTEGER values.

array81 · 2011-09-17 17:50:48

I have create a now record (today) then I have use this code to retrieve the ModTime:

createdate := Iso8601ToDateTime(TabellaRisultati.GetString(e, 2));
lastdate := Iso8601ToDateTime(TabellaRisultati.GetString(e, 3));

I have received 30/12/1899 I don't understand why.

The record is created with this code:

    ProgettoRecord := TSQLProjects.Create;
    try
      ProgettoRecord.Title := StringToUTF8(edtTitle.Text);
      ProgettoRecord.Kind := StringToUTF8(cbbType.Text);
      ProgettoRecord.Files := StringToUTF8(cbbNumber.Text);
      ProgettoRecord.DateFrom := cbbFrom.Date;
      ProgettoRecord.DateTo := cbbTo.Date;
      ProgettoRecord.Status := cbbStatus.ItemIndex;
      ProgettoRecord.Note := StringToUTF8(memDescription.Text);

      EditItem := Database.Add(ProgettoRecord, true);
    finally
      ProgettoRecord.Free;
    end;

ab · 2011-09-17 18:06:43

What is the result of your GetString(e, 2) and GetString(e, 3) ?

This sounds like not a valid ISO 8601 text format.

I think this is a TModTIme, so it is an integer, not a text.

So use Iso8601(GetInt64(e,2)).ToDateTime instead.

array81 · 2011-09-17 23:12:02

OK now works but I have a problem. When I create a new record I have TCreateTime and TModTime with the same value and with correct value (the time of creation).

When I update it with this code:

ProgettoRecord := TSQLProjects.Create;
    try
      ProgettoRecord.Title := StringToUTF8(edtTitle.Text);
      ProgettoRecord.Kind := StringToUTF8(cbbType.Text);
      ProgettoRecord.Files := StringToUTF8(cbbNumber.Text);
      ProgettoRecord.DateFrom := cbbFrom.Date;
      ProgettoRecord.DateTo := cbbTo.Date;
      ProgettoRecord.Status := cbbStatus.ItemIndex;
      ProgettoRecord.Note := StringToUTF8(memDescription.Text);

      EditItem := Database.Update(ProgettoRecord);
    finally
      ProgettoRecord.Free;
    end;

I have the right value of TModTime, but TCreateTime is set to 0 (30/12/1899)

ab · 2011-09-18 07:30:03

ProgettoRecord should already contain the previous field values, before update.
It override the existing values.

You have to use a Database.Retrieve before Database.Update if you want to keep the TCreateTime field.
For instance, here is how the testing is made:

procedure TTestExternalDatabase.Test(StaticVirtualTableDirect: boolean);
  (...)
            Check(fClient.Retrieve(i,RExt,true),'for update');
            Check(RExt.YearOfBirth<>RExt.YearOfDeath);
            RExt.YearOfBirth := RExt.YearOfDeath;
            Check(fClient.Update(RExt),'Update 1/100 rows');
            Check(fClient.UnLock(RExt));

array81 · 2011-09-18 08:36:49

I thought it would not be overwritten TCreateTime if I used the framework...

In any case thanks, I will try it.

array81 · 2011-09-18 10:33:18

It's works but I still do not understand why TCreateTime is overwrite when I use Update function. I think Update function should edit onlt TModTime but not TCreateTime. TCreateTime should be edit only when I use Add function (or diretly by sql). Is not right?

ab · 2011-09-18 11:20:28

array81 wrote:

It's works but I still do not understand why TCreateTime is overwrite when I use Update function. I think Update function should edit onlt TModTime but not TCreateTime. TCreateTime should be edit only when I use Add function (or diretly by sql). Is not right?

You are certainly right.
I'll have to make a change here.

array81 · 2011-09-18 20:01:47

Good, so I will wait for it.

array81 · 2011-09-21 11:07:54

Any news about it?

array81 · 2011-10-16 20:22:46

Some hope to add this feature?

ab · 2011-10-17 09:42:07

OK - I've added it.
Was not so simple because there was no special handling of Upate/Insert/Select statements.
So I've added TSQLOccasion to handle the special case of field type (like TCreateTime).
See http://synopse.info/fossil/info/b1686cc0ba

TCreateTime published fields now are not modified at update.

Related integrated tests have been included.

mORMot Open Source

#1 2011-09-11 22:08:26

TCreateTime

#2 2011-09-13 15:12:28

Re: TCreateTime

#3 2011-09-13 16:39:12

Re: TCreateTime

#4 2011-09-13 18:00:26

Re: TCreateTime

#5 2011-09-16 12:08:48

Re: TCreateTime

#6 2011-09-16 15:43:38

Re: TCreateTime

#7 2011-09-17 17:50:48

Re: TCreateTime

#8 2011-09-17 18:06:43

Re: TCreateTime

#9 2011-09-17 23:12:02

Re: TCreateTime

#10 2011-09-18 07:30:03

Re: TCreateTime

#11 2011-09-18 08:36:49

Re: TCreateTime

#12 2011-09-18 10:33:18

Re: TCreateTime

#13 2011-09-18 11:20:28

Re: TCreateTime

#14 2011-09-18 20:01:47

Re: TCreateTime

#15 2011-09-21 11:07:54

Re: TCreateTime

#16 2011-10-16 20:22:46

Re: TCreateTime

#17 2011-10-17 09:42:07

Re: TCreateTime

Board footer