# SQLite Forum

Calculating time between two dates

# Calculating time between two dates

### (1) By birdman518 on 2022-01-07 12:56:45 [link] [source]

Given something like this:
create table concerts (
id integer not null,
artist text not null,
concertDate text not null
);
insert into concerts values(100, 'FOO FIGHTERS', '2019-03-22');
insert into concerts values(101, 'JOE COCKER', '2007-03-22');
insert into concerts values(102, 'ELO', '1998-04-18');
insert into concerts values(103, 'YES', '2003-11-02');

sqlite> select * from concerts order by concertDate asc;
id          artist      concertDate
----------  ----------  -----------
102         ELO         1998-04-18
103         YES         2003-11-02
101         JOE COCKER  2007-03-22
100         FOO FIGHTE  2019-03-22

I want to ask the question, how long was it between the most
recent concert and the second most recent?

IOW, from 2007-03-22 to 2019-03-22

I guess more generally I could have it calculate the dates between each row and the one prior to it.

I know I can do this with Python but is there a way to create a SQL query?

Thanks,

Mitch

### (2) By anonymous on 2022-01-07 14:00:47 in reply to 1 [link] [source]

This might be a good place to start:

Date & time functions:

https://sqlite.org/lang_datefunc.html

With the functions there, you should be able to compute the time intervals in whatever units you want.

### (3) By anonymous on 2022-01-07 14:04:45 in reply to 1 [link] [source]

Adding one last record just one day later than your last record should give some confidence in correct calculation:

sqlite> .mode box
sqlite> insert into concerts values(999, 'ONE', '2019-03-23');
sqlite> select *
...> ,julianday(concertDate) as JDay
...> ,julianday(concertDate) -  lag(julianday(concertDate),1) over (order by concertDate rows between 1 preceding and 1 preceding) as DeltaDays
...> from concerts
...> ;
┌─────┬──────────────┬─────────────┬───────────┬───────────┐
│ id  │    artist    │ concertDate │   JDay    │ DeltaDays │
├─────┼──────────────┼─────────────┼───────────┼───────────┤
│ 102 │ ELO          │ 1998-04-18  │ 2450921.5 │           │
│ 103 │ YES          │ 2003-11-02  │ 2452945.5 │ 2024.0    │
│ 101 │ JOE COCKER   │ 2007-03-22  │ 2454181.5 │ 1236.0    │
│ 100 │ FOO FIGHTERS │ 2019-03-22  │ 2458564.5 │ 4383.0    │
│ 999 │ ONE          │ 2019-03-23  │ 2458565.5 │ 1.0       │
└─────┴──────────────┴─────────────┴───────────┴───────────┘
sqlite>

### (4) By Ryan Smith (cuz) on 2022-01-07 16:35:44 in reply to 1 [link] [source]

There's a lot of ways to achieve this, mostly through some form of using the Julian-day calculation as pointed out by the previous posts, but you could as easily use Unix seconds strftime('%s',somedate) or whatever function returns a date/time value as a proportional scalar number.

Here are some few examples of different ways to do it:

-- SQLite version 3.35.4  [ Release: 2021-04-02 ]  on  SQLitespeed version 2.1.3.11.
-- ================================================================================================
-- First, just some setup of the demo data, included for completeness only
create table concerts (
id          INT     not null,
artist      TEXT    not null,
concertDate NUMERIC not null -- PS: "NUMERIC" is better, but TEXT works too.
);

insert into concerts values
(100, 'FOO FIGHTERS', '2019-03-22')
,(101, 'JOE COCKER',   '2007-03-22')
,(102, 'ELO',          '1998-04-18')
,(103, 'YES',          '2003-11-02')
;

SELECT * FROM concerts;

--      id     |artist        |concertDate
-- ------------|--------------|------------
--      100    |FOO FIGHTERS  |2019-03-22
--      101    |JOE COCKER    |2007-03-22
--      102    |ELO           |1998-04-18
--      103    |YES           |2003-11-02

-- Example 1 - Isolating the latest and previous items in sub queries
-- and correlated sub queries:
SELECT PD.latest, PD.prev
FROM (
SELECT LD.latest, (SELECT MAX(concertDate) FROM concerts WHERE concertDate < LD.latest) AS prev
FROM (SELECT MAX(concertDate) AS latest FROM concerts) AS LD
) AS PD
;

-- latest      |prev
-- ------------|------------
-- 2019-03-22  |2007-03-22

-- Example 2: Doing the same, but translating it to time-spans:
SELECT PD.latest, PD.prev,
(julianday(PD.latest)-julianday(PD.prev)) AS Δ_days,
(julianday(PD.latest)-julianday(PD.prev))/30.437 AS Δ_months,
(julianday(PD.latest)-julianday(PD.prev))/365.25 AS Δ_years
FROM (
SELECT LD.latest, (SELECT MAX(concertDate) FROM concerts WHERE concertDate < LD.latest) AS prev
FROM (SELECT MAX(concertDate) AS latest FROM concerts) AS LD
) AS PD
;

-- latest      |prev        |   Δ_days   |            Δ_months|   Δ_years
-- ------------|------------|------------|--------------------|------------
-- 2019-03-22  |2007-03-22  |   4383.0   |    144.002365541939|    12.0

-- Example 3: Use a window function in case you are interested in ALL the
-- time-spans between concerts, not just the latest:
SELECT id, artist, concertDate,
MIN(concertDate) OVER prev AS previousConcert,
julianday(concertDate) - julianday(MIN(concertDate) OVER prev) AS delta
FROM concerts
WINDOW prev AS (ORDER BY concertDate ASC ROWS 1 PRECEDING)
;

--      |              |            |previousCon-|
--   id |artist        |concertDate |cert        |  delta
-- -----|--------------|------------|------------|--------
--  102 |ELO           |1998-04-18  |1998-04-18  |   0.0
--  103 |YES           |2003-11-02  |1998-04-18  | 2024.0
--  101 |JOE COCKER    |2007-03-22  |2003-11-02  | 1236.0
--  100 |FOO FIGHTERS  |2019-03-22  |2007-03-22  | 4383.0

-- ------------------------------------------------------------------------------------------------

Thanks to all...

### (6) By Holger J (holgerj) on 2022-01-08 15:29:13 in reply to 1 [source]

I'd say that Window functions would be the best way to do this. With LAG() and LEAD() you can access data from the previous and next row.