Wsrtjtyk

Code:

.mode column

.width 40 20 20 6 10 90

.headers off

select name, datetime("start time","unixepoch","localtime"), datetime("end time","unixepoch","localtime"), "end time"-"start time", "completion status", "failed clients list"

from  "savegroup job"

where datetime("end time","unixepoch","localtime") >= datetime("now", "-6 hours")

and "completion status" like "%failed%";

The >= datetime("now", "-6 hours") does not work.

I need to get the last input data from the last 6 hours.

Thanks

I believe that your issue is very likely that the values in the start time and end time columns are not saved in the correct format. That is for the unixepoch modifer to apply then the values MUST be in DDDDDDDDDD format.

As per :-

The "unixepoch" modifier (11) only works if it immediately follows a
timestring in the DDDDDDDDDD format. This modifier causes the
DDDDDDDDDD to be interpreted not as a Julian day number as it normally
would be, but as Unix Time - the number of seconds since 1970. If the
"unixepoch" modifier does not follow a timestring of the form
DDDDDDDDDD which expresses the number of seconds since 1970 or if
other modifiers separate the "unixepoch" modifier from prior
DDDDDDDDDD then the behavior is undefined. For SQLite versions before
3.16.0 (2017-01-02), the "unixepoch" modifier only works for dates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (unix times of
-62167219200 through 106751991167).

SQL As Understood By SQLite - Date And Time Functions - Modifiers

For example consider the following (see comments) :-

DROP TABLE IF EXISTS 'savegroup job';

CREATE TABLE IF NOT EXISTS 'savegroup job' (name TEXT,'start time' TEXT, 'end time' TEXT, 'completion status' TEXT);

INSERT INTO 'savegroup job' VALUES



  -- store values in DDDDDDDDDD format

    ('Name001',strftime('%s','2018-01-01 10:30'),strftime('%s','2018-01-01 12:30'),'this failed'),

    ('Name002',strftime('%s','2018-02-01 10:30'),strftime('%s','2018-02-01 12:30'),'this failed'),

    ('Name003',strftime('%s','2018-03-01 10:30'),strftime('%s','2018-03-01 12:30'),'this failed'),

    ('Name004',strftime('%s','now'),strftime('%s','now','+6 hours'),'this failed'),

    ('Name005',strftime('%s','now','+3 hours'),strftime('%s','now','+14 hours'),'this failed'),



    -- store values in "YYYY-MM-DD HH:MM:SS" format 

    ('Name006','2018-01-01 10:30','2018-01-01 12:30','this failed'),

    ('Name007','2018-01-01 10:30','2018-01-01 12:30','this failed'),

    ('Name008','2018-01-01 10:30','2018-01-01 12:30','this failed'),

    ('Name009',datetime('now'),datetime('now','+6 hours'),'this failed'),

    ('Name010',datetime('now','+3 hours'),datetime('now','+14 hours'),'this failed')

;

-- Show all data

SELECT * FROM 'savegroup job';



-- The query from the question

select name, datetime("start time","unixepoch","localtime"), datetime("end time","unixepoch","localtime"), "end time"-"start time", "completion status", "failed clients list"

from  "savegroup job"

where datetime("end time","unixepoch","localtime") >= datetime("now", "-6 hours")

and "completion status" like "%failed%";

Results

All data :-

Your Query (it does work if the values are store in the correct format) :-

Handle data as currently stored

Assuming that the issue is that columns start time and end time do have values store as in "YYYY-MM-DD HH:MM:SS" format (rows where name is Name006 - Name010) then the following query would work :-

-- Modified query from the question

SELECT name, 

    datetime("start time"), 

    datetime("end time"),

    strftime('%s',datetime("end time"))-strftime('%s',datetime("start time")), 

    "completion status", 

    "failed clients list"

FROM  "savegroup job"

-- where strftime('%s',"end time","unixepoch","localtime") >= strftime('%s',"now", "-6 hours") and "completion status" like "%failed%";

WHERE strftime('%s',"end time") >= strftime('%s',datetime('now','-6 hours'))

    AND "completion status" LIKE '%failed%'

;

resulting in :-