Time Query towards SQLite Database

[phr0x]

Hey, I want to filter a database depending on which timeframe gets typed in by the user.

I save time values within a pascal with code like this

Code: Pascal  [Select][+][-]

1. SQLQuery1.SQL.Text:='Insert into dbtable (Value,time) values (:PHvalue, :PHtime);
2. SQLQuery1.Params.Parambyname('PHvalue').AsInteger := A;
3. SQLQuery1.Params.Parambyname('PHtime').AsDateTime :=Now;
4.  

This works very well for me, within the database the date is displayed as a julian date and when I want to show it in a grid it shows up as a gregorian date. All of this works fine for me.

I have an issue with my filtering request tho, because for filtering I need to send the time frame as a julian date. What I do at the moment is that I transfer the date typed in by the user (in the gregorian format) into the format of the julian date and then I send the request with the julian date format towards the sqlite database. This doesnt actually work for me tho, I have a formula I found but I cant get it to work proper. It always is off the actual date.

So my question is, is there an easy way to send a time filter request towards the sqlite database. Without writing a own function for the transformation of the date formats?
I need to be able to filter to milliseconds aswell.

I hope you guys understand what I want to achieve, if not I am happy to further elaborate or send some of my code.

Best Regards 

[taazz]

in datetime fields you never use equal date comparison because the time portion is expected to much too. You usually compare with >= today and < tomorrow to get everything for today.

[Zvoni]

And people always laugh at me, when i say i store DateTime as Double in my DB's.
The integer part being the Date, the fractional part being the time of that day.......
What's easier?
Calculating Double's against each other and Formating this Double to a Date for displaying, or the other way round?

[taazz]

same thing with doubles as well you either compare >= 10.0 and < 11 or trunc(a) = 10 it changes nothing when comparing except its a bit easier to edit and print a datetime field.

[Zvoni]

taazz, i know that.
But considering, SQLite doesn't have a "native" datetime-type (it's kind of a weird Text-Type), i find it much easier to store it as double (or integer if i don't need time, just days), and then format it for displaying while using the raw Double/Integer from the DB to calculate what i need.

Especially, if you remember that all those Date-Functions (like "now") return Type TDateTime which is an alias for Double.
https://www.freepascal.org/docs-html/rtl/system/tdatetime.html

[engkin]

@phr0x, just curious, why are you using Julian dates?

[phr0x]

@phr0x, just curious, why are you using Julian dates?

To be honest I just saved it with the code I have posted and that's how it came out.
I am fairly new to pascal and sqlite. is there other ways to save date and time up to milliseconds?

I have tried it with using a string but that didnt work out for me at all.

[engkin]

I am fairly new to pascal and sqlite. is there other ways to save date and time up to milliseconds?

Yes, use TDateTime:

Code: Pascal  [Select][+][-]

1. program Project1;
2.  
3. {$mode objfpc}{$H+}
4.  
5. uses
6.   dateutils
7.   { you can add units after this };
8.  
9. var
10.   dt:TDateTime;
11.   YYYY, MM, DD, HH, Min, Sec, mSec: Word;
12. begin
13.   dt := EncodeDateTime( 2018 { year } ,
14.                         7  { month } ,
15.                         26 { day },
16.                         12 { hour },
17.                         34 { min },
18.                         56 { sec } ,
19.                         78 { millisec } );
20.   DecodeDateTime(dt,YYYY,MM,DD,HH,Min,Sec,mSec);
21.   WriteLn(YYYY,'.',MM,'.',DD,' - ',HH,':',Min,':',Sec,' ',mSec,'/1000');
22.   ReadLn;
23. end.

[BrunoK]

If you work at the millisecond level, it would be better that you separate TDateTime in 2 fields.

Code: Pascal  [Select][+][-]

1. procedure ?
2. var
3.   lDateTime := now;
4. begin
5.   // etc ...
6.   Query1.SQL.Text:='Insert into dbtable (Value,time) values (:PHvalue, :PHDate, :PHmSecs)';
7.   SQLQuery1.Params.Parambyname('PHvalue').AsInteger := A;
8.   lDateTime := now;
9.   SQLQuery1.Params.Parambyname('PHDate').AsInteger := Ceil(lDateTime);
10.   { Store milliseconds since midnigth }
11.   SQLQuery1.Params.Parambyname('PHmSecs').AsInteger := Ceil(frac(lDateTime)*24*60*60*1000);
12.   // etc ...
13. end;
14.  

Then the where query would be, for an interval including bounds.
Where
  ((aDateStart>=PHDate and amSecStart>=PHmSecs) and (aDateEnd<=PHDate and amSecEnd<=PHmSecs))

The reason is that it is imposible to store EXACTLY the fractional part of base 10 number in a base 2 number (double). There are, of course, some numbers that can be expressed as 2^^x * binary_number, such as 0.5, 0.25, 0.125 etc ... and their multiples  but 0.1, 0.2 cannot be stored in any precise way in a double. For example 13:00 has no possible exact number. (1/24*13) 1/24 (1 hour)=2^³*3  is not representable exactly in the fractional part of a double, rounding may help.
It is a bit like dividing in base 10 : 10/3 will end in 3.333333...etc...3...etc...

Very long ago I developed a reservation system and was caught off guard by that particularity.

[phr0x]

Thank you for all your answers, I will try all the things you said out.

Is it possible to use the encodedatetime function with the 'now' function? So that I can encode the date and time that's at the moment?

encodedatetime(now) is as expected an error.

Best Regards

[engkin]

Thank you for all your answers, I will try all the things you said out.

Is it possible to use the encodedatetime function with the 'now' function? So that I can encode the date and time that's at the moment?

encodedatetime(now) is as expected an error.

Best Regards

Maybe you mean DecodeDateTime?

Code: Pascal  [Select][+][-]

1. program Project1;
2.  
3. {$mode objfpc}{$H+}
4.  
5. uses
6.   dateutils, sysutils
7.   { you can add units after this };
8.  
9. var
10.   dt:TDateTime;
11.   YYYY, MM, DD, HH, Min, Sec, mSec: Word;
12. begin
13.   DecodeDateTime(Now, YYYY, MM, DD, HH, Min, Sec, mSec);
14.   WriteLn(YYYY,'.',MM,'.',DD,' - ',HH,':',Min,':',Sec,' ',mSec,'/1000');
15.   ReadLn;
16. end.

[phr0x]

Yeah Decode was what I was looking for, Still have a problem with it tho.

So my Saving routinge looks like this:

Code: Pascal  [Select][+][-]

1.  procedure TDatabase1.PRC_AbspeichernMesswert (Messung_val, CMD_Byte : integer);  //CMD Bytes :1 für Modul,2 für Strommessung, 3 für PT100, 4 für Beschleunigung, 5 für Kraft
2.  begin
3.  SQLQuery1.Close;
4.              SQLQuery1.SQL.Clear;
5.              ProgressBar1.StepIt;
6.              SQLQuery1.SQL.Text := 'INSERT INTO dbsensorreadings (Sensorwert, Einheit, Sensortyp, CommandByte, Zeit) values (:PH_val, :PH_unit, :PH_senstype,:PH_Cmd_Byte, :PH_Zeit)';
7.              // other Params come here, took them out to make it simple
8.              SQLQuery1.Params.ParamByName('PH_Zeit').AsDateTime := Now;
9.              SQLQuery1.ExecSQL;
10.              SQLTransaction1.CommitRetaining;
11.  end;  
12.  

This command will save the dates within my Database like this:

112|24|C°|Modultemperatur|15|2458337.56084822
113|24|C°|Modultemperatur|15|2458337.56144491
114|24|C°|Modultemperatur|15|2458337.56144676
115|24|C°|Modultemperatur|15|2458337.56144843

The last column is the date, it is obviously encoded. But if I run these values with the decode functions, it tells me the year is something around 8000. They should be 7th Aug 2018.

Using the 'now' function the way it is with decodedatetime does not cause any problems for me. The Problem is decoding a date that is within the Database.

The bigger picture I want to accomplish is give the user the ability to type in a gregorian date, then I want to encode that. And with the encoded Date I will be able to send a proper select Query towards the Database. But it seems like it's not saved the same way in the database as the 'now' command would save it if not used with the database.

So long story short, Using the 'now' within my application, that means decoding and encoding works fine now. The Problem is that the values within my database seem to be a different format.

I want to stress that it works with the charts and the dbgrid within my application tho, they show all the dates completely correct. I just seem to be failing at doing that conversion manually.

This is how I set up my table btw:

Code: Pascal  [Select][+][-]

1. CREATE TABLE dbsensorreadings (id INTEGER PRIMARY KEY AUTOINCREMENT, Sensorwert NUMERIC, Einheit Text,Sensortyp Text,CommandByte TEXT, Zeit DATETIME);
2.  

And this is how I decode the time

Code: Pascal  [Select][+][-]

1. procedure TDatabase1.Button1Click(Sender: TObject);
2. var
3.    dt:Tdatetime;
4.    YYYY, MM, DD, HH, Min, Sec, mSec :word;
5. begin
6.  
7.   dt :=2458337.56084822;
8.   DecodeDateTime(dt,YYYY,MM,DD,HH,Min,Sec,msec);
9. end;  
10.  

Hope I didn't confuse you guys now 

Best Regards

[440bx]

Yeah Decode was what I was looking for, Still have a problem with it tho.

So my Saving routinge looks like this:

Code: Pascal  [Select][+][-]

1.  procedure TDatabase1.PRC_AbspeichernMesswert (Messung_val, CMD_Byte : integer);  //CMD Bytes :1 für Modul,2 für Strommessung, 3 für PT100, 4 für Beschleunigung, 5 für Kraft
2.  begin
3.  SQLQuery1.Close;
4.              SQLQuery1.SQL.Clear;
5.              ProgressBar1.StepIt;
6.              SQLQuery1.SQL.Text := 'INSERT INTO dbsensorreadings (Sensorwert, Einheit, Sensortyp, CommandByte, Zeit) values (:PH_val, :PH_unit, :PH_senstype,:PH_Cmd_Byte, :PH_Zeit)';
7.              // other Params come here, took them out to make it simple
8.              SQLQuery1.Params.ParamByName('PH_Zeit').AsDateTime := Now;
9.              SQLQuery1.ExecSQL;
10.              SQLTransaction1.CommitRetaining;
11.  end;  
12.  

This command will save the dates within my Database like this:

112|24|C°|Modultemperatur|15|2458337.56084822
113|24|C°|Modultemperatur|15|2458337.56144491
114|24|C°|Modultemperatur|15|2458337.56144676
115|24|C°|Modultemperatur|15|2458337.56144843

The last column is the date, it is obviously encoded. But if I run these values with the decode functions, it tells me the year is something around 8000. They should be 7th Aug 2018.

Using the 'now' function the way it is with decodedatetime does not cause any problems for me. The Problem is decoding a date that is within the Database.

The bigger picture I want to accomplish is give the user the ability to type in a gregorian date, then I want to encode that. And with the encoded Date I will be able to send a proper select Query towards the Database. But it seems like it's not saved the same way in the database as the 'now' command would save it if not used with the database.

So long story short, Using the 'now' within my application, that means decoding and encoding works fine now. The Problem is that the values within my database seem to be a different format.

I want to stress that it works with the charts and the dbgrid within my application tho, they show all the dates completely correct. I just seem to be failing at doing that conversion manually.

This is how I set up my table btw:

Code: Pascal  [Select][+][-]

1. CREATE TABLE dbsensorreadings (id INTEGER PRIMARY KEY AUTOINCREMENT, Sensorwert NUMERIC, Einheit Text,Sensortyp Text,CommandByte TEXT, Zeit DATETIME);
2.  

And this is how I decode the time

Code: Pascal  [Select][+][-]

1. procedure TDatabase1.Button1Click(Sender: TObject);
2. var
3.    dt:Tdatetime;
4.    YYYY, MM, DD, HH, Min, Sec, mSec :word;
5. begin
6.  
7.   dt :=2458337.56084822;
8.   DecodeDateTime(dt,YYYY,MM,DD,HH,Min,Sec,msec);
9. end;  
10.  

Hope I didn't confuse you guys now 

Best Regards

Can you provide how those dates in your database are encoded ? are they the number of seconds since a particular date ?

IOW, how are those dates encoded ?... knowing that, it will probably be easy to tell you how to decode them.

[phr0x]

Can you provide how those dates in your database are encoded ? are they the number of seconds since a particular date ?

IOW, how are those dates encoded ?... knowing that, it will probably be easy to tell you how to decode them.

Well, that is kind of my problem. I cant tell you with 100% certainty, they could be julian.
But as I have written, all I do is save the time with the 'now' function, and then I send that to the db. So they should be encoded the same way, 'now' encodes them. But they are not I think.

[Zvoni]

I still say, that the real DataType in your DB your date is stored in is what throws you for a loop
https://www.sqlite.org/datatype3.html
Read Chapter 2.2 and check your Database