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 

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

(5) By birdman518 on 2022-01-07 18:23:49 in reply to 1 [link] [source]

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.