SQLite Forum

How can I calculate trip duration?
Login

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);?

TIA, David

(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.

(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.

(3) By ThanksRyan on 2021-07-22 22:34:16 in reply to 1 [link] [source]

(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?

(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'
);
Purchases,duration*1440,TxnPerMin
42767,1884.12331692874,22.6986204224219
22.6986204224219*1884.12331692874