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 [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 [link] [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.