how to query based on Datetime

longge007 · 2011-01-25 05:45:06

var
  QJson:TSQLTableJSON;
  st:TDatetime;
  sqlwhere:RAWUTF8 ;
begin
  st:=trunc(Datetimepicker1.Date)+Frac(Datetimepicker2.Time);
  QJson:=Database.List([TSQLSamplerecord],'*','time='+DateTimeToIso8601(st,true,'T'));
  if QJson<>nil then
  try
      edit3.Text:=inttostr(QJson.RowCount);
  finally
     qJson.Free;
  end;
end;

in my table there is one field 'Time',like time ='2011-01-24T10:47:30', the result is no. i don't know what's the matter。

ab · 2011-01-25 07:07:49

What is the TSQLSamplerecord type definition?
I guess this is perhaps:

Time: TDateTime;

In this case, Time field will be filled with Delphi Iso8601 textual values.
So you should write:

QJson:=Database.List([TSQLSamplerecord],'*',FormatUTF8('time="%"',[DateTimeToIso8601(st,true)]));

Since this field is stored as a string, you'll have to use quotes for the WHERE statement.

Another possibility could be to use a TTimeLog type: in this case, an Int64 value will be stored in the field.
See its definition in SQLite3Commons:

  /// fast integer-encoded date and time value
  // - faster than Iso-8601 text and TDateTime
  // - to be used as published property field in TSQLRecord
  // - convenient for current date and time process (logging e.g.)
  // - use internally for computation an abstract "year" of 16 months of 32 days
  // of 32 hours of 64 minutes of 64 seconds - same as Iso8601ToSeconds()
  // - type cast any value of TTimeLog with the Iso8601 object below for easy
  // access to its content
  TTimeLog = type Int64;

longge007 · 2011-01-25 07:41:25

yeah，you are right，i'm using first type。In fact ，our project used Int64 type about Time.i want to translate it into your framwok.i did't fint TTimelog type in your TTimelog. now i can try to use it. and i think the TTimelog(int64) type is best way to store time event.
thanks a lot

ab · 2011-01-25 08:33:09

Yes, TTimeLog is very efficient, and can be used to fast comparison between two time/date values.
You can even create a SQLite3 index on the TTimeLog field, and it will use less space on disk than Iso8601.

One drawback of TTimeLog is that the Int64 encoding is proprietary.
It does rely on the abstract "year" of 16 months of 32 days of 32 hours of 64 minutes of 64 seconds... that is it's computed very efficiently by some low level bit shifting operations.
But it's not the native time type of SQLite3...

In practice, since TTimeLog is well integrated in the framework, you can use it directly, for instance in our Grid component.

longge007 · 2011-01-25 09:27:44

if i use the TTimelog type, for example ,there are two TDatetime :Dtstart,Dtend; if i want to query the table with time>=Dtstart and time<=Dtend,
whether or not i can directly use

sqlwhere='time='+IntToStr(Iso8601FromDateTime(Dtstart))+' and '+'time='+IntToStr(Iso8601FromDateTime(Dtend));
QJson:=Database.List([TSQLSamplerecord],'*',sqlwhere);

ab · 2011-01-25 12:52:10

You should better code, to use prepared statements and the more easy to read FormatUTF8() function:

sqlwhere := FormatUTF8('time>=:(%): and time<=:(%):',[Iso8601FromDateTime(Dtstart),Iso8601FromDateTime(Dtend)]);

but yes, this is perfectly possible.

And if you create an index over the time column, results will be immediate.
The right way of creating an index is to override the InitializeTable method of the class (so that it will be created automatically at table creation):

class procedure TSQLSamplerecord.InitializeTable(Server: TSQLRestServer; const FieldName: ShortString);
begin
  if (FieldName[0]=#0) or IdemPropName(FieldName,'time') then
    Server.CreateSQLIndex(self,'time',false);
end;

longge007 · 2011-01-30 07:34:47

i have redefinde the field time as TtimeLog,now

st:TDatetime;
st:=trunc(Datetimepicker1.Date)+Frac(Datetimepicker2.Time);//st:=20110128 09:16:27 
QJson:=Database.List([TSQLSamplerecord],'*',FormatUTF8('time=:(%):',[Iso8601FromDateTime(st)]));//time=134959502363

..

do you guess what's the result of Json.rowcount?

it's 6816 ,which is all rows ,in fact it's only 1 row
and i trace the FquerySqL : 'select id,time,Name,Question from Samplerecord where time=:(134959502363):;'
why?

Last edited by longge007 (2011-01-30 07:49:17)

longge007 · 2011-01-30 08:38:53

ab wrote:

You should better code, to use prepared statements and the more easy to read FormatUTF8() function:
sqlwhere := FormatUTF8('time>=:(%): and time<=:(%):',[Iso8601FromDateTime(Dtstart),Iso8601FromDateTime(Dtend)]);
but yes, this is perfectly possible.
And if you create an index over the time column, results will be immediate.
The right way of creating an index is to override the InitializeTable method of the class (so that it will be created automatically at table creation):
class procedure TSQLSamplerecord.InitializeTable(Server: TSQLRestServer; const FieldName: ShortString);
begin
  if (FieldName[0]=#0) or IdemPropName(FieldName,'time') then
    Server.CreateSQLIndex(self,'time',false);
end;

when i override the procedure InitializeTable,there is an error:

TSQLSampleRecord = class(TSQLRecord)
  private
    fQuestion: RawUTF8;
    fName: RawUTF8;
    fTime: TTimeLog;//TDatetime;
    fWave: TSQLRawBlob;
  published
    property Time: TTimeLog read fTime write fTime;
    property Name: RawUTF8 read fName write fName stored false; //made Unique
    property Question: RawUTF8 read fQuestion write fQuestion;
    property Wave: TSQLRawBlob read fWave write fWave;
  public  procedure InitializeTable(Server: TSQLRestServer; const FieldName: ShortString);override;
  end;
implementation
//..
procedure TSQLSampleRecord.InitializeTable(Server: TSQLRestServer; const FieldName: ShortString);override;
begin
if (FieldName[0]=#0) or IdemPropName(FieldName,'time') then
    Server.CreateSQLIndex(self,'time',false);
end;

[DCC Error] SampleData.pas(30): E2037 Declaration of 'InitializeTable' differs from previous declaration
[DCC Error] SampleData.pas(71): E2070 Unknown directive: 'override'
[DCC Error] SampleData.pas(74): E2250 There is no overloaded version of 'CreateSQLIndex' that can be called with these arguments

ab · 2011-01-30 08:39:16

About the compilation error messages, get sure you've downloaded the LATEST version from source code repository.

Do you use an index?

What if you get rid of the : (%: ) and write 'time=%' ?

What the result returned by SQLiteSpy if you run:

 select id,time,Name,Question from Samplerecord where time=134959502363;

Did you create a fresh database after having redefined the field tiem as TTimeLog?
I guess your data was is still containing text-encoded time, and not Int64 time.

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

As I wrote above, I guess you didn't delete your previous database file, and time values are still encoded as text, not as Int64 TTImeLog values.

And your overriden InitializeTable is wrong: it should be declared as a CLASS PROCEDURE not only a PROCEDURE.

longge007 · 2011-01-30 09:22:00

ab wrote:

As I wrote above, I guess you didn't delete your previous database file, and time values are still encoded as text, not as Int64 TTImeLog values.
And your overriden InitializeTable is wrong: it should be declared as a CLASS PROCEDURE not only a PROCEDURE.

yeah, my version is 1.12
now as your above ,now the Querysql can work.
but the creatindex can't override.

type
TSQLSampleRecord = class(TSQLRecord)
  private
    fQuestion: RawUTF8;
    fName: RawUTF8;
    fTime: TTimeLog;//TDatetime;
    fWave: TSQLRawBlob;
  published
    property Time: TTimeLog read fTime write fTime;
    property Name: RawUTF8 read fName write fName stored false; //made Unique
    property Question: RawUTF8 read fQuestion write fQuestion;
    property Wave: TSQLRawBlob read fWave write fWave;
  public
  function DataAsHex(aClient: TSQLRestClientURI): RawUTF8;
  class procedure InitializeTable(Server: TSQLRestServer; const FieldName: ShortString);override;
  end;
implementation
//..
class procedure TSQLSamplerecord.InitializeTable(Server: TSQLRestServer; const FieldName: ShortString);
begin
  if (FieldName[0]=#0) or IdemPropName(FieldName,'time') then
    Server.CreateSQLIndex(self,'time',false);
end;

the Error is [DCC Error] SampleData.pas(71): E2070 Unknown directive: 'override'

what's the wrong ,i am fused with it.

Last edited by longge007 (2011-01-30 09:22:42)

ab · 2011-01-30 14:43:24

I don't know what's happening: your above code is correct, and compiles by me...

So I guess this is a bug of the Delphi IDE. Please restart it. If it doesn't work, delete all dcu generated for the library.

longge007 · 2011-01-31 01:10:00

thanks a lot, as you said, now it's ok.:)

Leslie7 · 2017-10-23 20:23:26

I could not find any info on how to work with TTimeLog fields in SQL queries to calculate differences in days, hours, ...

ab · 2017-10-23 20:51:29

The easiest is to convert to TDateTime for computation.

Leslie7 · 2017-10-23 21:28:37

I am not sure I follow you. TDateTime is a pascal type, right ? I need to work with date and time differences within the SQL queries. Eg to select records based on a field value within 7 days from a given date or within 10 minutes from a given time.
An example would be very useful.

Last edited by Leslie7 (2017-10-23 21:38:37)

ab · 2017-10-24 08:27:00

If you want to play with TTimeLog values directly in DB, you can use, if your DB backend is SQLite3, the TimeLog() SQL function, which returns an ISO-8601 date/time.
But if you want to pickup a field value within a given period, you just make the computation on the Delphi side using a TTimeLog value, and TDateTime for computation, then use a parametrized query with a 'DateField>?' clause, binding the TTimeLog Int64 value.

Leslie7 · 2017-10-24 20:40:38

I think it is clear now. Thank you!

mORMot Open Source

#1 2011-01-25 05:45:06

how to query based on Datetime

#2 2011-01-25 07:07:49

Re: how to query based on Datetime

#3 2011-01-25 07:41:25

Re: how to query based on Datetime

#4 2011-01-25 08:33:09

Re: how to query based on Datetime

#5 2011-01-25 09:27:44

Re: how to query based on Datetime

#6 2011-01-25 12:52:10

Re: how to query based on Datetime

#7 2011-01-30 07:34:47

Re: how to query based on Datetime

#8 2011-01-30 08:38:53

Re: how to query based on Datetime

#9 2011-01-30 08:39:16

Re: how to query based on Datetime

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

Re: how to query based on Datetime

#11 2011-01-30 09:22:00

Re: how to query based on Datetime

#12 2011-01-30 14:43:24

Re: how to query based on Datetime

#13 2011-01-31 01:10:00

Re: how to query based on Datetime

#14 2017-10-23 20:23:26

Re: how to query based on Datetime

#15 2017-10-23 20:51:29

Re: how to query based on Datetime

#16 2017-10-23 21:28:37

Re: how to query based on Datetime

#17 2017-10-24 08:27:00

Re: how to query based on Datetime

#18 2017-10-24 20:40:38

Re: how to query based on Datetime

Board footer