Lazarus

Programming => Databases => Topic started by: Pe3s on November 27, 2022, 01:28:58 pm

Title: [SOLVED] SQLite Sum() time error
Post by: Pe3s on November 27, 2022, 01:28:58 pm
Hello, how was the total time of the zebu counted over 24 hours?
How can I improve the code?

Code: Pascal  [Select][+][-]
  1. procedure TfGlowna.Button1Click(Sender: TObject);
  2. var
  3.   Zegar: TTime;
  4. begin
  5.   ZQuery3.SQL.Clear;
  6.   ZQuery3.SQL.Text:= 'SELECT SUM(Ilosc_godzin) total FROM godziny';
  7.   ZQuery3.Open;
  8.   try
  9.     Zegar:= StrToTime(ZQuery3.FieldByName('total').AsString);
  10.     Panel2.Caption:= FormatDateTime('[hh]:nn', Zegar, [fdoInterval]);
  11.   finally
  12.     Zquery3.Close;
  13.   end;
  14. end;
  15.  
Title: Re: SQLite Sum() time error
Post by: paweld on November 27, 2022, 01:57:34 pm
"ilość godzin" - what type of data is it? int or real?
Title: Re: SQLite Sum() time error
Post by: Pe3s on November 27, 2022, 02:15:18 pm
string
Title: Re: SQLite Sum() time error
Post by: paweld on November 27, 2022, 02:23:12 pm
only numeric values can be summed (int, real). show table structure and some sample data
Title: Re: SQLite Sum() time error
Post by: Pe3s on November 27, 2022, 02:49:08 pm
Code: Pascal  [Select][+][-]
  1. CREATE TABLE godziny(Data VARCHAR(10), Godzina_rozpoczecia VARCHAR(5), Godzina_zakonczenia VARCHAR(5), Ilosc_godzin VARCHAR(6), Uwagi VARCHAR(255), GodzinyID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, MiesiacID INTEGER NOT NULL)
  2.  
Title: Re: SQLite Sum() time error
Post by: af0815 on November 27, 2022, 03:41:09 pm
As paweld says, it must be a integer or real value stored in the database. ilość godzin mean sum of hours is google translate is right.

If you store hours (as int) and part of hours (real, float) it is not compatible with datatype Time nor with DateTime. You have to convert this explicit to get TDateTime or Time values.

And to store numeric values in string is not a good idea, becaus you cannot work with a lot of built in functions of the Database server.
Title: Re: SQLite Sum() time error
Post by: paweld on November 27, 2022, 03:46:43 pm
With such a structure, it will be difficult to achieve. I suggest you change the structure of this table to the following 
Code: SQL  [Select][+][-]
  1. CREATE TABLE godziny (
  2. GodzinyID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  3. MiesiacID INTEGER NOT NULL
  4. Czas_rozpoczecia datetime,  
  5. Czas_zakonczenia datetime,
  6. Uwagi VARCHAR(255))
 
and then you can add new records as follows: 
from sql:
Code: SQL  [Select][+][-]
  1. INSERT INTO godziny (MiesiacID, Czas_rozpoczecia, Czas_zakonczenia, Uwagi) SELECT 11, datetime('now'), datetime('now'), 'test'
  2.  
or from app:
Code: Pascal  [Select][+][-]
  1. query.SQL.Text := ' insert into godziny (MiesiacID, Czas_rozpoczecia, Czas_zakonczenia, Uwagi) values (:mid, :cod, :cdo, :uw) ';  
  2. query.ParamByName('mid').AsInteger := iMiesiacID;  
  3. query.ParamByName('cod').AsDateTime := Now;  
  4. query.ParamByName('cdo').AsDateTime := Now;  
  5. query.ParamByName('uw').AsString := 'test';  
  6. query.ExecSQL;  
  7.  
 
Then you will be able to add up the times without any problem: 
Code: Pascal  [Select][+][-]
  1. procedure TfGlowna.Button1Click(Sender: TObject);  
  2. var  
  3.   Zegar: Double;
  4. begin
  5.   ZQuery3.SQL.Clear;
  6.   ZQuery3.SQL.Text:= 'SELECT SUM(julianday(czas_zakonczenia)-julianday(czas_rozpoczecia)) total FROM godziny';
  7.   ZQuery3.Open;
  8.   try
  9.     Zegar:= ZQuery3.FieldByName('total').AsFloat;
  10.     Panel2.Caption:= FormatDateTime('[hh]:nn', Zegar, [fdoInterval]);
  11.   finally
  12.     Zquery3.Close;
  13.   end;
  14. end;
Title: Re: SQLite Sum() time error
Post by: Pe3s on November 27, 2022, 04:41:38 pm
After changing the time to REAL, TimeEdit components do not display the time, how to format them?
Code: Pascal  [Select][+][-]
  1. fGlowna.ZQuery2.ParamByName('Godzina_rozpoczecia').AsDateTime:= DateTimeToStr(TimeEdit1.Text);
  2.  

Whereas

Code: Pascal  [Select][+][-]
  1. fGlowna.ZQuery2.ParamByName('Godzina_rozpoczecia').AsDateTime:= StrToDateTime(TimeEdit1.Text);
  2.  
shows some signs
Title: Re: SQLite Sum() time error
Post by: paweld on November 27, 2022, 04:58:55 pm
in the original version of the structures you had the date and time separately. In the proposed example, they are combined, so instead of TEdit, use the TDateTimePicker controls
Title: Re: SQLite Sum() time error
Post by: Pe3s on November 27, 2022, 05:12:49 pm
Yes it is all time date and time separately.
My point is that when I select the hour from the component, I have an error.
How to convert TimeEdit1.Text to work with AsDateTime
Title: Re: SQLite Sum() time error
Post by: Pe3s on November 27, 2022, 08:56:45 pm
I made a code like this
Code: Pascal  [Select][+][-]
  1. procedure TfGlowna.Button1Click(Sender: TObject);
  2. var
  3.   bm: TBookmark;
  4.   Total: TTime;
  5. begin
  6.   bm:= ZQuery2.GetBookmark;
  7.   ZQuery2.DisableControls;
  8.   try
  9.     Total:= 0;
  10.     ZQuery2.First;
  11.   while not ZQuery2.EOF do
  12.   begin
  13.     Total:= Total + StrToTime(ZQuery2.FieldByName('Ilosc_godzin').AsString);
  14.     ZQuery2.Next;
  15.   end;
  16.     Panel2.Caption:= FormatDateTime('[hh]:nn', Total, [fdoInterval]);
  17.   finally
  18.     ZQuery2.GotoBookmark(bm);
  19.     ZQuery2.EnableControls;
  20.   end;
  21. end;
  22.  
Title: Re: SQLite Sum() time error
Post by: Zvoni on November 28, 2022, 09:20:37 am
Or you don't separate Date and Time in your Database.
If you still want to use TEXT-Storage-Class, save the DateTime's in Format "YYYY-MM-DD HH:MM:SS" (ISO-Format)

Then it's easy to get what you want by using SQL, and you can avoid jumping through hoops in your Front-End-Code
Title: Re: SQLite Sum() time error
Post by: Pe3s on November 28, 2022, 10:13:47 am
as it will improve.  code not to show error 175 is not time

Code: Pascal  [Select][+][-]
  1. procedure TfGlowna.Button1Click(Sender: TObject);
  2. var
  3.   Zegar: TTime;
  4. begin
  5.   ZQuery3.SQL.Clear;
  6.   ZQuery3.SQL.Text:= 'SELECT SUM(Ilosc_godzin) total FROM godziny';
  7.   ZQuery3.Open;
  8.   try
  9.     Zegar:= StrToTime(ZQuery3.FieldByName('total').AsString);
  10.     Panel2.Caption:= FormatDateTime('[hh]:nn', Zegar, [fdoInterval]);
  11.   finally
  12.     Zquery3.Close;
  13.   end;
  14. end;
  15.  
Title: Re: SQLite Sum() time error
Post by: Zvoni on November 28, 2022, 10:13:57 am
Quote from: paweld on November 27, 2022, 03:46:43 pm
Code: SQL  [Select][+][-]
  1. INSERT INTO godziny (MiesiacID, Czas_rozpoczecia, Czas_zakonczenia, Uwagi) SELECT 11, datetime('now'), datetime('now'), 'test'
  2.  
or from app:
Code: Pascal  [Select][+][-]
  1. query.SQL.Text := ' insert into godziny (MiesiacID, Czas_rozpoczecia, Czas_zakonczenia, Uwagi) values (:mid, :cod, :cdo, :uw) ';  
  2. query.ParamByName('mid').AsInteger := iMiesiacID;  
  3. query.ParamByName('cod').AsDateTime := Now;  
  4. query.ParamByName('cdo').AsDateTime := Now;  
  5. query.ParamByName('uw').AsString := 'test';  
  6. query.ExecSQL;  
  7.  
 
Be very careful with this code, especially the " first" INSERT (inserting Time by calling DateTime-Function)
if you use DateTime('now') in SQLite-SQL it returns UTC !!! --> https://www.sqlite.org/lang_datefunc.html
The second one is going to take System-Time of the Workstation.

correct:
Code: SQL  [Select][+][-]
  1. INSERT INTO godziny (MiesiacID, Czas_rozpoczecia, Czas_zakonczenia, Uwagi) SELECT 11, datetime('now', 'localtime'), datetime('now', 'localtime'), 'test'
  2.  
Title: Re: SQLite Sum() time error
Post by: Pe3s on November 28, 2022, 10:19:45 am
I do not use the system time, I choose the time from the TimeEdit component
Title: Re: SQLite Sum() time error
Post by: Zvoni on November 28, 2022, 11:11:09 am
Quote from: Pe3s on November 28, 2022, 10:19:45 am
I do not use the system time, I choose the time from the TimeEdit component
Your point?
Doesn't change the Fact, if your Date/Times are stored in the proper format in the SQLite-DB you can get your information directly from SQL
Title: Re: SQLite Sum() time error
Post by: Zvoni on November 28, 2022, 11:27:40 am
Short sample:
Code: SQL  [Select][+][-]
  1. CREATE TABLE "tbl_test" (
  2.         "DateFrom"      TEXT,
  3.         "DateTo"        TEXT
  4. )
Code: SQL  [Select][+][-]
  1. INSERT INTO "tbl_test" ("DateFrom", "DateTo") VALUES ('2022-11-27 08:23:45', '2022-11-27 16:35:41');
  2. INSERT INTO "tbl_test" ("DateFrom", "DateTo") VALUES ('2022-11-28 09:02:35', '2022-11-28 17:56:23');
Code: SQL  [Select][+][-]
  1. SELECT julianday(DateTo)-Julianday(DateFrom) AS FloatValue,strftime('%H:%M:%S',julianday(DateTo)-julianday(DateFrom),'12:00:00') AS StringValue FROM tbl_test

Returns:
FloatValue                   StringValue
0.341620370279998   08:11:56
0.370694444514811   08:53:48

Code: SQL  [Select][+][-]
  1. SELECT strftime('%H:%M:%S',SUM(julianday(DateTo)-Julianday(DateFrom)),'12:00:00') AS SummedUp FROM tbl_test
Returns:
SummedUp
17:05:44
Title: Re: SQLite Sum() time error
Post by: SymbolicFrank on November 28, 2022, 12:31:22 pm
In Windows, a datetime is a float, with the integer part being the date and the fractional part being the time. There are 32 and 64 bit variants. You can do math with those.
You also have Unix time, the amount of seconds from a start date (often, 1970-01-01). It (fortunately) ignores leap seconds. 32 and 64 bit variants. Usable for math as well.
Another option is the ISO DateTime: a string as Zvoni said. This is the most portable and only readable format, but you cannot do math with it.

So, the best way to store it in an SQLite database is as an ISO string. But if you want to do math with it, you have to convert it to a datetime first. You can do that in SQLite or in Free Pascal. If you want to do math to it directly, Unix time is your best bet.

Free Pascal / Lazarus uses the float format.
Title: Re: SQLite Sum() time error
Post by: Zvoni on November 28, 2022, 12:44:33 pm
Quote from: SymbolicFrank on November 28, 2022, 12:31:22 pm
In Windows, a datetime is a float, with the integer part being the date and the fractional part being the time. There are 32 and 64 bit variants. You can do math with those.
You also have Unix time, the amount of seconds from a start date (often, 1970-01-01). It (fortunately) ignores leap seconds. 32 and 64 bit variants. Usable for math as well.
Another option is the ISO DateTime: a string as Zvoni said. This is the most portable and only readable format, but you cannot do math with it.

So, the best way to store it in an SQLite database is as an ISO string. But if you want to do math with it, you have to convert it to a datetime first. You can do that in SQLite or in Free Pascal. If you want to do math to it directly, Unix time is your best bet.

Free Pascal / Lazarus uses the float format.
Close. Though, this only applies to SQLite.
Using TEXT as Datatype for the Column, you store DateTimes in ISO-Format.
Then you can do math with them in SQL directly (as shown above).
And it will respect Leap-Years.

AFAIK, all other DBMS, which support DateTime-Datatype, store it internally as a Double (64-Bit floating point).
And you can do Math on them Directly in SQL, incl. respecting Leap-Years
Title: Re: SQLite Sum() time error
Post by: Pe3s on November 28, 2022, 04:51:28 pm
How can I insert the value from the TimeEdit1 component, no conversion works, it displays the value 1.9E3. I tried to use
StrToTime
strToDateTime

Code: Pascal  [Select][+][-]
  1. fGlowna.ZQuery2.ParamByName('Godzina_rozpoczecia').AsDateTime:= StrToTime(TimeEdit1.Text);
  2.  

How can I fix the bug
Title: Re: SQLite Sum() time error
Post by: Zvoni on November 28, 2022, 06:22:55 pm
AsDateTime is not going to work, if your Datatype is Real.
Use AsFloat
Title: Re: SQLite Sum() time error
Post by: Pe3s on November 28, 2022, 06:57:34 pm
Bug
Title: Re: SQLite Sum() time error
Post by: Zvoni on November 28, 2022, 07:12:19 pm
*facepalm*

Reading the documentation is really becoming a lost art…..

https://lazarus-ccr.sourceforge.io/docs/lcl/editbtn/ttimeedit.html

The first public property…..
Title: Re: SQLite Sum() time error
Post by: Pe3s on November 28, 2022, 07:36:29 pm
I read these documentation neither TimeEdit.Text nor TimeEdit.Time does not insert time only 1.9E3

Title: Re: SQLite Sum() time error
Post by: Zvoni on November 28, 2022, 08:12:17 pm
Quote from: Pe3s on November 28, 2022, 07:36:29 pm
I read these documentation neither TimeEdit.Text nor TimeEdit.Time does not insert time only 1.9E3
Because „1.9E3“ IS a real value.
Exactly the datatype you defined in your database.

Why do people always ignore advice given by people who have already been there?
Title: Re: SQLite Sum() time error
Post by: Pe3s on November 28, 2022, 08:25:17 pm
They don't ignore, they just try to learn, some things come easier than others. Sorry for the questions.
I greet you with a smile

Title: Re: SQLite Sum() time error
Post by: paweld on November 28, 2022, 09:07:04 pm
Example attached - I used TDateTimePicker to edit the time.
I modified too my previous post with table structure.
Title: Re: SQLite Sum() time error
Post by: Pe3s on November 29, 2022, 02:33:44 pm
Thank you very much for all the answers.
Title: Re: SQLite Sum() time error
Post by: Thaddy on November 29, 2022, 02:38:49 pm
Quote from: Pe3s on November 29, 2022, 02:33:44 pm
Thank you very much for all the answers.
Just keep in mind the right order to store a date and time in a database. That is critical and I have no sign from you that you actually now know why?
Enlighten us... Otherwise we keep trying...
Plz show us some "homework", any age accepted, and try to explain if you really understand it now! I am not quite sure.
In your own words please;
1. How do you store date and time in a database in the correct order?
2. Why is that order important?
3. Explain UTC and why it is important
4. Why do display time and UTC time differ almost always? Where not?
5. (easy) Why do you never store date and time as local time?
6. Why do we store date and time as a real? (Maybe programming language differences, in Pascal it is a double.)

You can win a bottle of booze with the correct answers. Just P.M. me.
Only Pe3s, not you lot!
TinyPortal © 2005-2018