需要使用SQLite查询进行一些奇怪的时间转换

或许以下几点可以作为基础.

• 您的问题中有一些似乎不匹配的内容，例如%H是24小时中的小时，午夜是实际的第二天00:00:00.

无论如何，也许下面的内容可以作为你想要的东西的基础.

• 请注意，在结果输出中还包括了其他数据，以期帮助理解各个方面.

它自己的查询:-

WITH local_utc_modifier(lum) AS 
    /* CTE (Common Table Expression a temp table) with the difference between local and utc (not sure - is correct but principle IF device is setup accordingly)*/
    (SELECT (strftime('%s','now','localtime')) - (strftime('%s','now','UTC')))
SELECT
    name,
    (SELECT lum FROM local_utc_modifier) AS lum , /* the modifier value: include to demonstrate  <<<<<<<<<<EXTRA>>>>>>>>>>*/ 
    strftime('%Y-%m-%dT%H:%M:%SZ',time,'unixepoch','-'||(SELECT * FROM local_utc_modifier)||' seconds') /* output value */ AS time,
    value, /* NOTE ARBRITRAY */
    max(value) AS maxv, /* include to demonstrate arbrtrary value <<<<<<<<<<EXTRA>>>>>>>>>>*/
    min(value) AS minv, /* as prev <<<<<<<<<<EXTRA>>>>>>>>>>*/
    avg(value) AS avgv, /* as prev <<<<<<<<<<EXTRA>>>>>>>>>>*/
    count() AS number_found /* more than 1!!!! SEE ABOVE RESULTS <<<<<<<<<<EXTRA>>>>>>>>>> */
    
FROM your_table
/* Only midnight rows to a second */
WHERE strftime('%H:%M:%S',time,'unixepoch','-'||(SELECT * FROM local_utc_modifier)||' seconds') = '00:00:00'
    /* MIGHT WANT ADDITIONAL CONDITION JUST FOR CURRENT DATE */
/* Note if only 1 row per name then no need for GROUP BY HOWEVER MUST NOT THEN HAVE aggregate functions (count,max,min,avg) */
GROUP BY name
;

• See the comments，因为查询的某些部分可能不需要/不需要.

以上内容的演示:

DROP TABLE IF EXISTS your_table;
CREATE TABLE IF NOT EXISTS your_table (
        name TEXT,
        value REAL,
        time INTEGER
);

/* Insert some testing data */
INSERT INTO your_table (name,value,time) 
    VALUES
    (1,100,1695765399),(2,200,1695765405),(3,300,1695765410),(3,10,1695765415),(2,20,1695765420),
    /* these ones are at midnight*/
    (1,100,1695765420 + 180),(1,15,1695765600),(2,222,1695765600),(3,333,1695765600),
    /* 1 second past midnight */
    (5,555,1695765601)
    ;
SELECT name, value, datetime(time,'unixepoch') AS easyreadtime, time FROM your_table;
WITH local_utc_modifier(lum) AS 
    /* CTE (Common Table Expression a temp table) with the difference between local and utc (not sure - is correct but principle IF device is setup accordingly)*/
    (SELECT (strftime('%s','now','localtime')) - (strftime('%s','now','UTC')))
SELECT
    name,
    (SELECT lum FROM local_utc_modifier) AS lum , /* the modifier value: include to demonstrate  <<<<<<<<<<EXTRA>>>>>>>>>>*/ 
    strftime('%Y-%m-%dT%H:%M:%SZ',time,'unixepoch','-'||(SELECT * FROM local_utc_modifier)||' seconds') /* output value */ AS time,
    value, /* NOTE ARBRITRAY */
    max(value) AS maxv, /* include to demonstrate arbrtrary value <<<<<<<<<<EXTRA>>>>>>>>>>*/
    min(value) AS minv, /* as prev <<<<<<<<<<EXTRA>>>>>>>>>>*/
    avg(value) AS avgv, /* as prev <<<<<<<<<<EXTRA>>>>>>>>>>*/
    count() AS number_found /* more than 1!!!! SEE ABOVE RESULTS <<<<<<<<<<EXTRA>>>>>>>>>> */
    
FROM your_table
/* Only midnight rows to a second */
WHERE strftime('%H:%M:%S',time,'unixepoch','-'||(SELECT * FROM local_utc_modifier)||' seconds') = '00:00:00'
    /* MIGHT WANT ADDITIONAL CONDITION JUST FOR CURRENT DATE */
/* Note if only 1 row per name then no need for GROUP BY HOWEVER MUST NOT THEN HAVE aggregate functions (count,max,min,avg) */
GROUP BY name
;

DROP TABLE IF EXISTS your_table; /* CLEANUP DEMO ENVIRONMENT */

• Important以上已在美国东部夏令时使用，因此需要考虑测试数据的时区差异. 运行上述程序时，Results为:-

• 加载第一个SELECT的数据:-

- note the 2 highlighted name **1** rows both are at midnight (to demonstrate GROUP BY and aggregate functions)

• 根据对你问题的解释，结果如下:

• 第2栏是额外的一栏，根据设备显示当地和UTC之间的时差(AEST时间).
• 最后四列演示聚合函数的分组.

Aggregate Functions and GROUP BY个

对于名称1，NUMBER_FOUND(来自count函数)显示有2行与WHERE标准匹配(可能会注意到所需的 case ).

GROUP BY子句定义将被视为聚合函数应用到的组的行集，并注意到对于组，将输出1行.(因此，包含名称的3个输出中，名称5在一秒钟内被删除以包含名称).

• 如果省略GROUP BY子句，则如果使用任何聚合函数，则所有输出都被视为1个组，从而产生1行输出.

如果 Select 要输出的列，并且该列不受聚合表达式的约束，则提取的值将是该组中某一行的值.它是一个任意值(例如，对于值列15被提取，它可能是100)(最大值、最小值和平均值说明).

• 根据查询优化器决定处理行的方式，任意值(S)将来自第一个值.除非深入了解优化器，否则最好不要依赖这样的值.

Additional个

想一想，尽管下面的内容比较复杂，但它是一个演示，它可能会让事情更容易理解，也提供了可以 Select 的选项.

它类似于原始演示，但加载数据:-

• 10000行，包括:-
  • 名称1-10，
  • 值1-100，
  • 和(本地)午夜1分钟的时间.
    • 有些正好是午夜(除非非常不走运，因为午夜的几率是60分之一)

添加了原始查询以显示各个方面(假设在存储数据时使用UTC时间).

它清楚地显示的一件事是，不需要提出LUM(本地UTC修饰符)，只需要‘UTC’或不‘UTC’或‘LocalTime’(设备/SQLite已经知道修饰符).

所以这里是更深入的版本(现在我还没有半睡半醒):

DROP TABLE IF EXISTS your_table;
CREATE TABLE IF NOT EXISTS your_table (
        name TEXT,
        value REAL,
        time INTEGER
);
/* populate table with 10000 rows (as per LIMIT) 
    with names 1-10 (random), 
    random values 1-1000, and 
    date and time as per the current date at 00:00:?? 
        where ?? is 0 - 59 seconds after midnight
*/
WITH 
    cte_counter(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM cte_counter LIMIT 10000)
-- SELECT * FROM cte_counter;
    INSERT INTO your_table SELECT
        (abs(random()) % 10) + 1 /* 1 to 10 */  AS name,
        (abs(random()) % 1000) + 1 /* 1 to 1000 */ AS value,
        
        /* get the current date, apply the time as 00:00:00 (midnight) and store as UTC time */ 
        strftime('%s',(date('now')||'T00:00:00'),'UTC','+'||(abs(random()) % 60)||' seconds') AS time FROM cte_counter
;
SELECT *,datetime(time,'unixepoch','UTC') AS UTCTime, datetime(time,'unixepoch','localtime') FROM your_table;


/* PROCESS THE LOADED DATA */
WITH local_utc_modifier(lum) AS 
    /* CTE (Common Table Expression a temp table) with the difference between local and utc (not sure - is correct but principle IF device is setup accordingly)*/
    (SELECT (strftime('%s','now','localtime')) - (strftime('%s','now','UTC')))
SELECT
    name, /* AS IS */
    /* the modifier value: included to demonstrate (note should not be 79200 for NZ that is AEST difference) <<<<<<<<<<EXTRA>>>>>>>>>>*/
    (SELECT lum FROM local_utc_modifier) AS lum , /* the modifier value: included to demonstrate (note should not be 79200 for NZ that is AEST difference) <<<<<<<<<<EXTRA>>>>>>>>>>*/ 
    /* The stored time as local time (you would expect 00:00:??)*/
    strftime('%Y-%m-%dT%H:%M:%SZ',time,'unixepoch','localtime') AS time,
    /* The stored time as per UTC time */
    strftime('%Y-%m-%dT%H:%M:%SZ',time,'unixepoch','UTC') /* output value */ AS AlternateTime,
    /* The unadjusted stored time. EXPECT IT TO MATCH ALTERATETIME (to confirm data stored as UTC, if stored as LOCAL then SQL has to be changed)*/
    strftime('%Y-%m-%dT%H:%M:%SZ',time,'unixepoch') AS UATime,
    value, /* NOTE WHEN GROUPED THIS WILL BE AN ARBRITRAY VALUE */
    max(value) AS maxv, /* include to demonstrate arbrtrary value <<<<<<<<<<EXTRA>>>>>>>>>>*/
    min(value) AS minv, /* as prev <<<<<<<<<<EXTRA>>>>>>>>>>*/
    avg(value) AS avgv, /* as prev <<<<<<<<<<EXTRA>>>>>>>>>>*/
    count() AS number_found /* more than 1!!!! SEE ABOVE RESULTS <<<<<<<<<<EXTRA>>>>>>>>>> */
    /* LOOK AT JUST THE TIME PORTION (as exactly 00:00:00 is wanted e.g. NOT 00:00:01)*/
    , strftime('%H:%M:%S',time,'unixepoch','localtime') AS loctime
    , strftime('%H:%M:%S',time,'unixepoch','UTC') AS UTCtime
    /* No need for this calculation, localtime should be good but as proof of concept- */
    , strftime('%H:%M:%S',time,'unixepoch','UTC','+'||(SELECT * FROM local_utc_modifier)||' seconds') AS timeonly
    /* just get the hours in the day, if midnoght then 0 (prehaps the most efficient way) */
    , strftime('%s',time,'unixepoch','localtime') % (60 * 60 * 24) AS hoursAsInt
FROM your_table
/* Only midnight rows to a second */
/* NOTE could use various means of detcting midnight rows only e.g. :-
    the simpler strftime('%H:%M:%S',time,'unixepoch','localtime') = '00:00:00' 
    or even strftime('%s',time,'unixepoch','localtime') % (60 * 60 * 24) = 0
    see above and output for results of the left side of the expression
    */
WHERE strftime('%H:%M:%S',time,'unixepoch','UTC','+'||(SELECT * FROM local_utc_modifier)||' seconds') = '00:00:00'
    /* MIGHT WANT ADDITIONAL CONDITION JUST FOR CURRENT DATE */
/* Note if only 1 row per name then no need for GROUP BY HOWEVER MUST NOT THEN HAVE aggregate functions (count,max,min,avg) */
GROUP BY name
;
DROP TABLE IF EXISTS your_table;

• 建议将上述内容放入SQLite工具(使用Navicat)，并使用代码/SQL.