[SOLVED] SQLite Sum() time error
lazarus / 16-Databases

----------------------------------------------------------------------
Pe3s · 2022-11-27 12:28

Hello, how was the total time of the zebu counted over 24 hours?
How can I improve the code?

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
TfGlowna.Button1Click(Sender: TObject);var  Zegar: TTime;begin 
ZQuery3.SQL.Clear;  ZQuery3.SQL.Text:= 'SELECT SUM(Ilosc_godzin) total
FROM godziny';  ZQuery3.Open;  try    Zegar:=
StrToTime(ZQuery3.FieldByName('total').AsString);    Panel2.Caption:=
FormatDateTime('[hh]:nn', Zegar, [fdoInterval]);  finally   
Zquery3.Close;  end;end;

----------------------------------------------------------------------
paweld · 2022-11-27 12:57

"ilość godzin" - what type of data is it? int or real?

----------------------------------------------------------------------
Pe3s · 2022-11-27 13:15

string

----------------------------------------------------------------------
paweld · 2022-11-27 13:23

only numeric values can be summed (int, real). show table structure
and some sample data

----------------------------------------------------------------------
Pe3s · 2022-11-27 13:49

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 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)

----------------------------------------------------------------------
af0815 · 2022-11-27 14:41

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.

----------------------------------------------------------------------
paweld · 2022-11-27 14:46

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE godziny (GodzinyID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
MiesiacID INTEGER NOT NULLCzas_rozpoczecia datetime,  Czas_zakonczenia
datetime, Uwagi VARCHAR(255)) 
and then you can add new records as follows: 
from sql:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
godziny (MiesiacID, Czas_rozpoczecia, Czas_zakonczenia, Uwagi) SELECT
11, datetime('now'), datetime('now'), 'test' or from app: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
query.SQL.Text := ' insert into godziny (MiesiacID, Czas_rozpoczecia,
Czas_zakonczenia, Uwagi) values (:mid, :cod, :cdo, :uw) ';
 query.ParamByName('mid').AsInteger := iMiesiacID;
 query.ParamByName('cod').AsDateTime := Now;
 query.ParamByName('cdo').AsDateTime := Now;
 query.ParamByName('uw').AsString := 'test';  query.ExecSQL;    
Then you will be able to add up the times without any problem: 
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
TfGlowna.Button1Click(Sender: TObject);  var    Zegar: Double;begin 
ZQuery3.SQL.Clear;  ZQuery3.SQL.Text:= 'SELECT
SUM(julianday(czas_zakonczenia)-julianday(czas_rozpoczecia)) total
FROM godziny';  ZQuery3.Open;  try    Zegar:=
ZQuery3.FieldByName('total').AsFloat;    Panel2.Caption:=
FormatDateTime('[hh]:nn', Zegar, [fdoInterval]);  finally   
Zquery3.Close;  end;end;

----------------------------------------------------------------------
Pe3s · 2022-11-27 15:41

After changing the time to REAL, TimeEdit components do not display
the time, how to format them?
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
fGlowna.ZQuery2.ParamByName('Godzina_rozpoczecia').AsDateTime:=
DateTimeToStr(TimeEdit1.Text); 
Whereas

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
fGlowna.ZQuery2.ParamByName('Godzina_rozpoczecia').AsDateTime:=
StrToDateTime(TimeEdit1.Text); shows some signs

----------------------------------------------------------------------
paweld · 2022-11-27 15:58

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

----------------------------------------------------------------------
Pe3s · 2022-11-27 16:12

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 · 2022-11-27 19:56

I made a code 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
TfGlowna.Button1Click(Sender: TObject);var  bm: TBookmark;  Total:
TTime;begin  bm:= ZQuery2.GetBookmark;  ZQuery2.DisableControls;  try 
  Total:= 0;    ZQuery2.First;  while not ZQuery2.EOF do  begin   
Total:= Total +
StrToTime(ZQuery2.FieldByName('Ilosc_godzin').AsString);   
ZQuery2.Next;  end;    Panel2.Caption:= FormatDateTime('[hh]:nn',
Total, [fdoInterval]);  finally    ZQuery2.GotoBookmark(bm);   
ZQuery2.EnableControls;  end;end;

----------------------------------------------------------------------
Zvoni · 2022-11-28 08:20

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

----------------------------------------------------------------------
Pe3s · 2022-11-28 09:13

as it will improve.  code not to show error 175 is not 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
TfGlowna.Button1Click(Sender: TObject);var  Zegar: TTime;begin 
ZQuery3.SQL.Clear;  ZQuery3.SQL.Text:= 'SELECT SUM(Ilosc_godzin) total
FROM godziny';  ZQuery3.Open;  try    Zegar:=
StrToTime(ZQuery3.FieldByName('total').AsString);    Panel2.Caption:=
FormatDateTime('[hh]:nn', Zegar, [fdoInterval]);  finally   
Zquery3.Close;  end;end;

----------------------------------------------------------------------
Zvoni · 2022-11-28 09:13

Quote from: paweld on November 27, 2022, 03:46:43 pmCode: SQL
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
godziny (MiesiacID, Czas_rozpoczecia, Czas_zakonczenia, Uwagi) SELECT
11, datetime('now'), datetime('now'), 'test' or from app: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
query.SQL.Text := ' insert into godziny (MiesiacID, Czas_rozpoczecia,
Czas_zakonczenia, Uwagi) values (:mid, :cod, :cdo, :uw) ';
 query.ParamByName('mid').AsInteger := iMiesiacID;
 query.ParamByName('cod').AsDateTime := Now;
 query.ParamByName('cdo').AsDateTime := Now;
 query.ParamByName('uw').AsString := 'test';  query.ExecSQL;    
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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
godziny (MiesiacID, Czas_rozpoczecia, Czas_zakonczenia, Uwagi) SELECT
11, datetime('now', 'localtime'), datetime('now', 'localtime'), 'test'

----------------------------------------------------------------------
Pe3s · 2022-11-28 09:19

I do not use the system time, I choose the time from the TimeEdit
component

----------------------------------------------------------------------
Zvoni · 2022-11-28 10:11

Quote from: Pe3s on November 28, 2022, 10:19:45 amI 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

----------------------------------------------------------------------
Zvoni · 2022-11-28 10:27

Short sample:
Code: SQL  [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 "tbl_test" (        "DateFrom"      TEXT,        "DateTo"      
TEXT)Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
"tbl_test" ("DateFrom", "DateTo") VALUES ('2022-11-27 08:23:45',
'2022-11-27 16:35:41');INSERT INTO "tbl_test" ("DateFrom", "DateTo")
VALUES ('2022-11-28 09:02:35', '2022-11-28 17:56:23');Code: SQL
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT strf
time('%H:%M:%S',SUM(julianday(DateTo)-Julianday(DateFrom)),'12:00:00')
AS SummedUp FROM tbl_testReturns:
SummedUp
17:05:44

----------------------------------------------------------------------
SymbolicFrank · 2022-11-28 11:31

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.

----------------------------------------------------------------------
Zvoni · 2022-11-28 11:44

Quote from: SymbolicFrank on November 28, 2022, 12:31:22 pmIn 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

----------------------------------------------------------------------
Pe3s · 2022-11-28 15:51

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
fGlowna.ZQuery2.ParamByName('Godzina_rozpoczecia').AsDateTime:=
StrToTime(TimeEdit1.Text); 
How can I fix the bug

----------------------------------------------------------------------
Zvoni · 2022-11-28 17:22

AsDateTime is not going to work, if your Datatype is Real.
Use AsFloat

----------------------------------------------------------------------
Pe3s · 2022-11-28 17:57

Bug

----------------------------------------------------------------------
Zvoni · 2022-11-28 18:12

*facepalm*

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

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

The first public property…..

----------------------------------------------------------------------
Pe3s · 2022-11-28 18:36

I read these documentation neither TimeEdit.Text nor TimeEdit.Time
does not insert time only 1.9E3

----------------------------------------------------------------------
Zvoni · 2022-11-28 19:12

Quote from: Pe3s on November 28, 2022, 07:36:29 pmI 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?

----------------------------------------------------------------------
Pe3s · 2022-11-28 19:25

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

----------------------------------------------------------------------
paweld · 2022-11-28 20:07

Example attached - I used TDateTimePicker to edit the time.
I modified too my previous post with table structure.

----------------------------------------------------------------------
Pe3s · 2022-11-29 13:33

Thank you very much for all the answers.

----------------------------------------------------------------------
Thaddy · 2022-11-29 13:38

Quote from: Pe3s on November 29, 2022, 02:33:44 pmThank 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!