### Bookstore

 Computer Math and Games in Pascal (preview) Lazarus Handbook

### Author Topic: [SOLVED] SQLite Sum() time error  (Read 908 times)

#### Pe3s

• Sr. Member
• Posts: 327
##### [SOLVED] SQLite Sum() time error
« 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.
« Last Edit: November 29, 2022, 02:34:42 pm by Pe3s »

#### paweld

• Hero Member
• Posts: 549
##### Re: SQLite Sum() time error
« Reply #1 on: November 27, 2022, 01:57:34 pm »
"ilość godzin" - what type of data is it? int or real?
Best regards / Pozdrawiam
paweld

#### Pe3s

• Sr. Member
• Posts: 327
##### Re: SQLite Sum() time error
« Reply #2 on: November 27, 2022, 02:15:18 pm »
string
« Last Edit: November 27, 2022, 02:18:40 pm by Pe3s »

#### paweld

• Hero Member
• Posts: 549
##### Re: SQLite Sum() time error
« Reply #3 on: November 27, 2022, 02:23:12 pm »
only numeric values can be summed (int, real). show table structure and some sample data
Best regards / Pozdrawiam
paweld

#### Pe3s

• Sr. Member
• Posts: 327
##### Re: SQLite Sum() time error
« Reply #4 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.

#### af0815

• Hero Member
• Posts: 1086
##### Re: SQLite Sum() time error
« Reply #5 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.
regards
Andreas

#### paweld

• Hero Member
• Posts: 549
##### Re: SQLite Sum() time error
« Reply #6 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;
« Last Edit: November 28, 2022, 08:59:02 pm by paweld »
Best regards / Pozdrawiam
paweld

#### Pe3s

• Sr. Member
• Posts: 327
##### Re: SQLite Sum() time error
« Reply #7 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
« Last Edit: November 27, 2022, 04:47:40 pm by Pe3s »

#### paweld

• Hero Member
• Posts: 549
##### Re: SQLite Sum() time error
« Reply #8 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
Best regards / Pozdrawiam
paweld

#### Pe3s

• Sr. Member
• Posts: 327
##### Re: SQLite Sum() time error
« Reply #9 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

#### Pe3s

• Sr. Member
• Posts: 327
##### Re: SQLite Sum() time error
« Reply #10 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.

#### Zvoni

• Hero Member
• Posts: 1593
##### Re: SQLite Sum() time error
« Reply #11 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
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

#### Pe3s

• Sr. Member
• Posts: 327
##### Re: SQLite Sum() time error
« Reply #12 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.

#### Zvoni

• Hero Member
• Posts: 1593
##### Re: SQLite Sum() time error
« Reply #13 on: November 28, 2022, 10:13:57 am »
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.
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

#### Pe3s

• Sr. Member
• Posts: 327
##### Re: SQLite Sum() time error
« Reply #14 on: November 28, 2022, 10:19:45 am »
I do not use the system time, I choose the time from the TimeEdit component