sqlite - 如何使用 sqllite 获取最近 6 小时的数据

Question

代码：

.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%";

>= datetime("now", "-6 hours")不起作用。

我需要获取过去 6 小时的最后输入数据。

谢谢

标签： sqlite

Answer 1

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 :-