Calculating difference between datetime stamp in sqlite

Question

I want to calculate the difference between two columns containing datetime stamps in db browser SQLite, I want the answers in minutes, and it keeps returning "Null". Please what could be the reason and how can I solve it? I tried using this;

SELECT started_at, ended_at, (strftime('%M','ended_at') - strftime('%M','started_at'))as duration FROM citi1;

Answer 1

You have 'started_at' and 'ended_at' which are string literals and not identifiers and SQLite returns null when you use them in strftime().

But, even if you remove the single quotes you will not get the timestamp difference, because subtracting only the minutes parts of 2 timestamps does not return their difference.
For example, the difference that you would get for started_at = '2022-03-31 13:15:00' and ended_at = '2022-03-31 14:00:00' would be -15 (= 0 - 15).

Use strftime('%s', some_date) which returns the number of seconds since 1970-01-01 00:00:00 for both timestamps, subtract and divide by 60 to get the correct difference in minutes:

SELECT started_at, ended_at, 
       (strftime('%s', ended_at) - strftime('%s', started_at)) / 60 AS duration 
FROM citi1;

See the demo.