CODEWITHSUNDEEP
sqlitetimedifference
miloo mito
miloo mito

Reputation: 13

How to calculate time differences HH:MM in a table?

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!

Upvotes: 1

Views: 266

Answers (2)

forpas
forpas

Reputation: 164099

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

Upvotes: 2

Ryan
Ryan

Reputation: 313

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.

Upvotes: 0

Related Questions