SQLite Forum

Select entries based on date/time plus one before and one after
Login

Select entries based on date/time plus one before and one after

(1) By anonymous on 2021-12-23 10:21:18 [link] [source]

Hi,

I have some sensor data that is inserted into my db in irregular intervals. It's always a datetime and a number.

Now when it comes to showing these in a chart I do it by selecting everything within a specific time, like today.

Is there a way I can get one row before and one row after my result set so I have a "contigous" chart showing?

Right now it's a bit off if there are only like 2 entries for a single day at 10am and 12am or so.

(2) By Bill Wade (billwade) on 2021-12-23 12:47:11 in reply to 1 [source]

To get the last record before a date-time (dt) value:

select * from mytable where dt < :starttime order by dt desc limit 1;

A similar query would get the first record after a date-time value. It sounds like you already know how to get the records in the date range.

You can combine the three select statements into one with UNION. If you want a total order on the union, I suspect your final statement will look like

select * from (select ... union select ... union select ...) order by dt;

(3) By Ryan Smith (cuz) on 2021-12-23 23:09:14 in reply to 1 [link] [source]

This assumes that the row-ids are contiguous (i.e. no deletes ever happen), and that we are looking for the all the logs on 10 December 2021 (plus of course the last log before and the first log after that):

  -- SQLite version 3.35.4  [ Release: 2021-04-02 ]  on  SQLitespeed version 2.1.3.11.
================================================================================================

CREATE TABLE logtable(
  logtime NUMERIC NOT NULL UNIQUE,
  logdata TEXT
);

INSERT INTO logtable(logtime, logdata) VALUES
 ('2021-12-09 15:00:00', 'This log is earlier on the previous day - Should be ignored')
,('2021-12-09 22:00:00', 'This log is later on the previous day - Should be ignored')
,('2021-12-09 23:00:00', 'This log is the last log on the previous day, report should start here')
,('2021-12-10 01:00:00', 'This log is the earliest on the report day - Include it')
,('2021-12-10 12:00:00', 'This log is the middle on the report day - Include it')
,('2021-12-10 22:00:00', 'This log is the latest on the report day - Include it')
,('2021-12-11 01:00:00', 'This log is the earliest on the day after, the report should end here')
,('2021-12-11 05:00:00', 'This log is another log on the day after - Should be ignored')
;



WITH rowrange(minTimeID, maxTimeID) AS (
    SELECT MIN(rowid)-1, MAX(rowid)+1
      FROM logtable
     WHERE logtime >= '2021-12-10 00:00:00' AND logtime < '2021-12-11 00:00:00'
)
SELECT *
  FROM logtable, rowrange
 WHERE logtable.rowid BETWEEN rowrange.minTimeID AND rowrange.maxTimeID
;

  -- logtime              |logdata                                                                 
  -- ---------------------|------------------------------------------------------------------------
  -- 2021-12-09 23:00:00  |This log is the last log on the previous day, report should start here  
  -- 2021-12-10 01:00:00  |This log is the earliest on the report day - Include it                 
  -- 2021-12-10 12:00:00  |This log is the middle on the report day - Include it                   
  -- 2021-12-10 22:00:00  |This log is the latest on the report day - Include it                   
  -- 2021-12-11 01:00:00  |This log is the earliest on the day after, the report should end here   

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


(4) By Ryan Smith (cuz) on 2021-12-23 23:17:22 in reply to 1 [link] [source]

This one is probably fractionally slower, but it does not require the row ids to be contiguous, so even if there are deletions from the logtable, this should still work correctly:

  -- ================================================================================================

CREATE TABLE logtable(
  logtime NUMERIC NOT NULL UNIQUE,
  logdata TEXT
);

INSERT INTO logtable(logtime, logdata) VALUES
 ('2021-12-09 15:00:00', 'This log is earlier on the previous day - Should be ignored')
,('2021-12-09 22:00:00', 'This log is later on the previous day - Should be ignored')
,('2021-12-09 23:00:00', 'This log is the last log on the previous day, report should start here')
,('2021-12-10 01:00:00', 'This log is the earliest on the report day - Include it')
,('2021-12-10 12:00:00', 'This log is the middle on the report day - Include it')
,('2021-12-10 22:00:00', 'This log is the latest on the report day - Include it')
,('2021-12-11 01:00:00', 'This log is the earliest on the day after, the report should end here')
,('2021-12-11 05:00:00', 'This log is another log on the day after - Should be ignored')
;


WITH rangemin(timeID) AS (
    SELECT MAX(rowid) FROM logtable WHERE logtime <  '2021-12-10 00:00:00'
),   rangemax(timeID) AS (
    SELECT MIN(rowid) FROM logtable WHERE logtime >= '2021-12-11 00:00:00'
)
SELECT logtable.*
  FROM logtable, rangemin, rangemax
 WHERE logtable.rowid BETWEEN rangemin.timeID AND rangemax.timeID
;

  -- logtime              |logdata                                                                 
  -- ---------------------|------------------------------------------------------------------------
  -- 2021-12-09 23:00:00  |This log is the last log on the previous day, report should start here  
  -- 2021-12-10 01:00:00  |This log is the earliest on the report day - Include it                 
  -- 2021-12-10 12:00:00  |This log is the middle on the report day - Include it                   
  -- 2021-12-10 22:00:00  |This log is the latest on the report day - Include it                   
  -- 2021-12-11 01:00:00  |This log is the earliest on the day after, the report should end here   

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