Time Query towards SQLite Database lazarus / 16-Databases ---------------------------------------------------------------------- phr0x · 2018-07-26 07:17 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 thisCode: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SQLQuery1.SQL.Text:='Insert into dbtable (Value,time) values (:PHvalue, :PHtime);SQLQuery1.Params.Parambyname('PHvalue').AsInteger := A;SQLQuery1.Params.Parambyname('PHtime').AsDateTime :=Now;  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 · 2018-07-26 08:05 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 · 2018-07-26 09:53 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 · 2018-07-26 10:17 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 · 2018-07-26 13:26 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 · 2018-07-26 17:34 @phr0x, just curious, why are you using Julian dates? ---------------------------------------------------------------------- phr0x · 2018-07-26 21:36 Quote from: engkin on July 26, 2018, 07:34:16 pm@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 · 2018-07-27 03:28 Quote from: phr0x on July 26, 2018, 11:36:04 pmI 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i program Project1; {$mode objfpc}{$H+} uses  dateutils  { you can add units after this }; var  dt:TDateTime;  YYYY, MM, DD, HH, Min, Sec, mSec: Word;begin  dt := EncodeDateTime( 2018 { year } ,                      7  { month } ,                        26 { day },                      12 { hour },                        34 { min },                      56 { sec } ,                        78 { millisec } );  DecodeDateTime(dt,YYYY,MM,DD,HH,Min,Sec,mSec);  WriteLn(YYYY,'.',MM,'.',DD,' - ',HH,':',Min,':',Sec,' ',mSec,'/1000');  ReadLn;end. ---------------------------------------------------------------------- BrunoK · 2018-07-27 13:13 If you work at the millisecond level, it would be better that you separate TDateTime in 2 fields. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure ?var  lDateTime := now;begin  // etc ...  Query1.SQL.Text:='Insert into dbtable (Value,time) values (:PHvalue, :PHDate, :PHmSecs)';  SQLQuery1.Params.Parambyname('PHvalue').AsInteger := A;  lDateTime := now;  SQLQuery1.Params.Parambyname('PHDate').AsInteger := Ceil(lDateTime);  { Store milliseconds since midnigth }  SQLQuery1.Params.Parambyname('PHmSecs').AsInteger := Ceil(frac(lDateTime)*24*60*60*1000);  // etc ...end;  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*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 · 2018-08-06 18:14 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 · 2018-08-06 19:35 Quote from: phr0x on August 06, 2018, 08:14:45 pmThank 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i program Project1; {$mode objfpc}{$H+} uses  dateutils, sysutils  { you can add units after this }; var  dt:TDateTime;  YYYY, MM, DD, HH, Min, Sec, mSec: Word;begin  DecodeDateTime(Now, YYYY, MM, DD, HH, Min, Sec, mSec);  WriteLn(YYYY,'.',MM,'.',DD,' - ',HH,':',Min,':',Sec,' ',mSec,'/1000');  ReadLn;end. ---------------------------------------------------------------------- phr0x · 2018-08-06 23:43 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  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 begin SQLQuery1.Close;             SQLQuery1.SQL.Clear;             ProgressBar1.StepIt;             SQLQuery1.SQL.Text := 'INSERT INTO dbsensorreadings (Sensorwert, Einheit, Sensortyp, CommandByte, Zeit) values (:PH_val, :PH_unit, :PH_senstype,:PH_Cmd_Byte, :PH_Zeit)';              // other Params come here, took them out to make it simple             SQLQuery1.Params.ParamByName('PH_Zeit').AsDateTime := Now;           SQLQuery1.ExecSQL;             SQLTransaction1.CommitRetaining; end; 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TABLE dbsensorreadings (id INTEGER PRIMARY KEY AUTOINCREMENT, Sensorwert NUMERIC, Einheit Text,Sensortyp Text,CommandByte TEXT, Zeit DATETIME);  And this is how I decode the time Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TDatabase1.Button1Click(Sender: TObject);var   dt:Tdatetime;   YYYY, MM, DD, HH, Min, Sec, mSec :word;begin   dt :=2458337.56084822;  DecodeDateTime(dt,YYYY,MM,DD,HH,Min,Sec,msec);end; Hope I didn't confuse you guys now  Best Regards ---------------------------------------------------------------------- 440bx · 2018-08-07 00:26 Quote from: phr0x on August 07, 2018, 01:43:57 amYeah Decode was what I was looking for, Still have a problem with it tho. So my Saving routinge looks like this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  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 begin SQLQuery1.Close;             SQLQuery1.SQL.Clear;             ProgressBar1.StepIt;             SQLQuery1.SQL.Text := 'INSERT INTO dbsensorreadings (Sensorwert, Einheit, Sensortyp, CommandByte, Zeit) values (:PH_val, :PH_unit, :PH_senstype,:PH_Cmd_Byte, :PH_Zeit)';              // other Params come here, took them out to make it simple             SQLQuery1.Params.ParamByName('PH_Zeit').AsDateTime := Now;           SQLQuery1.ExecSQL;             SQLTransaction1.CommitRetaining; end; 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TABLE dbsensorreadings (id INTEGER PRIMARY KEY AUTOINCREMENT, Sensorwert NUMERIC, Einheit Text,Sensortyp Text,CommandByte TEXT, Zeit DATETIME);  And this is how I decode the time Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TDatabase1.Button1Click(Sender: TObject);var   dt:Tdatetime;   YYYY, MM, DD, HH, Min, Sec, mSec :word;begin   dt :=2458337.56084822;  DecodeDateTime(dt,YYYY,MM,DD,HH,Min,Sec,msec);end; 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 · 2018-08-07 05:07 QuoteCan 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 · 2018-08-07 05:55 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 ---------------------------------------------------------------------- phr0x · 2018-08-07 07:24 QuoteI 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 Yeah I get what you are saying, but how can the actual values within my db be wrong if my Dbgrid and TChart display them correctly. A value that is supposed to be 7th Aug 2018 17:00:00 gets displayed correctly in chart and grid. I tried all other ways to save dates before I cam to using them the way I do now. This is the one that is the least complicated concerning filtering and displaying stuff in chart etc. I just use the DBchartsource thing and everything works out. Seperating it into two columns will just give me problems displaying it within my Charts, at least that is what happened last time I tried that. Correct me if there is an easy way to do this. Best Regards Edit: Or do you mean I just have to change the column from type datetime to double and everything will work the same? ---------------------------------------------------------------------- Zvoni · 2018-08-07 07:42 Not necessarily. Have you tried to do a corresponding SELECT-Query to what your DBGrid etc. are doing? Please note the date/time-Functions of SQLite linked in that chapter. There are functions to reformat Julian-Dates, UNIXTIME etc. What i mean is: Try to write a SELECT-Query using those Date/Time- Functions of SQLite until you get the same result as with your DBGrid. That way, you will know if it's Julian-Dates, UNIXTIME or whatever in your DB as raw data. Then you'll know how to reformat your NOW-Function when inserting into the recordset ---------------------------------------------------------------------- phr0x · 2018-08-07 08:41 QuoteNot necessarily. Have you tried to do a corresponding SELECT-Query to what your DBGrid etc. are doing? Please note the date/time-Functions of SQLite linked in that chapter. There are functions to reformat Julian-Dates, UNIXTIME etc. What i mean is: Try to write a SELECT-Query using those Date/Time- Functions of SQLite until you get the same result as with your DBGrid. That way, you will know if it's Julian-Dates, UNIXTIME or whatever in your DB as raw data. Then you'll know how to reformat your NOW-Function when inserting into the recordset Oh yeah I did not write that earlier, I took the values and put them into a online Converter for Julian Date to Gregorian. That worked out perfectly. So I wrote a function that converts them but after trying a few things out I still dont get it to work properly. I only did it to seconds for now. I have tried a couple of these equations but none seem to actually work, they all are off just a litte bit. Function looks like this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i function TDatabase1.FCT_Zeitumrechnung(Jahr, Monat, TTag, Stunden, Minuten, Sekunden :integer):double;var  B: double;  A: double;  JD:double;begin   if (Month <= 2) then        begin            Month := Month+12;            Year  := Year-1;        end;  A := Int(Year/100);  B := 2-A + Int(A/4);  JD:=(Int(365.25 * (Year+4716)) + Int(30.6001*(Month+1)) + Tag + B + (Hours/ 24 + Minutes/ 1440 + Seconds/ 86400) - 1524.5);  result := JD;end; ---------------------------------------------------------------------- Zvoni · 2018-08-07 08:57 I think you misunderstood me: Use the SQLite-Date/Time-Functions directly within your SELECT-Statement!! I'd go about it like this: 1) SELECT MyDate (raw Data - what it looks like in your DB when you use SQLite-Studio or whatever) 2) SELECT JulianDay(MyDate) 3) SELECT Datetime(MyDate, 'unixepoch') etc. etc. I wouldn't even do it from your Laz-Program, but from a SQLite- Workbench-Tool (like SQLite-Studio or whatever) ---------------------------------------------------------------------- BrunoK · 2018-08-07 09:21 @phr0x in Reply #11 Try to replace Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SQLQuery1.Params.ParamByName('PH_Zeit').AsDateTime := Now;by Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SQLQuery1.Params.ParamByName('PH_Zeit').AsFloat := Now; Because setting the parameter in sqlite3conn.pp, Procedure SQLite3Cursor.bindparams(AParams : TParams); for some (strange ?) reason offsets to JulianEpoch before storing the data. In sqlite3conn.pp,  TSQLite3Connection.LoadField(...) they hack to bring a Julian date to an adjusted TDateTime. That's why the date shows correctly in your DBGrid. ---------------------------------------------------------------------- phr0x · 2018-08-07 10:15 QuoteTry to replace Code: Pascal  [Select] SQLQuery1.Params.ParamByName('PH_Zeit').AsDateTime := Now; by Code: Pascal  [Select] SQLQuery1.Params.ParamByName('PH_Zeit').AsFloat := Now; Because setting the parameter in sqlite3conn.pp, Procedure SQLite3Cursor.bindparams(AParams : TParams); for some (strange ?) reason offsets to JulianEpoch before storing the data. In sqlite3conn.pp,  TSQLite3Connection.LoadField(...) they hack to bring a Julian date to an adjusted TDateTime. That's why the date shows correctly in your DBGrid. Will try this out. QuoteI think you misunderstood me: Use the SQLite-Date/Time-Functions directly within your SELECT-Statement!! I'd go about it like this: 1) SELECT MyDate (raw Data - what it looks like in your DB when you use SQLite-Studio or whatever) 2) SELECT JulianDay(MyDate) 3) SELECT Datetime(MyDate, 'unixepoch') etc. etc. I wouldn't even do it from your Laz-Program, but from a SQLite- Workbench-Tool (like SQLite-Studio or whatever) Okay now I get where you are going with this, the format is definetly julian day. But now I don't seem to find how I convert from Julian day to gregorian and the other way around did this sqlite> select Datetime(2458337.5608481,'julianday'); Which doesnt make much sense. Sorry for being a bit slow on this . ---------------------------------------------------------------------- BrunoK · 2018-08-07 11:16 Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i uses  ...SysUtils... function DateTimeFromJulian(aJulian:Double):TDateTime;begin  Result:=aJulian+JulianEpoch; // JulianEpoch in SysUtilsend; function DateTimeToJulian(aStdDateTime:TDateTime):TDateTime { or Double if you prefer}; begin  Result:=aStdDateTime-JulianEpoch;end;  ALL THIS IS UNTESTED. I have limited knowledge of SQLite3, except I think it is realy good for many purposes. Anyway, it looks it would be simpler to standardize your dates so they match usual usage in Lazarus and that would mean when you set the DateTime field with SQLQuery1.Params.ParamByName('PH_Zeit').AsFloat := Now; // AsFloat doesn't do any offseting to Julian Double Your RetrieveQuery would be, for example Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   RetrieveQuery.SQL.Text:=    'select * from TheTable where PH_Zeit <= :PH_ZeitEnde';  RetrieveQuery.Params.ParamByName('PH_ZeitEnde').AsFloat := { Again, AsFloat to avoid  conversion }    EncodeDateTime(aYear, aMonth, aDay, aHour, aMinute, aSecond, aMilliSecond);  RetrieveQuery.Open;