SQLite Forum

time difference between datetime value
Login

time difference between datetime value

(1) By CHERRY on 2021-11-11 09:51:14 [link] [source]

Hello,

I have a DateTime record in SQLite database.

time
11-08-2021 15:16:44
11-08-2021 17:09:22
11-09-2021 17:20:39
...
11-11-2021 09:31:54
11-11-2021 10:35:37
11-11-2021 10:45:11
11-11-2021 11:54:28
-----------------------------------------

I filter the DateTime that fall in the specifc date range by using 
string date = DateTime.Now.AddHours(-3).ToString("yyyy-MM-dd HH:mm:00Z");
                string query = $"SELECT * FROM LastRun WHERE LastUpdateTime > '{queryDate}' ";

I able to get the results as below.
time
11-11-2021 09:31:54
11-11-2021 10:35:37
11-11-2021 10:45:11
11-11-2021 11:54:28
-------------------------------------------

The next thing is I want to get the time difference among each other like below.

time                  difference
11-11-2021 09:31:54   ??
11-11-2021 10:35:37   ??
11-11-2021 10:45:11   ??
11-11-2021 11:54:28   ??

Is it possible to get the time difference like this and how I query and get the difference of the time? Any suggestion?

(2) By Mark Lawrence (mark) on 2021-11-11 10:42:09 in reply to 1 [link] [source]

Here is one possibility, using the lag() window function and the julianday() datetime function:

CREATE TABLE t1(
    time TEXT
);

INSERT INTO t1(time) VALUES
    ('11-08-2021 15:16:44'),
    ('11-08-2021 17:09:22'),
    ('11-09-2021 17:20:39'),
    ('11-11-2021 09:31:54'),
    ('11-11-2021 10:35:37'),
    ('11-11-2021 10:45:11'),
    ('11-11-2021 11:54:28')
;

WITH normal AS (
    SELECT substr(time,7,4) || '-'
        || substr(time,4,2) || '-'
        || substr(time,1,2) || ' '
        || substr(time,12) AS time
    FROM t1
)
SELECT
    time,
    lag(time, 1) OVER (ORDER BY time),
    julianday(time) - (julianday(lag(time, 1) OVER (ORDER BY time))) AS dur
FROM normal
ORDER BY time;

Which results in the following:

time                 lag(time, 1) OVER (ORDER BY time)  dur
-------------------  ---------------------------------  -------------------
2021-08-11 15:16:44  NULL                               NULL
2021-08-11 17:09:22  2021-08-11 15:16:44                0.0782175925560296
2021-09-11 17:20:39  2021-08-11 17:09:22                31.0078356484883
2021-11-11 09:31:54  2021-09-11 17:20:39                60.6744791665114
2021-11-11 10:35:37  2021-11-11 09:31:54                0.0442476850003004
2021-11-11 10:45:11  2021-11-11 10:35:37                0.00664351880550385
2021-11-11 11:54:28  2021-11-11 10:45:11                0.0481134257279336

(3) By Mark Lawrence (mark) on 2021-11-11 10:51:42 in reply to 2 [source]

I should clarify that the above dur result is in days. Multiply by 24*60*60 to get seconds.

I would also recommend learning about "bound values" in whatever language you are using to query SQLite with, instead of interpolating variables into your query string, to avoid some common security pitfalls.