How can I calculate trip duration?
(1) By David Jackson (davidjackson) on 2021-07-22 22:00:54 [link] [source]
Howdy, Considering that SQLite doesn't seem to have a data or DateTime column type. How can I calculate trip duration? The data as a started_at and ended_at timestamps. Can I use a CAST function ie SELECT CAST("2017-08-29" AS DATE);?
(2.1) By Richard Hipp (drh) on 2021-07-22 22:27:49 edited from 2.0 in reply to 1 [source]
SELECT (julianday(ended_at)-julianday(started_at)) as duration FROM ...
The duration computed this way will be in days. Multiply by 24.0 to get hours, or 86400.0 to get seconds, and so forth. Example:
SELECT (julianday('now') - julianday('2000-05-29 14:16')) AS duration; ┌──────────────────┐ │ duration │ ├──────────────────┤ │ 7724.33961798623 │ └──────────────────┘ SELECT (julianday('now') - julianday('2000-05-29 14:16'))*86400.0 AS 'duration in seconds'; ┌─────────────────────┐ │ duration in seconds │ ├─────────────────────┤ │ 667383060.794015 │ └─────────────────────┘
The SQLite project has been going for a little over 7724 days, or about 667 million seconds.
(3) By ThanksRyan on 2021-07-22 22:34:16 in reply to 1 [link] [source]
Similar thread here: https://sqlite.org/forum/forumpost/4e3a94320d893c27?t=h
(4) By ThanksRyan on 2021-07-22 22:38:15 in reply to 1 [link] [source]
Similar question for me. I think I'm close but still not there yet...
I want to know the transactions per minute based on the elapsed time:
select purchases, Duration*24*60 as minutes, TxnPerMin from ( select event, count(event) as Purchases, julianday(max(`datetime`)) - julianday(min(`datetime`)) as duration, minutes/purchases as TxnPerMin from table1 where event ='end' );
What kind of subquery do I need for this?
(5) By David Jackson (davidjackson) on 2021-07-22 22:58:53 in reply to 2.1 [link] [source]
Richard, Thank look like what I am looking for.
(6) By ThanksRyan on 2021-07-23 04:22:48 in reply to 4 [link] [source]
I think this is simpler and doesn't need a subsub query.
How many transactions per minute...
select Purchases, duration*1440, purchases/(duration*1440) as TxnPerMin from ( select event, count(event) as Purchases, julianday(max(`datetime`)) - julianday(min(`datetime`)) as duration from table1 where event ='end' );