# SQLite Forum

How can I calculate trip duration?

# How can I calculate trip duration?

### (1) By David Jackson (davidjackson) on 2021-07-22 22:00:54 [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 [link] [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.

### (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
``````