Time Query towards SQLite Database

[phr0x]

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

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]

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]

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

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][+][-]

1. function TDatabase1.FCT_Zeitumrechnung(Jahr, Monat, TTag, Stunden, Minuten, Sekunden :integer):double;
2. var
3.   B: double;
4.   A: double;
5.   JD:double;
6. begin
7.  
8.   if (Month <= 2) then
9.         begin
10.             Month := Month+12;
11.             Year  := Year-1;
12.         end;
13.   A := Int(Year/100);
14.   B := 2-A + Int(A/4);
15.   JD:=(Int(365.25 * (Year+4716)) + Int(30.6001*(Month+1)) + Tag + B + (Hours/ 24 + Minutes/ 1440 + Seconds/ 86400) - 1524.5);
16.   result := JD;
17. end;  
18.  

[Zvoni]

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]

@phr0x in Reply #11

Try to replace

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

1. SQLQuery1.Params.ParamByName('PH_Zeit').AsDateTime := Now;

by

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

1. 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]

Try 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.

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)

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]

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

1. uses
2.   ...SysUtils...
3.  
4. function DateTimeFromJulian(aJulian:Double):TDateTime;
5. begin
6.   Result:=aJulian+JulianEpoch; // JulianEpoch in SysUtils
7. end;
8.  
9. function DateTimeToJulian(aStdDateTime:TDateTime):TDateTime { or Double if you prefer};
10. begin
11.   Result:=aStdDateTime-JulianEpoch;
12. end;
13.  

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][+][-]

1.   RetrieveQuery.SQL.Text:=
2.     'select * from TheTable where PH_Zeit <= :PH_ZeitEnde';
3.   RetrieveQuery.Params.ParamByName('PH_ZeitEnde').AsFloat := { Again, AsFloat to avoid  conversion }
4.     EncodeDateTime(aYear, aMonth, aDay, aHour, aMinute, aSecond, aMilliSecond);
5.   RetrieveQuery.Open;
6.