How can I calculate trip duration?
(1) By David Jackson (davidjackson) on 2021-07-22 22:00:54 [link]
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);? TIA, David
(2.1) By Richard Hipp (drh) on 2021-07-22 22:27:49 edited from 2.0 in reply to 1 [link]
> 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]
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
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]
Richard, Thank look like what I am looking for.
(6) By ThanksRyan on 2021-07-23 04:22:48 in reply to 4 [link]
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' ); ``` ``` Purchases,duration*1440,TxnPerMin 42767,1884.12331692874,22.6986204224219 ``` ``` 22.6986204224219*1884.12331692874 ```