I am fairly new here. I hope my question is clear (feedback is greatly appreciated). I am trying to filter data using days, hours, and seconds in my code in SQLite. I know my code below did not work when applying hours and seconds, but it did work for the days. I have attached Table 1 below to demonstrate the error I am facing. Usually, the result date and time are always before the diagnosis date and time. The result and diagnosis time is not correct in Table 1. The desired goal is to filter out the dates and time that do not correspond (see Table 2). How do I write my code where it properly filters the data that show the time in the correct order? My code is shown below:

Table 1

Table 2

CREATE TABLE covid AS
SELECT *
FROM lab9both JOIN diag9both ON lab9both.PTID=diag9both.PTID AND RESULT_DATE1 BETWEEN DATE(diagdate1,'-7 day', '-23 hours') AND diagdate1 

CodePudding user response：

The dates and times are just strings, so you can concatenate a date value and a time value to get a datetime and compare it against another datetime:

SELECT *
FROM Table1
WHERE Result_Date || ' ' || Result_Time < Diagnosis_Date || ' ' || Diagnosis_Time;

Note that the space in-between is not actually needed.

See the demo.