DATETIME devide by Float

(1) By JohnML (195710) on 2024-01-06 16:24:08 [link] [source]

I have a sqlite3 table with two fields:

"Zeit_gelaufen", Datatyp DateTime, the data has only the time like "hh:mm:ss" "km_gelaufen", Datatyp Numeric(5,2) value like 1.7

This SQL code is not working:

Select Zeit_gelaufen/km_gelaufen From laufdaten1

I expected this:

00:11:14 / 1.7 = 00:06:36

Whats wronge?

(2) By anonymous on 2024-01-06 19:06:19 in reply to 1 [link] [source]

What do you get with this sql:

select typeof(Zeit_gelaufen), typeof(km_gelaufen) from yourtable limit 1

of course replace tablename yourtable with your table name.

(3) By JohnML (195710) on 2024-01-06 23:25:46 in reply to 2 [link] [source]

SQLite Studio (3.4.4) tells me:

text;real

(4) By JohnML (195710) on 2024-01-06 23:43:03 in reply to 2 [link] [source]

In Sqlite IRC someone told me:

Convert DateTime "Zeit_gelaufen" to seconds, do the calculation and afterwards convert back to hh:mm:ss

Sounds logic but i have no idea which sqlite functions to use for that :-(

(5) By Larry Brasfield (larrybr) on 2024-01-07 01:51:00 in reply to 4 [link] [source]

On the SQLite home page, look for "Date and time functions".

(6) By Spindrift (spindrift) on 2024-01-07 09:03:03 in reply to 1 [source]

"What is wrong" is that you are trying to divide a string value by a number.

The underlying false assumption is that sqlite is thinking of your time as a numeric quantity, but it is not doing this. It is just a string.

The advice that you were given to convert this to a number of seconds is wise, as it would then be a numeric quantity and can be divided sensibly.

SQLite does have sufficient datetime functions to achieve this, they are easily found with a simple Google search. They are slightly more difficult to use for your situation, so I will give you an example below.

SELECT TIME((
unixepoch('00:17:35') -- convert your query to seconds
 - unixepoch ('00:00:00') -- remove basis to give absolute value
) / 7.0, -- perform division, note decimal to force float (unneeded but probably what you are expecting)
'unixepoch') -- ensure interpreted as seconds not Julian day

Do note that times must be rigidly in HH:MM or HH:MM:SS format with a leading zero where needed.

Your initial query therefore would be:

SELECT TIME((unixepoch('00:11:14')-unixepoch('00:00:00'))/1.7,'unixepoch');

(7) By JohnML (195710) on 2024-01-07 10:44:42 in reply to 6 [link] [source]

... and it works, thank you :-)

Tonight i was thinking about doing it by: extract hours, minutes and seconds with three calls of substring. Multiply hours with 3600, minutes with 60 add seconds . Sum these three values and devide it by 1.7. But how to convert tis uge integer back to "hh:mm:ss" ??

Because of your nice answer, i don't need that anymore :-)

Thanks again

(8) By Spindrift (spindrift) on 2024-01-07 12:40:56 in reply to 7 [link] [source]

Thank heavens, I'm very glad I replied, that sounds horrible 😉

Beware that using datetime functions this way hides certain assumptions - I would want to test extensively if your time might ever exceed 23:59:59 for example, or be negative. I presume this is not a concern for you, but just bear in mind.

(9) By JohnML (195710) on 2024-01-07 13:13:20 in reply to 8 [link] [source]

It is just a table to write a diary of my weekly jogging dates with time per kilometer, run distance , average time per km ... and all that in a defined date range.

So no life is in danger :-)

I had this diary until now in a working google sheets calculation.

Since i'm new to SQlite i have lots to learn and your code is good stuff for that.

Thanks again ...