datetime - SQLite: express the difference as days, hours, minutes between two given dates

Question

I am trying to express the difference of two given dates in days, hours, and minutes (like 1 day, 6 hours, 17 minutes.) as SQLite query output. I have entryin and entryout as datetime fields in a SQLitedatabase. I tried all combinations of julianday and strftime but still running into rough weather.

I tried strftime('%d %H:%M', julianday(entryout)-julianday(entryin)). For a row the values are 2011-11-10 11:46, and 2011-11-09 09:00. but the output is 25 14:46 instead of 01 02:46.

Can some one help me with this, or point me correct logic for this? Thanks in advance.

Answer 1

You can try something like this:

SELECT
    CAST((strftime('%s', '2011-11-10 11:46') - strftime('%s', '2011-11-09 09:00')) / (60 * 60 * 24) AS TEXT) || ' ' ||
    CAST(((strftime('%s', '2011-11-10 11:46') - strftime('%s', '2011-11-09 09:00')) % (60 * 60 * 24)) / (60 * 60) AS TEXT) || ':' ||
    CAST((((strftime('%s', '2011-11-10 11:46') - strftime('%s', '2011-11-09 09:00')) % (60 * 60 * 24)) % (60 * 60)) / 60 AS TEXT);