Recent

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

Zvoni

  • Hero Member
  • *****
  • Posts: 1604
Re: SQLite Sum() time error
« Reply #15 on: November 28, 2022, 11:11:09 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
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

Zvoni

  • Hero Member
  • *****
  • Posts: 1604
Re: SQLite Sum() time error
« Reply #16 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
« Last Edit: November 28, 2022, 11:30:33 am by Zvoni »
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

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1147
Re: SQLite Sum() time error
« Reply #17 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.

Zvoni

  • Hero Member
  • *****
  • Posts: 1604
Re: SQLite Sum() time error
« Reply #18 on: November 28, 2022, 12:44:33 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
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: 330
Re: SQLite Sum() time error
« Reply #19 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
« Last Edit: November 28, 2022, 05:36:17 pm by Pe3s »

Zvoni

  • Hero Member
  • *****
  • Posts: 1604
Re: SQLite Sum() time error
« Reply #20 on: November 28, 2022, 06:22:55 pm »
AsDateTime is not going to work, if your Datatype is Real.
Use AsFloat
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: 330
Re: SQLite Sum() time error
« Reply #21 on: November 28, 2022, 06:57:34 pm »
Bug

Zvoni

  • Hero Member
  • *****
  • Posts: 1604
Re: SQLite Sum() time error
« Reply #22 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…..
« Last Edit: November 28, 2022, 07:15:59 pm by Zvoni »
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: 330
Re: SQLite Sum() time error
« Reply #23 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


Zvoni

  • Hero Member
  • *****
  • Posts: 1604
Re: SQLite Sum() time error
« Reply #24 on: November 28, 2022, 08:12:17 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?
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: 330
Re: SQLite Sum() time error
« Reply #25 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


paweld

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

Pe3s

  • Sr. Member
  • ****
  • Posts: 330
Re: SQLite Sum() time error
« Reply #27 on: November 29, 2022, 02:33:44 pm »
Thank you very much for all the answers.

Thaddy

  • Hero Member
  • *****
  • Posts: 12623
Re: SQLite Sum() time error
« Reply #28 on: November 29, 2022, 02:38:49 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!
« Last Edit: November 29, 2022, 02:59:08 pm by Thaddy »
The only thing I can say about Putin - born st Petersburg- is that he is indeed Russian, as opposed to Stalin, who was Georgian. Depending of historical time frame they could both be Lithuanian or Polish...even German. Shut him up!

 

TinyPortal © 2005-2018