1

Im working on a flight-logbook in sqlite.

The "flights"-table has the following structure:

CREATE TABLE flights (event_id INT PRIMARY KEY, date TEXT, offblock TEXT, onblock TEXT, duration TEXT;

My goal is to find a statement that i can insert into the "duration" column, so that I will have the flight duration there.

INSERT INTO flights VALUES (1, "2019-04-04", "12:00", "18:00", XXX);

The result of duration should be 06:00, like this:

SELECT duration from flights WHERE event_id = 1;

06:00

Can anyone give me a working hint how to do this in the easiest possible way?

Thanks a lot!

miloo mito
  • 13
  • 2
  • How are you going to execute the insert statement? If you are doing manually, then you can always calculate urself. If otherwise, tell us how do you plan to insert the data (Like ef etc)? – It's a trap Apr 04 '19 at 15:21
  • For now I'll do it manually and of course i could do it manually, but id like to have it automated bec sometimes I'll have to add a lot of inserts at once. And i really hope there's a way for that... – miloo mito Apr 04 '19 at 15:29

2 Answers2

2

You can do it with strftime() and time() like this:

SELECT strftime('%H:%M', time(strftime('%s','18:00') - strftime('%s','12:00'), 'unixepoch'))

which results in:

06:00
forpas
  • 160,666
  • 10
  • 38
  • 76
  • Thank you forpas, that's pretty close to what Im looking for!! :) The only problem is when the duration extends beyond a day, for example: Startime: 20:00 / Endtime: 07:10 (next day) -> correct result: 11:10. With the way above it gives me 12:50. – miloo mito Apr 04 '19 at 16:37
  • In case your result is negative, then just add 24 hours. FYI this answer is better than mine (i don't work in sqlite though) – It's a trap Apr 04 '19 at 16:38
  • You must be doing something wrong. It gives me 11:10 – forpas Apr 04 '19 at 16:38
  • Copy and paste this: `SELECT strftime('%H:%M', time(strftime('%s','07:10') - strftime('%s','20:00'), 'unixepoch'))` – forpas Apr 04 '19 at 16:39
  • You must subtract starttime from endtime and not the opposite. – forpas Apr 04 '19 at 16:43
  • my mistake! Thats exactly what i was looking for! Thanks a lot!! – miloo mito Apr 04 '19 at 16:57
0

What you want to do is pretty complex as you have a string which represents time, which there isn't an explicit type for in sqlite. It's quite complicated, but it is possible and you could do the following:

-First remove the colon from the string: how to remove characters from a string in sqlite3 database?

-Then convert this string to an int: Convert string to int inside WHERE clause of SQLITE statment

-You would need to do this for the hours and minutes separately, as ints are obviously 10 based and minutes are 60 based so you can't simply subtract them. You would do this via ths Substr(X,Y,Z) function: https://www.sqlite.org/lang_corefunc.html

-Then you would do arithmetic to subtract final - initial time for both the hours and minutes. https://www.w3resource.com/sqlite/arithmetic-operators.php

-Finally take the calculated hours, and minutes, and add a colon in between them (assuming you want the same format).

Like I said, it's kinda heavy.. but it is doable if this automation saves time in the long run. This should be enough to get you there.

Ryan
  • 314
  • 1
  • 10
  • Thanks a lot! Sounds like some work, but im positive that this would work, will try it later this evening!! Is there any other way to do it in a more simple way? Im free to change the Insert / table format/data type. Im new to sql, and i cannot believe that there's no way to simply find the differences in time. – miloo mito Apr 04 '19 at 15:54
  • Unfortunately there isn't a built in simpler method. If Sqlite had a DateTime data type there would be.. but it does not. So basically you're doing all the arithmetic yourself based on a string which only you know what it represents. I'm not sure where you're getting your data from, but if you could somehow compute this difference before you send it into your sqllite service this would be the only way to simplify it. Cheers. (ps, don't forget to mark my answer if it was helptul! I know you're new here.. so welcome :) ) – Ryan Apr 04 '19 at 16:01